Splunk IT Service Intelligence

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
Explorer
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
0 Karma

DavidHourani
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...