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!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...