topic Re: Rebuilding the Problems tile from Dashboards classic to the new Dashboards app in DQL

Rebuilding the Problems tile from Dashboards classic to the new Dashboards app

Georgi_Vuldzhev — Thu, 09 Oct 2025 06:29:33 GMT

Hello everyone,

I am facing a really annoying issue when I started rebuilding some of our Dashboards from Managed in the app in SaaS. I am fairly new with DQL, however I find it strange that something this simple is overcomplicated in SaaS.

What I am struggling with is that I am trying to display all currently active problems in a Dashboard but I don't find an easy way to do that. By using the pre-built "Problems" tile you get a list, OK. But when I try to show a single value tile I happen to fail displaying the actual number of open problems. I am using this DQL query:

fetch dt.davis.problems
| filter event.kind == "DAVIS_PROBLEM"
//| filter event.status == "ACTIVE"
| filter event.status != "CLOSED"
| summarize {activeProblems = countDistinct(event.id)}

Even though I am looking for open problems I always get the number of affected entities, instead of the problem count. This means that if I have 3 open problems and one of them affects 5 hosts, the number I get in the tile is 7, which is not right.

I tried several different approaches, but I kind of miss how to get it to work properly. Probably it's me...but still it was so much easier in Managed.

Has anyone faced this?

All the best,
Georgi

Re: Rebuilding the Problems tile from Dashboards classic to the new Dashboards app

t_pawlak — Thu, 09 Oct 2025 07:34:51 GMT

Hi Georgi

IMO what you’re seeing happens because dt.davis.problems emits multiple records per problem (including updates and affected entities). A straight count can easily “overcount” when one problem affects multiple hosts.
You could try this:

fetch dt.davis.problems | filter event.status == "ACTIVE" | summarize activeProblems = countDistinct(event.id)

event.id is the unique problem identifier, so countDistinct will return the number of open problems, not the number of entities.
Or More robust (take the latest status per problem first)

fetch dt.davis.problems | summarize lastStatus = takeLast(event.status), by: { event.id } | filter lastStatus == "ACTIVE" | summarize activeProblems = count()

This way you collapse everything to one row per problem (last known status), then count only those still active. This avoids duplicates caused by status updates or multiple affected entities.

Re: Rebuilding the Problems tile from Dashboards classic to the new Dashboards app

Georgi_Vuldzhev — Thu, 09 Oct 2025 11:24:22 GMT

Hi @t_pawlak ,

I tried with:

fetch dt.davis.problems
| summarize lastStatus = takeLast(event.status), by: { event.id }
| filter lastStatus == "ACTIVE"
| summarize activeProblems = count()

but it still shows wrong amount of problems
Reality: 6 active problems (one with 2 affected entities)
Number shown: 7

It's really strange. It also gives the same number as:

fetch dt.davis.problems
| filter event.status == "ACTIVE"
| summarize activeProblems = countDistinct(event.id)

and also the same as my initial query.

Re: Rebuilding the Problems tile from Dashboards classic to the new Dashboards app

t_pawlak — Fri, 10 Oct 2025 09:09:11 GMT

You're right — what you're seeing happens because dt.davis.problems returns multiple records per problem (updates, affected entities, historical statuses). A simple countDistinct(event.id) can still “overcount” since it includes historical entries.

The trick is to always collapse to the latest status per problem and remove duplicates. You can do this with takeLast() and the dt.davis.is_duplicate flag:

fetch dt.davis.problems | filter not(dt.davis.is_duplicate) | summarize lastStatus = takeLast(event.status), by: { event.id } | filter lastStatus == "ACTIVE" | summarize activeProblems = count()

Explanation:

not(dt.davis.is_duplicate) removes duplicate rows for the same problem.

takeLast(event.status) ensures you only look at the most recent status.

Filtering on ACTIVE after this guarantees that already “CLOSED” problems won’t be counted.

That’s why in your earlier attempts “solved” problems were still showing up as active.

Re: Rebuilding the Problems tile from Dashboards classic to the new Dashboards app

Georgi_Vuldzhev — Thu, 16 Oct 2025 12:24:27 GMT

Hi @t_pawlak,
I have tested this for couple of days and indeed it seems to work fine. Thanks a lot for your help!