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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...