topic Re: Fetch active problems in DQL

Fetch active problems

educampver — Thu, 01 Jun 2023 06:51:42 GMT

Hi,

I am trying to make a query that would fetch currently active problems, but I'm struggling with handling multiple of the same display_id. The filters I think will be good enough for me would look like this:
| filter event.kind == "DAVIS_PROBLEM" and event.status == "ACTIVE". However, now I need a way to fetch only the latest update of a given problem (to my understanding, the same problem id multiple times means the problem got updated and I only want to consider the problem from the latest snapshot). Thanks in advance!

Re: Fetch active problems

sinisa_zubic — Mon, 19 Jun 2023 09:09:27 GMT

Hi Edu,

One way of achieving this would be following

fetch all problems
sort them by the timestamp
summarize them by display_id and select the first status. because in the previous step the records are sorted by timestamp, you will get after the summarize the latest problem status
then you just filter for active problems

And here you have the query

fetch events | filter event.kind == "DAVIS_PROBLEM" | sort timestamp desc | summarize affected_entities= first(affected_entity_ids),event.status=first(event.status), by:display_id | filter event.status != "CLOSED"

Also please have a look at following help page where there are further DQL davis examples: https://www.dynatrace.com/support/help/shortlink/davis-dql-examples

Best,
Sini

Re: Fetch active problems

AK — Sat, 03 Feb 2024 15:47:08 GMT

@sinisa_zubic,

We are using similar query to fetch the problems which are open for more than 15 days.

However, what we are observing is, it is not matching with the count on problem card page.

As per more information received from support, fetch events is fetching the events associated with problem not actual number of problem and problem may have multiple events.

Is it possible to get actual count of problem or if that is not possible, how can we modify above query to match it to actual problem number on problem card page?

Regards,

Re: Fetch active problems

DantePaoloni — Sat, 03 Feb 2024 21:52:38 GMT

You should keep only those events where display_id is not null. As you notice if you don't do this, you will fetch all the events in the tenant.

Cheers.

Re: Fetch active problems

sinisa_zubic — Mon, 05 Feb 2024 13:46:16 GMT

Don't know the details of the conversation you had with support, but with adding

| filter event.kind == "DAVIS_PROBLEM"

to the query, you filter only on problems and not the events related to the problems.

My response from 1st June is already a bit outdated. Since then additional fields have been added to Grail (which are also documented in the release notes) and should also be in the query: problem.dt.davis.is_duplicate, problem.maintenance.is_under_maintenance .

following query should give you a much more accurate result.

fetch events, from:now()-370m | filter dt.system.bucket == "default_davis_events" | filter event.kind == "DAVIS_PROBLEM" | summarize {problem=takeMax(record(timestamp,resolved_problem_duration,dt.davis.is_duplicate,event.status,maintenance.is_under_maintenance,dt.davis.is_frequent_event, event.start)) }, by:{display_id} | fieldsFlatten problem | filter problem.event.status != "CLOSED" | filter problem.dt.davis.is_duplicate == false and problem.maintenance.is_under_maintenance == false

And to filter on those events, which are longer open than 15 days just add to the query this:

| filter problem.event.start < now() - 15d

Best,
Sini