Join SPL result to a single line?

[wbolten]

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. 

[richgalloway · Accepted solution]

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"

[wbolten]

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