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

[Bhargav_314]

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

[LawrenceBarratt]

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

[Bhargav_314 · Accepted solution]

Hi Lawrence,

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

[sinisa_zubic]

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

[Bhargav_314]

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

[sinisa_zubic]

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

[Bhargav_314]

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.

[sinisa_zubic]

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}

[Bhargav_314]

Hi Sini,

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

[sinisa_zubic]

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

[heybeckerj]

@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

[sinisa_zubic]

Hi @heybeckerj 

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

Best,
Sini