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

**URL:** <https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497>\
**Category:** DQL\
**Tags:** dql\
**Created:** [July 8, 2024, 9:26am UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497 "2024-07-08T09:26:04Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![heramb\_sawant](https://avatars.discourse-cdn.com/v4/letter/h/c4cdca/32.png) [@heramb\_sawant](https://community.dynatrace.com/u/heramb_sawant)\
**Post date:** [July 8, 2024, 9:26am UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497/1 "2024-07-08T09:26:04Z")

</div>

Hi ,  
I want to write one DQL which can compare&nbsp; jvm metric i.e.&nbsp;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&nbsp; average of&nbsp; thread count samples&nbsp; of 2 timeframe

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

Regards,

---

<div class="post-metadata">

**Author:** ![heramb\_sawant](https://avatars.discourse-cdn.com/v4/letter/h/c4cdca/32.png) [@heramb\_sawant](https://community.dynatrace.com/u/heramb_sawant)\
**Post date:** [July 8, 2024, 11:54am UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497/2 "2024-07-08T11:54:59Z")

</div>

Any help will be appreciated

Regards,  
Heramb Sawant

---

<div class="post-metadata">

**Author:** ![krzysztof\_hoja](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/krzysztof_hoja/32/34625_2.png) [@krzysztof\_hoja](https://community.dynatrace.com/u/krzysztof_hoja)\
**Post date:** [July 8, 2024, 9:03pm UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497/3 "2024-07-08T21:03:48Z")

</div>

I hope it helps:

```markup
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)

```

 ![krzysztof_hoja_0-1720472580811.png](https://global.discourse-cdn.com/dynatrace/original/2X/5/547163f8494ae544286fca033f55620806d31a7f.png)

Kris

---

<div class="post-metadata">

**Author:** ![heramb\_sawant](https://avatars.discourse-cdn.com/v4/letter/h/c4cdca/32.png) [@heramb\_sawant](https://community.dynatrace.com/u/heramb_sawant)\
**Post date:** [July 10, 2024, 12:47pm UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497/4 "2024-07-10T12:47:05Z")

</div>

Thanks Kris&nbsp; for providing this DQL, this definitely giving comparison&nbsp; but I&nbsp; am specifically looking to compare two test&nbsp; release timeframes. I modified dql by adding test timeframe window like below but&nbsp;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

---

<div class="post-metadata">

**Author:** ![krzysztof\_hoja](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/krzysztof_hoja/32/34625_2.png) [@krzysztof\_hoja](https://community.dynatrace.com/u/krzysztof_hoja)\
**Post date:** [July 10, 2024, 6:43pm UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497/5 "2024-07-10T18:43:37Z")

</div>

What _ **shift:** _ parameter does is some sort of “cheating”&nbsp;😉.&nbsp;It&nbsp;shifts the timeframe specified in the basic query parameters&nbsp; 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:

```markup
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:

 ![krzysztof_hoja_0-1720636115836.png](https://global.discourse-cdn.com/dynatrace/original/2X/9/929e9fc6bbe2720871aebeb15534c0608a339bf4.png)

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

Result of such query:

```markup
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:

 ![krzysztof_hoja_1-1720636226221.png](https://global.discourse-cdn.com/dynatrace/original/3X/0/1/01a2a293a9e7342b3426afdb5327dbe031ee3e82.png)

and in raw form:

 ![krzysztof_hoja_2-1720636272724.png](https://global.discourse-cdn.com/dynatrace/original/3X/0/b/0b9c4aee7333c0705be7467581a8675102f5394e.png)

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

There&nbsp; are many possible solutions. This way:

```markup
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:

 ![krzysztof_hoja_3-1720636626084.png](https://global.discourse-cdn.com/dynatrace/original/2X/e/e6753849ccc903fef52d6ef85d40cb0d28ca6e1c.png)

but you can always combine this into single row:

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

```

 ![krzysztof_hoja_4-1720636923100.png](https://global.discourse-cdn.com/dynatrace/original/3X/5/a/5a1db1f32bc8d34e3b418fcc448b87fe9c833a9f.png)

---

<div class="post-metadata">

**Author:** ![heramb\_sawant](https://avatars.discourse-cdn.com/v4/letter/h/c4cdca/32.png) [@heramb\_sawant](https://community.dynatrace.com/u/heramb_sawant)\
**Post date:** [July 12, 2024, 12:21pm UTC](https://community.dynatrace.com/t/how-to-write-dql-to-comparison-jvm-metric-data-of-two-timeframe/46497/6 "2024-07-12T12:21:00Z")

</div>

Thanks you so much for the&nbsp; examples with detailed explanation.

Regards,  
Heramb Sawant
