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

[Bhargav_314]

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

[ChadTurner]

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

[sinisa_zubic]

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