Best way to combine these searches

[ohlafl]

The following query...

index=os host=* (source=cpu NOT cpu="all") OR source=vmstat OR source=df | stats max(cpu) as cpus, max(memTotalMB) as memoryMB, dc(Filesystem) as drives by host | eval cpus=if(isnum(cpus), tostring(cpus + 1)." cores", "N/A") | eval memoryGB=tostring(round((memoryMB/1000), 0))." GB" | eval drives=if(drives=0, "N/A", tostring(drives)." drives") | fields host, cpus, memoryGB, drives | rename host as Node | rename cpus as "Available CPU cores" | rename memoryGB as "Available RAM" | rename drives as "Available drives"

... displays some information about a specific host in table. I would like to expand this table with a column containing information about the hosts total disk size which I get by doing:

index=os host=* sourcetype=df | `convert_memk(Avail, cap)` | stats first(cap) as cap by Filesystem host | stats sum(cap) as cap by host | eval cap=if(cap=0, "N/A", round(cap/(1024*1024),0) . " GB")

What would in your opinion be the best and most effective way of combining these two searches? Since the latter one filters both by Filesystem and host you'd have to change the approach in order to get it to play with the first one without getting cores, memory etc. filtering by Filesystem as well.

[lguinn2 · Accepted solution]

[Edited to fix logic problem]

You could do this, which combines the two searches into a single report. This also makes a single pass through the data, which is probably a bit more efficient than running two searches:

index=os host=* (source=cpu NOT cpu="all") OR source=vmstat OR source=df OR sourcetype=df  
| `convert_memk(Avail, cap) `
| eval Filesystem = if(isnull(Filesystem),"no fs",Filesystem)
| stats max(cpu) as cpu, max(memTotalMB) as memTotalMB, first(cap) as cap by Filesystem host
| stats max(cpu) as cpus, max(memTotalMB) as memoryMB,count(eval(Filesystem!="no fs")) as drives, sum(cap) as cap by host 
| eval cpus=if(isnum(cpus), tostring(cpus + 1)." cores", "N/A") 
| eval memoryGB=tostring(round((memoryMB/1000), 0))." GB" 
| eval drives=if(drives=0, "N/A", tostring(drives)." drives") 
| fields host, cpus, memoryGB, drives 
| rename host as Node | rename cpus as "Available CPU cores" 
| rename memoryGB as "Available RAM" | rename drives as "Available drives"

[ohlafl]

I like this approach but the information on memTotalMB and cpu cores is not passed down the road since stats max(cpu) as cpu, max(memTotalMB) as memTotalMB, first(cap) as cap by Filesystem host outputs hosts, filesystems and then nothing for the columns cpus, memTotalMBs but does work for drives and cap. I guess we're back at the problem trying to sort something after something that is not associated?

[lguinn2]

Edited the answer to address this problem.

What do you get if you just run this:

index=os host=* (source=cpu NOT cpu="all") OR source=vmstat OR source=df OR sourcetype=df  
| `convert_memk(Avail, cap)` 
| eval Filesystem = if(isnull(Filesystem),"no fs",Filesystem)
| stats max(cpu) as cpu, max(memTotalMB) as memTotalMB, first(cap) as cap by Filesystem host

[ohlafl]

Thank you! This works perfectly apart from some naming issues with the lasts lines, the final solution is:

    index=os host=* (source=cpu NOT cpu="all") OR source=vmstat OR source=df OR sourcetype=df
| `convert_memk(Avail, nodeDiskCapacityByte) ` 
| eval Filesystem = if(isnull(Filesystem),"N/A", Filesystem) 
| stats max(cpu) as cpu, max(memTotalMB) as memTotalMB, first(nodeDiskCapacityByte) as nodeDiskCapacityByte by Filesystem, host 
| stats max(cpu) as cpus, max(memTotalMB) as memoryMB, count(eval(Filesystem!="N/A")) as drives, sum(nodeDiskCapacityByte) as nodeDiskCapacityByte by host 
| eval cpus=if(isnum(cpus), tostring(cpus+1)." cores", "N/A") 
| eval memoryGB=tostring(round((memoryMB/1000), 0))." GB" 
| eval drives=if(drives=0, "N/A", tostring(drives)." drives") 
| eval nodeDiskCapacityGB=round((nodeDiskCapacityByte/1000000), 0)." GB" 
| fields host, cpus, memoryGB, drives, nodeDiskCapacityGB 
| rename host as Node 
| rename cpus as "Available CPU cores" 
| rename memoryGB as "Available memory" 
| rename drives as "Number of drives" 
| rename nodeDiskCapacityGB as "Total node disk space"

This was also applicable on another problem I had, so thanks once again.

[woodcock]

Based on your last paragraph (with which I totally agree), IMHO, there is no good reason to attempt to combine the searches. What would be the point?

[ohlafl]

Because I want the table for each host to show the number of cores, total available RAM, number of drives and then the total available disk size. Sadly I have no control of how the information is being fed to Splunk, normally I'd probably just change the logs so that the total disk size is passed in a more accessible way but at the moment the second query is the only way.