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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...