Combining multiple fields for reporting

[Beth]

I'm trying to get my results into a single field called Percent_CPU_Load. However, since the field is defined twice, once as a rename and once as an eval, it will only return results for the second reference. Is there a way to get Percent_CPU_Load to contain values from both references.

index=* sourcetype="wmi:cputime" OR sourcetype=cpu startminutesago=5 | multikv fields pctIdle,PercentProcessorTime  | eval Percent_CPU_Load = 100 - pctIdle |rename PercentProcessorTime as Percent_CPU_Load | chart avg(Percent_CPU_Load) as CPU% by host

[Lowell · Accepted solution]

Your question threw me off for a second. You don't have a "field defined twice" issue, you have two different fields from two different kind of events that you want to consolidate for reporting purposes. This is different from a situation where you are trying to combine two fields within a single event...

I see that you are using standard WMI events and the "cpu" source as created by Splunk's Unix package. So I ran your search on my system and came up with this solution:

index=* sourcetype="wmi:cputime" OR sourcetype=cpu earliest=-5m | multikv fields pctIdle | eval Percent_CPU_Load=coalesce(PercentProcessorTime,100-pctIdle) | chart avg(Percent_CPU_Load) as CPU% by host

You could even consolidate it further: (It's mostly a question of readability and personal preference at this point)

index=* sourcetype="wmi:cputime" OR sourcetype=cpu earliest=-5m | multikv fields pctIdle | chart avg(eval(coalesce(PercentProcessorTime,100-pctIdle))) as CPU% by host

---

One other note. If you have multiple CPUs on your unix boxes, then you will end up with multiple events. This will throw off your totals. So I would suggest that your either keep only the "all" line, or use the per-CPU entries and drop the "all" entry. This gets slightly more complicated because you are effectively combining two different searches here.

It's still quite possible to do though, it just needs some extra attention to detail:

Here is one suggestion:

index=* sourcetype="wmi:cputime" OR sourcetype=cpu earliest=-5m | multikv fields pctIdle | search PercentProcessorTime OR all | eval Percent_CPU_Load=coalesce(PercentProcessorTime,100-pctIdle) | chart avg(Percent_CPU_Load) as CPU% by host

Another way to do this would be to add "CPU" to your list of fields to extract with "multikv", however, I noticed (at least on my systems) that the "CPU" column is inconsitent on for different OSes, (older version of Linux have "PM" as the CPU, and the CPU name is put in the "userPct" field. Whoops) But this issue can easily be avoided by simply searching for the term "all" instead of search for CPU="all".

[gkanapathy]

If you're not on 4.1 or higher, you won't have coalesce(x,y), but you can instead use if(isnull(x),y,x)