Splunk Search

How to split count of field1 where (field2=value1) and count of field1 where (field2=value2) without appending?

JoshuaJohn
Contributor

I want to find how many times an event happens based on the value of another field. Basically a count of IP addresses when they are "Connected" or "Disconnected".

CA is the IP address
SE is what tells me if they are connected/disconnected

I feel like this should be simple, but I have tried a few things to no avail:

| stats latest(SE) as SE by CA
| stats distinct_count(CA) as count_CA by SE
| stats dc(eval(if(SE=DISCONNECTED, count_CA, NULL))) AS disconnected_CA, dc(eval(if(SE=CONNECTED, count_CA, NULL))) AS connected_CA

Provides 0 in both fields (Not true)

| stats latest(SE) as SE by CA
| eval disconnectedCheck = if(SE==DISCONNECTED, disconnected_SE, disconnectedCheck)
| stats dc(CA) as disconnected_CA by disconnected_SE
| stats values(disconnected_CA)

No Results

| stats latest(SE) as SE by CA
| stats distinct_count(CA) as count_CA by SE
| eventstats sum(count_CA) as total_CA
| search SE = "DISCONNECTED" 
| eventstats values(count_CA) as disconnected_CA
| search SE = "CONNECTED" 
| eventstats values(count_CA) as connected_CA, values(total_CA) as total_CA
| eval Percentage=round((disconnected_CA/total_CA)*100,2)
| table disconnected_CA, total_CA, connected_CA, Percentage

No results

Any Ideas, would prefer not to append if possible?

0 Karma
1 Solution

harsmarvania57
SplunkTrust
SplunkTrust

Hi,

I didn't understand your question properly but have you tried something like this

<yoursearch>
| stats sum(eval(if(SE="DISCONNECTED",1,0))) as disconnected_count, sum(eval(if(SE="CONNECTED",1,0))) as connected_count

View solution in original post

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi,

I didn't understand your question properly but have you tried something like this

<yoursearch>
| stats sum(eval(if(SE="DISCONNECTED",1,0))) as disconnected_count, sum(eval(if(SE="CONNECTED",1,0))) as connected_count
0 Karma

JoshuaJohn
Contributor

Close this worked for me

| stats sum(eval(if(SE="DISCONNECTED",count_CA,0))) as disconnected_count, sum(eval(if(SE="CONNECTED",count_CA,0))) as connected_count

Feel free to change your comment into an answer, I can mark it as answered.

0 Karma
Get Updates on the Splunk Community!

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, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...