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

[heramb_sawant]

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,
 

[heramb_sawant]

Any help will be appreciated 

Regards,
Heramb Sawant

[krzysztof_hoja]

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

[heramb_sawant]

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

[krzysztof_hoja · Accepted solution]

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:

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"
]

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"}

[heramb_sawant]

Thanks you so much for the  examples with detailed explanation.

Regards,
Heramb Sawant