Get Host Name in Open Events

[PickleRick]

Greetings!  I'm attempting to DQL my way to a problems dashboard.  Think of the classic Problems UI in DT, showing active problems and what's affected.

So far my DQL is very basic:

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| sort timestamp desc
| filter event.status != "CLOSED"

Unfortunately when I get my events back, host name isn't part of the result set.  I get things like affected_entity_ids, but I don't get an actual host name.  If I have low disk space or a service that isn't responding, I'd like to see that.  To be honest, without it the returned data to my proposed dashboard isn't really helpful.

So... how can I get impacted service\database\server\website name?

[kalelazic · Accepted solution]

Hi PickleRick!

This query should help:

fetch events, from:now()-6h
| filter event.kind == "DAVIS_PROBLEM"
| summarize {problem=takeMax(record(
  timestamp,
  resolved_problem_duration,
  event.end,dt.davis.is_duplicate,
  event.status,maintenance.is_under_maintenance,
  dt.davis.is_frequent_event,
  affected_entity_ids
  )) }, by:{display_id}
| fieldsFlatten problem
| filter problem.dt.davis.is_frequent_event == false and problem.dt.davis.is_duplicate == false and problem.maintenance.is_under_maintenance == false
| filter problem.event.status == "CLOSED"
| expand entityId = problem.affected_entity_ids
| fieldsAdd entityName = if(startsWith(entityId,"SERVICE-"), lookup([fetch dt.entity.service], sourceField:entityId, lookupField:id)[entity.name])
| fieldsAdd entityName = if(startsWith(entityId,"HOST-"), lookup([fetch dt.entity.host], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"APPLICATION-"), lookup([fetch dt.entity.application], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"PROCESS_GROUP_INSTANCE-"), lookup([fetch dt.entity.process_group_instance], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"HTTP_CHECK-"), lookup([fetch dt.entity.http_check], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"SYNTHETIC_TEST-"), lookup([fetch dt.entity.synthetic_test], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"MOBILE_APPLICATION-"), lookup([fetch dt.entity.mobile_application], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| summarize affectedEntities = collectArray(entityName), by:{display_id}

Regards
Kale

[PickleRick]

Thanks Kale!  This helps immensely!  I have a lot to learn about DQL apparently.

Would you mind summarizing for me:

1. How does this work?  As an example,

if(startsWith(entityId,"MOBILE_APPLICATION-"), lookup([fetch dt.entity.mobile_application]

How would I know to check dt.entity.mobile_application?  Trying to understand what documentation I could have researched that would have given me that information.

2. This gives me problem id and affected service\application\host - if I wanted to add another field, lets say problem summary (ex: Low Disk Space), how would I go about doing that?  Not asking you to solve for me, asking for pointers so I can learn myself 🙂

Thanks again!

[sinisa_zubic]

Hi @PickleRick 

Question 1:

We are currently planning to release a more extended documentation about entities (a dictionary). From there you should be able to see what kind of entities exists, their properties and their relationships. Currently you won't be able to find that in documentation. But if you would have any problems raised in your environment which have mobile applications affected, then you would know.

Question 2:
This query looks how it looks because it considers some the way davis events & problems are stored in grail. First you must know, davis problem records in grail are basically a changelog. So before you do any kind of further processing of a davis problem, you want to get the most recent state of a davis problem.
Basically that part

fetch events, from:now()-6h
| filter event.kind == "DAVIS_PROBLEM"
| summarize {problem=takeMax(record(
  timestamp,
  resolved_problem_duration,
  event.end,dt.davis.is_duplicate,
  event.status,maintenance.is_under_maintenance,
  dt.davis.is_frequent_event,
  affected_entity_ids,
  event.name
  )) }, by:{display_id}

In the record function you list all fields which are to your interest. Because you are interested also in the problem summary, add the field event.name to that list.

After this step, every record consists of two fields, a field display_id and a complex field problem, which we have created with the record function. We want to flatten this complex record out, so lets use the fieldsFlatten command

| fieldsFlatten problem
| filter problem.dt.davis.is_frequent_event == false and problem.dt.davis.is_duplicate == false and problem.maintenance.is_under_maintenance == false
| filter problem.event.status == "CLOSED"

Since affected_entity_ids are stored in an array, we have to expand it and find to every affected entity ID the entity name.  

| expand entityId = problem.affected_entity_ids
| fieldsAdd entityName = if(startsWith(entityId,"SERVICE-"), lookup([fetch dt.entity.service], sourceField:entityId, lookupField:id)[entity.name])
| fieldsAdd entityName = if(startsWith(entityId,"HOST-"), lookup([fetch dt.entity.host], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"APPLICATION-"), lookup([fetch dt.entity.application], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"PROCESS_GROUP_INSTANCE-"), lookup([fetch dt.entity.process_group_instance], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"HTTP_CHECK-"), lookup([fetch dt.entity.http_check], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"SYNTHETIC_TEST-"), lookup([fetch dt.entity.synthetic_test], sourceField:entityId, lookupField:id)[entity.name], else: entityName)
| fieldsAdd entityName = if(startsWith(entityId,"MOBILE_APPLICATION-"), lookup([fetch dt.entity.mobile_application], sourceField:entityId, lookupField:id)[entity.name], else: entityName)

 We have many duplicate problems now, so lets summarize the problems and add the affected entities arrays in one array field. Also don't forget your additional field event.name (summary).

| summarize affectedEntities = collectArray(entityName), by:{display_id, problem.event.name}

The team is currently working on improvements so that you won't need to query the latest change of a davis problem.

Best,
Sini