topic Re: How do I extract one set from another? in Knowledge Management

How do I extract one set from another?

danielbb — Fri, 31 Jan 2020 14:58:34 GMT

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?

Re: How do I extract one set from another?

13tsavage — Wed, 30 Sep 2020 03:58:42 GMT

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

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.

Re: How do I extract one set from another?

danielbb — Fri, 31 Jan 2020 15:23:13 GMT

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

This thread relates to it...

Re: How do I extract one set from another?

13tsavage — Fri, 31 Jan 2020 15:43:39 GMT

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

Re: How do I extract one set from another?

danielbb — Fri, 31 Jan 2020 16:47:35 GMT

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.

Re: How do I extract one set from another?

DalJeanis — Fri, 31 Jan 2020 17:24:43 GMT

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

Re: How do I extract one set from another?

danielbb — Fri, 31 Jan 2020 18:40:05 GMT

Really neat @DalJeanis