Knowledge Management

How do I extract one set from another?

danielbb
Motivator

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?

Tags (2)
0 Karma
1 Solution

DalJeanis
Legend

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

View solution in original post

DalJeanis
Legend

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
Motivator

Really neat @DalJeanis

danielbb
Motivator
0 Karma

13tsavage
Communicator

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

13tsavage
Communicator

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
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...