topic Re: Hourly load distribution graph in DQL

Hourly load distribution graph

Red — Mon, 03 Feb 2025 10:33:07 GMT

Hello community,

I'm in the process of transferring dashboards from Splunk Cloud to Dynatrace (I'm getting the same data into a dedicated bucket).
I try to count the number of events per hour and per entity, in order to get an idea of the hourly load over one or more days (for example knowing if a type of event often occurs at the same times). So I made a simple graph that can show me this:

But I have a problem: I would like this graph to start at the 00:00 page and end at 23:00. time page and displays the number of events per entity each hour. But he must also know how to adapt if it takes several days. I'll give you examples of what I did on Splunk, maybe it's clearer (example on 24 hours of data and example on 7 days of data):

24 hours

7 days :

My order is as follows:

fetch events | filter source == "OnCall" and alertType == "CRITICAL" | filterOut in(routingKey, array("dynatrace_event", "mpb-dynatrace_event")) | fieldsAdd routingKey = if(routingKey == "dynatrace_2", "Dynatrace", else:if(routingKey == "servicepilot_prod", "ServicePilot", else:if(routingKey == "control-m", "Control-M", else:if(routingKey == "dynatrace_mfe", "Dynatrace MFE", else:if(routingKey == "control-m_mfe", "Control-M MFE", else:if(routingKey == "dynatrace_amm", "Dynatrace AMM", else:if(routingKey == "control-m_z-os", "Control-M Z/OS", else:if(routingKey == "mpb-control-m", "Control-M MPB", else:if(routingKey == "mpb-dynatrace_problem", "Dynatrace MPB", else:if(routingKey == "dynatrace_f02", "Dynatrace F02", else:routingKey)))))))))) | dedup incidentNumber | makeTimeseries count(), by: {routingKey}, interval:1h

I tried another approach that allows me to see what I'm looking for in table form but I can't convert it to a graph:

Do you have any idea how to do this (if it's possible)?

Sincerely,

Red

Re: Hourly load distribution graph

krzysztof_hoja — Mon, 03 Feb 2025 13:36:32 GMT

You solution is good in term of data - the problem is that chart requires timestamp datatype to correctly render x-axis. You can convert hour of the day into timestamp e.g. of today using duration() function and beginning of today (midnight) as a day start.

Bu it is also possible by following manipulation of timestamp and using makeTimeseries:

fetch bizevents, from: -7d | fieldsAdd timestamp = timestamp - timestamp@d + @d | makeTimeseries cnt=count(),from: @d, to: @d+1d, interval:1h

This way I map each timestamp from past week into today. I used recently introduced time alignment operator: https://docs.dynatrace.com/docs/discover-dynatrace/references/dynatrace-query-language/operators#time-alignment but it also possible to get similar effect using bin() function.

As we want to chars timeframe different they "analysis timeframe", "Data" needs to selected as a input for x-axis. This setting as well as result is visible on screenshot below:

Re: Hourly load distribution graph

Red — Mon, 03 Feb 2025 15:14:57 GMT

Hello @krzysztof_hoja,

Thank you for your help, I didn't know this method of handling timestamps. So, it's on track and in the notebook, it displays exactly as I wanted:

All I have to do now is find out how to do the same thing for the dashboard because there, it's not blocked on a time slot (display problem I imagine):

Thanks again for your help.

Sincerely,

Red

Re: Hourly load distribution graph

krzysztof_hoja — Mon, 03 Feb 2025 18:24:46 GMT

Dashboards have exactly the same setting, just located in different place: go to Visual -> (when Line/Area/Bar chary is selected) -> X-Axis