How do I extract one set from another?

[danielbb]

I have something like -

index=os_solaris sourcetype=cpu | stats count by host 
| join type=left host [|search index=os_solaris sourcetype=vmstat | stats count by host ]

I actually like to substract the output of index=os_solaris sourcetype=vmstat | stats count by host from the bigger set of index=os_solaris sourcetype=cpu | stats count by host

How can I do that?

[DalJeanis · Accepted solution]

Try something like

 index=os_solaris ( sourcetype=cpu OR sourcetype=vmstat )
| eval cpucount=case(sourcetype="cpu",1)
| eval vmcount=case(sourcetype="vmstat",1)
| stats sum(cpucount) as cpucount sum(vmcount) as vmcount by host
| eval diffcount=cpucount-vmcount

It can be written more succinctly, as this

 index=os_solaris ( sourcetype=cpu OR sourcetype=vmstat )
| stats sum(eval(case(sourcetype="cpu",1)) as cpucount 
    sum(eval(case(sourcetype="vmstat",1))) as vmcount 
    sum(eval(case(sourcetype="cpu",1,sourcetype="vmstat",-1))) as diffcount 
    by host

[danielbb]

Really neat @DalJeanis

[danielbb]

How to get subsearch to return a result which is NOT EQUAL to the returned value?

This thread relates to it...

[13tsavage]

Okay, not what I thought you were wanting. That is pretty neat, does that previous question help you at all?

[13tsavage]

Are you wanting a total count of hosts from vmstat minux the total count of hosts from cpu? Give this a try:

**
index=os_solaris sourcetype=cpu | where host!="" | stats count as Count1
| join type=left host [|search index=os_solaris sourcetype=vmstat | where host!="" | stats count as Count2]
| eval Total=(Count2 - Count1)
| fields Total
**

First I got the total count of events from sourcetype=cpu and where host field is not empty and named that count as Count1.
Second I got the total count of events from sourcetype=vmstat and where host field is not empty and named that count as Count2.
Next I create a new field called Total and take the total count of Count2 and subtract Count1 from that value.
Finally I just print the Total field to get a single integer.

[danielbb]

What I need is the list of hosts subtracted from the other one -

index=os_solaris sourcetype=cpu | stats count by host returns 100 hosts and the other one returns 40, the subtraction should return the list of 60 hosts.