topic Re: DQL Monthly Query for problems in DQL

DQL Monthly Query for problems

Eyad — Thu, 27 Feb 2025 19:41:09 GMT

hi all, i am trying to do Monthly Problem Count and ServiceNow Ticket Count Over the Last 4 Months in one tile.

fetch dt.davis.problems
| summarize TotalProblemMonthly=count(), by:{Month = formatTimestamp(timestamp, format: "MM-yyyy")}
| join [fetch dt.davis.problems
| filter matchesPhrase(labels.alerting_profile,"ServiceNow")
| summarize ServiceNowTickets=count(), by:{Month = formatTimestamp(timestamp, format: "MM-yyyy")}], on:{Month}

the result coming out not sorted i want to sort it based on a month, however if i use sort command will not use the year ad well which is a problem. look at the result image. any recommendation to fix the query to sort it?

Re: DQL Monthly Query for problems

StrangerThing — Thu, 27 Feb 2025 20:02:49 GMT

Try putting the year first, and you don't need to do the join.

fetch dt.davis.problems
| summarize TotalProblemMonthly=count(),
ServiceNowTickets=countIf(matchesPhrase(labels.alerting_profile, "ServiceNow")),
by:{Month = formatTimestamp(timestamp, format: "yyyy-MM")}
| sort Month desc

Re: DQL Monthly Query for problems

Eyad — Thu, 27 Feb 2025 20:15:29 GMT

Perfect!, my other question. why it says line graph is unsuitable. if i want the results in graph.

Re: DQL Monthly Query for problems

krzysztof_hoja — Fri, 28 Feb 2025 08:18:37 GMT

You should use rounded timestamps, not string representation. Also to do proper bar chart it is better to use timerange so single bar spans the time it applies to:

fetch dt.davis.problems | summarize { TotalProblemMonthly=count() , ServiceNowTickets=countIf(matchesPhrase(labels.alerting_profile, "ServiceNow")) }, by:{ timeframe=timeframe( from: timestamp@M, to: timestamp@M+1M ) } | sort timeframe desc

I used time alignment operators: https://docs.dynatrace.com/docs/discover-dynatrace/references/dynatrace-query-language/operators#time-alignment.

Result look like this: