topic How to write DQL to comparison jvm metric data of two timeframe in DQL

How to write DQL to comparison jvm metric data of two timeframe

heramb_sawant — Mon, 08 Jul 2024 09:26:04 GMT

Hi ,
I want to write one DQL which can compare jvm metric i.e. dt.runtime.jvm.threads.count data of two timeframe. Can somebody please how I can use lookup to join the two data set here.

I want to show metric name and average of thread count samples of 2 timeframe

Metric name June test 1 June Test 2
dt.runtime.jvm.threads.count 59 69

Regards,

Re: How to write DQL to comparison jvm metric data of two timeframe

heramb_sawant — Mon, 08 Jul 2024 11:54:59 GMT

Any help will be appreciated

Regards,
Heramb Sawant

Re: How to write DQL to comparison jvm metric data of two timeframe

krzysztof_hoja — Mon, 08 Jul 2024 21:03:48 GMT

I hope it helps:

timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00" | lookup [ timeseries tc_ref=avg(dt.runtime.jvm.threads.count), shift:-1d ], sourceField:timeframe, lookupField:timeframe, fields:{tc_ref} | fields metric="dt.runtime.jvm.threads.count", test=arrayAvg(tc), tc_ref=arrayAvg(tc_ref)

Kris

Re: How to write DQL to comparison jvm metric data of two timeframe

heramb_sawant — Wed, 10 Jul 2024 12:47:51 GMT

Thanks Kris for providing this DQL, this definitely giving comparison but I am specifically looking to compare two test release timeframes. I modified dql by adding test timeframe window like below but tc_ref is giving null .

timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-06-04T00:00", to: "2024-06-05T00:00"
| lookup [
timeseries tc_ref=avg(dt.runtime.jvm.threads.count), from: "2024-07-04T00:00", to: "2024-07-05T00:00"
], sourceField:timeframe, lookupField:timeframe, fields:{tc_ref}
| fields metric="dt.runtime.jvm.threads.count", test=arrayAvg(tc), tc_ref=arrayAvg(tc_ref)

Please help . is there need to change lookup and source Field??

Regards,
Heramb sawant

Re: How to write DQL to comparison jvm metric data of two timeframe

krzysztof_hoja — Wed, 10 Jul 2024 18:43:37 GMT

What shift: parameter does is some sort of "cheating" 😉. It shifts the timeframe specified in the basic query parameters and maps the resulting data points to timestamps from the original timeframe. Because I used lookup requires joining condition I used timeframe frame, because it was the same due to this effect. Logically in this case join condition is not needed, because both queries will return always 1 record.

You can observe this effect on chart when we remove aggregation to single value. I used append so we can have 2 different records, each with individual timeframe. This query:

timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00" | append [ timeseries tc_ref=avg(dt.runtime.jvm.threads.count), shift:-1d ]

Produces this result:

black line represents data for June 30th, but it is mapped into July 1st

Result of such query:

looks like this:

and in raw form:

As you can see timeframe field is different, so when used in lookup join condition it did not connect records.

There are many possible solutions. This way:

timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00" | append [ timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-06-30T00:00", to: "2024-07-01T00:00" ] | fields timeframe, tc = arrayAvg(tc)

result will be presented with 2 rows and by looking at timeframe column, you can see what is actual and what is reference value:

but you can always combine this into single row:

timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00" | append [ timeseries tc_ref=avg(dt.runtime.jvm.threads.count), from: "2024-06-30T00:00", to: "2024-07-01T00:00" ] | summarize {tc=takeAny(arrayAvg(tc)), tc_ref=takeAny(arrayAvg(tc_ref))}, by: {metric="dt.runtime.jvm.threads.count"}

Re: How to write DQL to comparison jvm metric data of two timeframe

heramb_sawant — Fri, 12 Jul 2024 12:21:00 GMT

Thanks you so much for the examples with detailed explanation.

Regards,
Heramb Sawant