topic Join SPL result to a single line? in Splunk Search

Join SPL result to a single line?

wbolten — Tue, 11 Aug 2020 12:20:49 GMT

Hi,

The following SPL returns records to me as shown below.

index="uf_basickpi" host!=DS-* (sourcetype="CPU" counter="% Processor Time") OR (sourcetype="Memory" counter="Available MBytes") OR (sourcetype="DiskStuff" counter="% Free Space" instance=C:) | stats latest(Value) as Value by host, counter | eval "CPU Time" = if(counter="% Processor Time",Value,0) | eval "RAM Available" = if(counter="Available MBytes",Value,0) | eval "C Free Space" = if(counter="% Free Space",Value,0) | table host, "CPU Time", "RAM Available", "C Free Space"

Rows 1,2 and 3 are from the same server. Rows 4,5 and 6 from the second server.

What I would like to have is a single row per server with the three values. What would be the best way to do this.

Re: Join SPL result to a single line?

richgalloway — Tue, 11 Aug 2020 12:41:56 GMT

The stats command can merge the rows.

index="uf_basickpi" host!=DS-* (sourcetype="CPU" counter="% Processor Time") OR (sourcetype="Memory" counter="Available MBytes") OR (sourcetype="DiskStuff" counter="% Free Space" instance=C:) | stats latest(Value) as Value by host, counter | eval "CPU Time" = if(counter="% Processor Time",Value,0) | eval "RAM Available" = if(counter="Available MBytes",Value,0) | eval "C Free Space" = if(counter="% Free Space",Value,0) | stats values(*) as * by host | table host, "CPU Time", "RAM Available", "C Free Space"

Re: Join SPL result to a single line?

wbolten — Tue, 11 Aug 2020 12:47:06 GMT

I did not know that 😉

With your suggestion I did get a single row but the 0 values where in there as well. Replaced them with isnull and now I only have values and a single two per server.

| stats latest(Value) as Value by host, counter | eval "CPU Time" = if(counter="% Processor Time",Value,isnull) | eval "RAM Available" = if(counter="Available MBytes",Value,isnull) | eval "C Free Space" = if(counter="% Free Space",Value,isnull) | stats Values(*) as * by host | table host, "CPU Time", "RAM Available", "C Free Space"

Thanks