Hi @StephenLHChan
This query should work for you
timeseries cpuUsage = avg(dt.process.cpu.usage),
by:{host.name, dt.entity.process_group_instance, dt.entity.host}, interval:1h, from:-2h, to:now()
| lookup [
timeseries cpuUsage = avg(dt.process.cpu.usage),
by:{host.name, dt.entity.host, dt.entity.process_group_instance},interval:1h, from:-2h, to:now()
| fieldsAdd cpuUsage = arrayAvg(cpuUsage)
| filter cpuUsage != 0
| sort dt.entity.host asc, cpuUsage desc
| summarize cpu=collectArray(cpuUsage), by: dt.entity.host
| fieldsadd threshold= if(arraySize(cpu)>= 5,cpu[4],else:0)
| fieldsadd threshold= if(arraySize(cpu) == 4,cpu[3],else:threshold)
| fieldsadd threshold=if(arraySize(cpu)== 3,cpu[2],else:threshold)
| fieldsadd threshold=if(arraySize(cpu)== 2,cpu[1],else:threshold)
| fieldsadd threshold=if(arraySize(cpu)== 1,cpu[0],else:threshold)
| fields dt.entity.host, threshold
], sourceField:dt.entity.host, lookupField:dt.entity.host
| filter arrayAvg(cpuUsage) >= lookup.threshold
| fields cpuUsage, host.name, timeframe, interval, dt.entity.process_group_instance
| lookup [fetch dt.entity.process_group_instance], sourceField:dt.entity.process_group_instance, lookupField:id
| fieldsRename process_group_instance.name = lookup.entity.name
| fieldsRemove lookup.id
| sort host.name asc, cpuUsage desc
| fields start=timeframe[start], end=timeframe[end], Host=host.name, Process=process_group_instance.name, arrayAvg(cpuUsage)
I have adapted the query a bit. For every process, I am doing here a lookup to find out what is the cpu threshold of the top processes on the host. After you have the threshold, you just filter to be above or equal to the top5 processes.
Regarding DQL limitations and the lookup command. One of its main purposes is to “lookup” the entity name for a certain entity id. So it is not a proper join command as you might know it from SQL. Therefore it has a limit when it comes to reading data.
Best,
Sini