Hostname for Process restart event

[janne_olkoniemi]

Hello,

I'm relativity new with DQL and taking my first baby steps with it currently and trying to figure out how to utilize it.
One us case I have currently is to list certain process restarts for table and facing some challenges how to include the hostname on my query where the restart has happened? 

I would be greatly appreciated if someone could help me on this since since not sure which way I should approach this challenge.

fetch events
| fieldsAdd dt.entity.host.name, dt.entity.process_group_instance.name, displayName, timestamp
| filter event.group_label == "Process restart" and dt.entity.process_group_instance.name == "WebSphere Liberty runtime"
| fields dt.entity.process_group_instance.name, timestamp, dt.entity.host.name
| sort timestamp desc
| limit 10

[sinisa_zubic]

Hi @janne_olkoniemi 

Are you looking for this query?

fetch events
| filter event.kind == "DAVIS_EVENT" and event.type == "PROCESS_RESTART"
| lookup [
  fetch dt.entity.process_group_instance
  | fieldsAdd host = belongs_to[dt.entity.host]
  | fieldsAdd host.name = lookup([fetch dt.entity.host | fields id, entity.name, osArchitecture], sourceField:host, lookupField:id)[entity.name]
], sourceField:dt.entity.process_group_instance, lookupField:id,fields: { host.name }
| fields  timestamp, dt.entity.process_group_instance.name,host.name

 Best,
Sini

[janne_olkoniemi]

Hi @sinisa_zubic,

Thanks for your suggestion and I was taking few days off from my work so took some time to answer .

The query itself seems to work but it just takes a quite a lot of time on our environment and if I try to pull data from the past 7 days it shoots the following error after a while.

The lookup command's subquery read too much data. Please continue to filter the lookup table or narrow the query time range.

But thanks for pointing it out how to utilize the lookup and now just need to find some way to make the query more lightweight. 

[sinisa_zubic]

I have tried to optimize the query and instead of lookup and I am using now join and append. Can you try this query out?

fetch events, scanLimitGBytes:-1
| fieldsAdd dt.system.bucket
| filter event.kind == "DAVIS_EVENT" and event.type == "PROCESS_RESTART"
| fieldsAdd dt.entity.process_group_instance = arrayFirst(affected_entity_ids)
| JOIN [
  fetch dt.entity.process_group_instance
  | fields dt.entity.host = belongs_to[dt.entity.host], dt.entity.process_group_instance = id
  | append[
    fetch dt.entity.host
    | fields dt.entity.host = id, hostname = entity.name
  ]
  | summarize {hostname = takeAny(hostname), dt.entity.process_group_instance = takeAny(dt.entity.process_group_instance)}, by:{dt.entity.host}
], on:{dt.entity.process_group_instance}, 
  fields:{
    hostname
  }
| fields  timestamp, dt.entity.process_group_instance.name,hostname