Get OPEN and CLOSED Davis problem

[Duran_Narbona]

Good afternoon,

I have two doubts:

1. Why when a problem is closed, it can appear with the word CLOSED and others with RESOLVED, or de mix?
2. Why does the CLOSED event appear twice in almost every Davis problem, within seconds of each other?

Example 1:

Example 2:

[Duran_Narbona]

This is my query to see all OPEN and CLOSED events. But it doesn't work, because it's duplicating events.

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| filter event.status_transition =="CREATED" or event.status_transition=="RESOLVED" or event.status_transition=="CLOSED"
| sort timestamp desc

[LawrenceBarratt]

@Duran_Narbona The events are the problem evolution over time.

If you're wanting a single event instance, you'll want to takeLast as part of the query to get the most recent update.

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| filter event.status_transition =="CREATED" or event.status_transition=="RESOLVED" or event.status_transition=="CLOSED"
| summarize CurrentTime = takeLast(timestamp), by:{display_id}
| sort CurrentTime desc

To get the count of problems and their most current status use the below:

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| filter event.status_transition =="CREATED" or event.status_transition=="RESOLVED" or event.status_transition=="CLOSED"
| summarize CurrentTime = takeLast(timestamp), by:{display_id, event.status_transition}
| summarize ProblemCount = countDistinct(display_id),by:{event.status_transition}

Be aware that this gets the current event.status_transition of the events table in Grail for a specific display_id

Thanks,

Lawrence

[sinisa_zubic · Accepted solution]

hi @Duran_Narbona 

In general Davis problems and events stored in grail are just status updates. For a further explanation please have a look here: https://community.dynatrace.com/t5/DQL/Notebook-query/m-p/211195/highlight/true#M53

This query should work for you

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| sort timestamp, direction:"ascending"
| summarize {event.status = takeLast(event.status)}, by:{ event.id }
| summarize count=count(), by:{event.status}

You can try it out here

For further examples regarding Davis problems & events in grail, please have a look at help

Best,
Sini

[RPbiaggio]

Hi, @sinisa_zubic, 

I'm trying to extract the total number of open problems, regardless of timeframe, that is, if it has 10, 30, 60 days, it should return that total. The problem is that I'm using some filters and when I try to pull more than 2 hours, the query freezes and I don't get a response. Is there anything that can be done, any adjustments to this DQL?

fetch events
| expand compute = entity_tags
| fieldsAdd compute = (compute=="COMPUTE:baremetal")
| expand datacenter = entity_tags
| fieldsAdd datacenter = (datacenter=="Datacenter:SP")
| expand environment = entity_tags
| fieldsAdd environment = (environment=="env:PRD")
| filter compute == true and datacenter == true and environment == true and not(matchesPhrase(entity_tags,"xxxxxxxxxx")) and not(matchesPhrase(entity_tags,"Banco de Dados"))
| filter event.kind == "DAVIS_PROBLEM"
| sort timestamp, direction:"ascending"
| summarize {event.start = takelast(event.start), event.status = takeLast(event.status)}, by:{ event.id }
| FILTER event.status == "ACTIVE"
//| summarize count = count()

Thank you

[sinisa_zubic]

Hi @RPbiaggio 

Changing the timeframe should not cause the query execution to freeze. There is an execution time out after 5 minutes, but you should still be able to see an interim result. Feel free to open a support ticket for further investigation here: https://one.dynatrace.com/hc/en-us/requests

Best,
Sini

[RPbiaggio]

Hello, thanks for the answer, but I still don't understand if it's possible to do what I need. I want to put only OPEN problems in a dashboard, regardless of the timeframe. So when I use this query, it shows me a much larger volume of problems with OPEN status than I actually have. Is this possible to do?

I have had problems open since November and I need to put this on the NOC screen, but I can't understand how to get just the open problems. When I select, for example, 7, 30 days, it returns almost 10k problems and I know that I don't have that with the OPEN status.

[sinisa_zubic]

yes it is possible to do this, but you need to set the timeframe to last 6h.

Why 6hours?
"Davis problem" records are a change log of problem updates in grail. In case there is no update for 3 hours, the last update is duplicated with a new timestamp. So with setting the timeframe to 6 hours, for every problem there should be at least two records.

Before you do any tag filtering, you should identify the latest change for every problem.

I would propose following query for you. Just I am not able to test it properly because I don't have an environment with the proper tag values to test.

fetch events, from:now()-6h
| filter dt.system.bucket == "default_davis_events"
| filter event.kind == "DAVIS_PROBLEM"
| summarize {problem = takeMax(
  record(timestamp, event.id, event.start, event.status, entity_tags)
)}, by:{ display_id }
| fieldsFlatten problem
| expand compute = problem.entity_tags
| fieldsAdd compute = (compute=="COMPUTE:baremetal")
| expand datacenter = problem.entity_tags
| fieldsAdd datacenter = (datacenter=="Datacenter:SP")
| expand environment = problem.entity_tags
| fieldsAdd environment = (environment=="env:PRD")
| filter compute == true and datacenter == true and environment == true and not(matchesPhrase(problem.entity_tags,"xxxxxxxxxx")) and not(matchesPhrase(problem.entity_tags,"Banco de Dados"))
| FILTER problem.event.status == "ACTIVE"

Best,
Sini