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

[sam_jacob]

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

[sam_jacob · Accepted solution]

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]

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]

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

[ppablo]

great, thanks @sam_jacob cheers!