how to display the number of problems and trends?

[Taihei]

I would like to use the single value visualization on the dashboard to display the number of PROBLEMS and trends by ENTITY TYPE.

I was able to display the following DQL for the number of PROBLEMS.

How can I display trend?

fetch dt.davis.problems
|filter matchesValue(affected_entity_types,"dt.entity.process_group_instance")
| lookup [
fetch dt.entity.process_group_instance
], sourceField:affected_entity_ids, lookupField:id
| fields root_cause_entity_name, display_id, event.category, event.start, timestamp

| summarize count()

[PedroSantos]

Hello @Taihei ,

I'm not sure I fully understood your problem. Correct me if I didn't.

From what I gathered, you're trying to see the trend that the number of problems from a given entity type follows. Is this correct?

Your query seems to be aiming at specifically following the number of problems that affect entity type "dt.entity.process_group_instance".

I tried to do the same for problems affecting entity type host (If you're only interested in the number of problems affecting a certain entity type, you do not need the lookup) :

fetch dt.davis.problems //fetch all problems
| filter matchesValue(affected_entity_types, "dt.entity.host") //filter for the entity_type you want
| makeTimeseries count(default: 0), // Use timeseries, default to 0 if no data
    interval: 15m  // Set the time interval you need

With timeseries you can produce a number of values in intervals within your timeframe.

With the single value tile this produces the following:

The array is the number of problems affecting Hosts over the course of 2 hours, with 15 minute intervals.

Then you can mess around with Visualize to make it look better:

Note:

Do note that this:

fetch dt.davis.problems //fetch all problems
| filter matchesValue(affected_entity_types, "dt.entity.process_group_instance") //filter for the entity_type you want
| makeTimeseries count(default: 0), // Use timeseries
    interval: 15m  // Set the time interval you need

And this:

fetch dt.davis.problems
|filter matchesValue(affected_entity_types,"dt.entity.process_group_instance")
| lookup [
fetch dt.entity.process_group_instance
], sourceField:affected_entity_ids, lookupField:id
|fields root_cause_entity_name, display_id, event.category, event.start, timestamp
| makeTimeseries count(default: 0), // Use timeseries
    interval: 15m  // Set the time interval you need

Both these will, for our purposes, produce the exact same results. Because we're using single value tiles, we don't need the extra fields.

[krzysztof_hoja]

Of course if you have a specific need for "singleValue" you can calculate it and keep in final query result. E.g. using query from @PedroSantos :

fetch dt.davis.problems //fetch all problems
| filter matchesValue(affected_entity_types, "dt.entity.process_group_instance") //filter for the entity_type you want
| makeTimeseries sparkline=count(default: 0), // Use timeseries
    interval: 15m  // Set the time interval you need
| fieldsAdd singleValue=arraySum(sparkline)

If needed value cannot be calculated from timeseries used as sparkline you can always add needed value using lookup or join command