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

[Georgi_Vuldzhev]

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

[t_pawlak]

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.

[Georgi_Vuldzhev]

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.