Splunk Search

How to get the average of two fields from two different indexes by time?

sam_jacob
Path Finder

I'm trying to get the average memory and CPU usage by the hour. Unfortunately, that information is stored on two different indexes, so I appended the CPU results with the memory results, and used bucket to get the result hourly. But I'm now getting the average of each hour.

index=[redacted] host=[redacted] sourcetype=[redacted] earliest=-24h@h latest=@h 
| multikv fields memUsedPct 
| append [search index=[redacted] host=[redacted] sourcetype=cpu earliest=-24h@h latest=@h | multikv fields pctIdle | search CPU=all | eval cpuUsedPct=100-pctIdle] 
| sort _time 
| bucket _time span=60m 
| eval Time=strftime(_time, "%m/%d/%y %H:%M")
| stats avg(cpuUsedPct) as "CPU Percent" by Time, avg(memUsedPct) as Percent by Time 

When I execute the search, I get the error: Error in 'stats' command: Repeated group-by field 'Time'. and I'm assuming this is because I get the average of two different fields by Time

Tags (3)
0 Karma
1 Solution

sam_jacob
Path Finder

I was able to figure it out what I was doing wrong. This is what the correct query should look like:

index=[redacted] host=[redacted] sourcetype=[redacted] earliest=-24h@h latest=@h 
| multikv fields memUsedPct 
| append [search index=[redacted] host=[redacted] sourcetype=cpu earliest=-24h@h latest=@h | multikv fields pctIdle | search CPU=all | eval cpuUsedPct=100-pctIdle] 
| sort _time 
| bucket _time span=60m 
| eval Time=strftime(_time, "%m/%d/%y %H:%M")
| stats avg(cpuUsedPct) as "CPU Percent", avg(memUsedPct) as Percent by Time 

View solution in original post

sam_jacob
Path Finder

I was able to figure it out what I was doing wrong. This is what the correct query should look like:

index=[redacted] host=[redacted] sourcetype=[redacted] earliest=-24h@h latest=@h 
| multikv fields memUsedPct 
| append [search index=[redacted] host=[redacted] sourcetype=cpu earliest=-24h@h latest=@h | multikv fields pctIdle | search CPU=all | eval cpuUsedPct=100-pctIdle] 
| sort _time 
| bucket _time span=60m 
| eval Time=strftime(_time, "%m/%d/%y %H:%M")
| stats avg(cpuUsedPct) as "CPU Percent", avg(memUsedPct) as Percent by Time 

ppablo
Retired

Hi @sam_jacob

Could you actually paste your answer/final working search as a formal answer in the "Enter your answer..." box below? That way I can accept that for you as the correct solution to resolve this post instead of it floating around as unanswered. Thanks!

sam_jacob
Path Finder

Thanks, yea I just submitted the answer. Once it gets approved I'll select it.

0 Karma

ppablo
Retired

great, thanks @sam_jacob cheers!

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...