topic Count of problem based on duration its open. <1 hour <12 hours <24 hours >24 hours. in DQL

Count of problem based on duration its open. <1 hour <12 hours <24 hours >24 hours.

Bhargav_314 — Wed, 23 Aug 2023 05:46:13 GMT

Hi Team,

Can you please let me know how to apply these filters and get the counts based on duration of the problem open.

Requirement: Count of problem based on duration its open. <1 hour <12 hours <24 hours >24 hours.

fetch events, from:-30d
| filter event.kind == "DAVIS_PROBLEM"

| fieldsAdd open_ts = if(event.status == "ACTIVE" AND event.status_transition == "UPDATED",timestamp)
| filter isNotNull(open_ts)
| summarize
open = min(open_ts),
by:{display_id, event.category, event.name }
| summarize
count=countDistinct(display_id),
by:{open}

| summarize count = count()
| sort count desc

Re: Count of problem based on duration its open. <1 hour <12 hours <24 hours >24 hours.

ChadTurner — Mon, 12 Feb 2024 19:54:57 GMT

@Bhargav_314 were you able to get a DQL statement that achieves your desired outcome?

Re: Count of problem based on duration its open. <1 hour <12 hours <24 hours >24 hours.

sinisa_zubic — Mon, 12 Feb 2024 20:22:24 GMT

Hi all

That should be the query you are looking for

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 | fieldsAdd duration_bucket = if(problem.event.start < now() - 24h, "> 24h") | fieldsAdd duration_bucket = if(problem.event.start >= now() - 24h, "< 24h", else: duration_bucket) | fieldsAdd duration_bucket = if(problem.event.start >= now() - 12h, "< 12h", else: duration_bucket) | fieldsAdd duration_bucket = if(problem.event.start > now() - 1h, "< 1h", else: duration_bucket) | summarize count = count(), by: { duration_bucket }

Best,
Sini