DQL count datapoints

[tore_davidsen1]

I`m trying to create a dashboard showing the % of data points where there was active problems. I figure that if I can count the number of data points where the value of problems is >0, then divide it with the total number of data points, and multiply by 100 it would give me exactly that. Any suggestions on how to do this would be appreciated.

[jolinger]

My best guess would be something like the following, assuming CPU utilization is the concern. Compute the average CPU utilization per interval, then count the number of intervals where utilization exceeds 50%. By casting those intervals to 1 and the rest to 0, summing them, and dividing by the total number of intervals, you get the percentage of time the CPU was above 50% utilization. Without knowing the exact use case, this would be my best guess.:

timeseries a=avg(log.sap.abap.st06.cpu_util, default:0)
| fields percent=arraySum(iCollectArray(toDouble(a[]>50)))/arraySize(a)*100

[jolinger]

If this is log-based rather than metric-based, I’d take a summarize countIf() approach: count the log entries that meet the problem criteria (e.g., execution time over a threshold) and divide that by the total number of log entries.

fetch logs
| filter event_type == "STAD"
| summarize toDouble(countIf(execution_time >15700))/count() * 100

[tore_davidsen1]

Well, actually what we want to do is to compare the number of datapoints in a given timeseries where there was active problems registered, and then divide it by the total number of datapoints within the same timeseries and then multiply by 100 to get an availability based on problems:

Example:

we have a timeseries with a resolution of 5m and default value 0, and our time range is 1 hour. That would give us 12 datapoints. Lets imagine that 4 out of this 12 datapoints had active problems registered. That would give us 3 / 12 * 100 = 25, which would then represent the availability for us. If you look at the dql below, it is how far I have been able to come:

fetch dt.davis.problems
// Filter out relevant events
| filter event.status == "ACTIVE"
// Make timeseries where datapoints with no value is replaced with 0, and a granularity of 5m.
| makeTimeseries count(default:0), interval: 5m
// Count the total number of datapoints, and put it in a field named PBMS
| fieldsAdd PBMS = `count(default:0)

But then, I just cannot figure out how to move on from here. What I want to do now, is to count the number of datapoints where there are active problems (value is more than 0), and then do the math (number of datapoints with value more than 0 / total number of datapoints.
 100).