HI Team, I need to optimize this query. we need if any host get high from last 15 min so alert should trigger if the utilization is high then 85 and less then 90 and 2. if the utilization is high then 90 for CPU and memory usage

[Hemant1]

index=vmware* (sourcetype=vmware:perf:cpu OR sourcetype=vmware:perf:mem) source=*virtual* |table p_average_cpu_usage_percent, p_average_mem_usage_percent , moid, host, _time|bin span=5m _time |rename avg(p_average_cpu_usage_percent) as "cpuUsage",avg(p_average_mem_usage_percent) as "memUsage"| lookup Entity moid AS moid, host AS host | stats avg(p_average_cpu_usage_percent), avg(p_average_mem_usage_percent) by moid,host,_time,lowername,name,type | rename avg(p_average_cpu_usage_percent) as "cpuUsage",avg(p_average_mem_usage_percent) as "memUsage"| stats count(eval(cpuUsage>=85 AND cpuUsage<90 )) as "OverThresholdCpu", count(cpuUsage) as "TotalCountCpu",count(eval(memUsage>=85 AND memUsage<90)) as "OverThresholdMem", count(memUsage) as "TotalCountMem" ,count(eval(cpuUsage>=90)) as "OverThresholdCpu90" ,count(eval(memUsage>=90)) as "OverThresholdMem90" by lowername,host|eval procentOverMem=if(OverThresholdMem>=2,1,0)|eval procentOverMem90=if(OverThresholdMem90>=2,1,0)|eval procentOverCpu90=if(OverThresholdCpu90>=2,1,0)|eval procentOverCpu=if(OverThresholdCpu>=2,1,0)| fields lowername host procentOverCpu procentOverCpu90 procentOverMem procentOverMem90 | rename lowername as hostName

[DavidHourani]

Hi @Hemant1,

I didnt understand your question, maybe if you put it in a text it would be easier. But I took a shot at improving your search, try this :

 index=vmware* (sourcetype=vmware:perf:cpu OR sourcetype=vmware:perf:mem) source=*virtual*
|bin span=5m _time
| stats avg(p_average_cpu_usage_percent) as "cpuUsage", avg(p_average_mem_usage_percent) as "memUsage" by moid,host,_time
| lookup Entity moid AS moid, host AS host OUTPUTNEW lowername
| stats count(eval(cpuUsage>=85 AND cpuUsage<90 )) as "OverThresholdCpu", count(cpuUsage) as "TotalCountCpu",count(eval(memUsage>=85 AND memUsage<90)) as "OverThresholdMem", count(memUsage) as "TotalCountMem" ,count(eval(cpuUsage>=90)) as "OverThresholdCpu90" ,count(eval(memUsage>=90)) as "OverThresholdMem90" by lowername,host
|eval procentOverMem=if(OverThresholdMem>=2,1,0)
|eval procentOverMem90=if(OverThresholdMem90>=2,1,0)
|eval procentOverCpu90=if(OverThresholdCpu90>=2,1,0)
|eval procentOverCpu=if(OverThresholdCpu>=2,1,0)
| rename lowername as hostName

Also depending if you are okay with running the lookup on the host without the moid field only then this will even run faster :

index=vmware* (sourcetype=vmware:perf:cpu OR sourcetype=vmware:perf:mem) source=*virtual*
|bin span=5m _time
| stats avg(p_average_cpu_usage_percent) as "cpuUsage", avg(p_average_mem_usage_percent) as "memUsage" by host,_time
| stats count(eval(cpuUsage>=85 AND cpuUsage<90 )) as "OverThresholdCpu", count(cpuUsage) as "TotalCountCpu",count(eval(memUsage>=85 AND memUsage<90)) as "OverThresholdMem", count(memUsage) as "TotalCountMem" ,count(eval(cpuUsage>=90)) as "OverThresholdCpu90" ,count(eval(memUsage>=90)) as "OverThresholdMem90" by host
| lookup Entity host  OUTPUTNEW lowername
|eval procentOverMem=if(OverThresholdMem>=2,1,0)
|eval procentOverMem90=if(OverThresholdMem90>=2,1,0)
|eval procentOverCpu90=if(OverThresholdCpu90>=2,1,0)
|eval procentOverCpu=if(OverThresholdCpu>=2,1,0)
| rename lowername as hostName

Let me know if that helps please provide some text explanation (not in the title) so I can help you make it even better.

Cheers,
David