topic Re: Count of Problem in each week by its category of the event type from last one month data in DQL

Count of Problem in each week by its category of the event type from last one month data

Bhargav_314 — Tue, 22 Aug 2023 11:16:24 GMT

Count of Problem in each week by its category of the event type from last one month data. Its giving wrong data.

fetch events, from:-30d
| filter event.kind == "DAVIS_PROBLEM"

| fieldsAdd open_ts = if(event.status == "ACTIVE" AND event.status_transition == "UPDATED",timestamp)
| filter isNotNull(open_ts)
| summarize
open = min(open_ts),
by:{display_id, event.category, event.name }
| summarize
count=countDistinct(display_id),
by:{open}

| summarize count = count(), by: {`1week interval` = bin(open, 7d)}
| sort count desc

Re: Count of Problem in each week by its category of the event type from last one month data

LawrenceBarratt — Tue, 22 Aug 2023 14:31:08 GMT

Hi,

You won't need most of the query, only this DQL.

fetch events, from:-30d | filter event.kind == "DAVIS_PROBLEM" | summarize ProblemCount = countDistinct(display_id), by:{event.category, `1 week interval` = bin(timestamp, 7d)} | sort ProblemCount desc

Re: Count of Problem in each week by its category of the event type from last one month data

sinisa_zubic — Tue, 22 Aug 2023 15:56:03 GMT

Hi @Bhargav_314

There are actually 2 things to consider why there is a discrepancy between the "problem feed" screen and the result of your query.

1. We have recently added a field "dt.davis.is_duplicate" - that one should flag if a davis problem is a merged problem or not. so you need to filter those out.

2. You don't have to filter on the timestamp of the transition to identify when a problem was opened or closed, now you can use the event.start or event.end field.

I hope following query works for you.

fetch events, from:-30d | filter event.kind == "DAVIS_PROBLEM" | sort timestamp desc | summarize {id = takeFirst(display_id), duplicate = takeFirst(dt.davis.is_duplicate), week = takeFirst(formatTimestamp(toTimestamp(toLong(event.start)), format:"w")), category = takeFirst(event.category)}, by:{display_id} | filter duplicate == false | summarize `Problem count` = count(), by:{`Week in year` = week, `Problem category` = category}

I can also share with you that the team is currently working on a new problem feed. There it will be possible that you pin e.g. the problem count on a dashboard or notebook. With that kind of action you will be also able to see how the DQL query behind the "problem count" number in the selected timeframe looks like. But I can't give you an ETA for this.

Best,
Sini

Re: Count of Problem in each week by its category of the event type from last one month data

heybeckerj — Tue, 22 Aug 2023 16:48:56 GMT

@sinisa_zubic this is great!

Is there a way to group by more than 1 Category axis in the bar chart?

Right now you can only choose 1

Re: Count of Problem in each week by its category of the event type from last one month data

Bhargav_314 — Wed, 23 Aug 2023 05:05:04 GMT

Hi Lawrence,

Counts are not matching when i match the notebooks output with problems page. Can you please check once?

Re: Count of Problem in each week by its category of the event type from last one month data

Bhargav_314 — Wed, 23 Aug 2023 05:08:18 GMT

Hi Sini,

Instead of the week in year can I get the start date of the week. I was trying that but its failing. PFA screen shot for your reference.

Thanks,

Bhargav

Re: Count of Problem in each week by its category of the event type from last one month data

sinisa_zubic — Wed, 23 Aug 2023 06:07:41 GMT

Hi Bhargav

that should be the query if you want to group by the start day of the week

fetch events, from:-30d | filter event.kind == "DAVIS_PROBLEM" | sort timestamp desc | summarize {id = takeFirst(display_id), duplicate = takeFirst(dt.davis.is_duplicate), open = takeFirst(toTimestamp(toLong(event.start))), category = takeFirst(event.category)}, by:{display_id} | filter duplicate == false | fieldsAdd weekOf=formatTimestamp(open-duration(getDayOfWeek(open) -1 ,"d"),format:"YYYY-MM-dd") | summarize `Problem count` = count(), by:{weekOf, category}

Best,
Sini

Re: Count of Problem in each week by its category of the event type from last one month data

sinisa_zubic — Wed, 23 Aug 2023 06:10:27 GMT

Hi @heybeckerj

Not sure when this will be possible. maybe @Philipp_Kastner can answer this?

Best,
Sini

Re: Count of Problem in each week by its category of the event type from last one month data

Bhargav_314 — Wed, 23 Aug 2023 06:13:02 GMT

Hi Sini,

Thanks for sharing the query, there is one issue i have found, we are fetching the data from last 30 days, but results are having June month data also. Can you please check once? I have attached screen shot.

Re: Count of Problem in each week by its category of the event type from last one month data

sinisa_zubic — Wed, 23 Aug 2023 06:53:13 GMT

Hi Bhargav,

My assumption was that in your environment the problems do not last longer than 1 week. You must have problems which have started quite some while ago. So that query does not work pretty well if you have such kind of problems since I am grouping by event start date.

If you group by the timestamp (update time of the event), then the result should look better

fetch events, from:-30d | filter event.kind == "DAVIS_PROBLEM" | sort timestamp desc | fieldsAdd weekOf=formatTimestamp(timestamp-duration(getDayOfWeek(timestamp) -1 ,"d"),format:"YYYY-MM-dd") | summarize {id = takeFirst(display_id), duplicate = takeFirst(dt.davis.is_duplicate), category = takeFirst(event.category)}, by:{display_id,weekOf} | filter duplicate == false | summarize `Problem count` = count(), by:{weekOf,category}

Re: Count of Problem in each week by its category of the event type from last one month data

Bhargav_314 — Wed, 23 Aug 2023 07:27:49 GMT

Hi Sini,

This query looks fine, but problem counts are not matching. can you please check once ?

Re: Count of Problem in each week by its category of the event type from last one month data

sinisa_zubic — Wed, 23 Aug 2023 16:18:59 GMT

Can you make sure for the timeframe to use from 00:00 to 23:59 on the problem screen so it matches the DQL query?

Please also note that davis problems in grail are per default stored for 35 days.

by how much (relative & absolute) are the counts not matching?

Beside that I don't know what could be the issue. for me the query is working in an environment with a similar amount of problems

If this still does not give you the desired result, then please create a support ticket via https://one.dynatrace.com/hc/en-us/requests . Tech support will help you with this issue