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

[JoshuaJohn]

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?

[harsmarvania57 · Accepted solution]

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

[JoshuaJohn]

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.