topic how to display the number of problems and trends? in DQL

how to display the number of problems and trends?

Taihei — Thu, 03 Oct 2024 07:08:34 GMT

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()

Re: how to display the number of problems and trends?

PedroSantos — Wed, 02 Oct 2024 15:16:17 GMT

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.

Re: how to display the number of problems and trends?

krzysztof_hoja — Wed, 02 Oct 2024 20:43:33 GMT

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