DQL Monthly Query for problems

[Eyad]

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?

[StrangerThing · Accepted solution]

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

[Eyad]

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

[krzysztof_hoja]

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#tim....

Result look like this: