Splunk Search

## How to split count of field1 where (field2=value1) and count of field1 where (field2=value2) without appending? 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".

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)
``````

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

Any Ideas, would prefer not to append if possible?

Tags (3)
1 Solution  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
``````  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
`````` 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
`````` Get Updates on the Splunk Community!