Splunk Search

How do I get a correct count using AND/OR statement

jbat
Engager

The following produces a count of 0 for "Other":

|stats count AS Contacts,  
   count(eval((in_value=1 AND duration!="")  
    OR (in_value=2 AND duration!="")  
         OR (out_value=1 AND duration!="")  
         OR (out_value=2 AND duration!=""))) AS Successful,  
   count(eval((in_value=1 AND duration="")  
         OR (in_value=2 AND duration="")  
         OR (out_value=1 AND duration="")  
         OR (out_value=2 AND duration=""))) AS Unsuccessful,  
   count(eval(in_value!=1 AND in_value!=2 AND out_value!=1 AND out_value!=2)) AS Other  
   BY groupCode  
|fields groupCode, Contacts, Successful, Unsuccessful, Other  
|addcoltotals Contacts, Successful, Unsuccessful, Other  

If I change "Other" to the following, the count can and does include some Successfuls:

count(eval((in_value!=1 AND in_value!=2) OR (out_value!=1 AND out_value!=2))) AS Other

If I change all the ANDs to ORs, I get the same count as Successfuls.

Tags (1)
0 Karma

lguinn2
Legend

This may not be an answer, but I think it may be easier to understand...

yoursearchhere
| eval success = if( duration!="" AND (in_value==1 OR in_value==2 OR out_value==1 OR out_value==2),1,0)
| eval fail = if(duration="" AND (in_value==1 OR in_value==2 OR out_value=1 OR out_value==2),1,0)
| eval other = if(in_value!=1 AND in_value!=2 AND out_value!=1 AND out_value!=2,1,0)
| stats count as Contacts, sum(success) as Successful, sum(fail) as Unsuccessful, sum(other) as Other 
     by  groupCode  
| fields groupCode, Contacts, Successful, Unsuccessful, Other  
| addcoltotals Contacts, Successful, Unsuccessful, Other 

As a diagnostic, just do this:

yoursearchhere
| eval success = if( duration!="" AND (in_value==1 OR in_value==2 OR out_value==1 OR out_value==2),1,0)
| eval fail = if(duration="" AND (in_value==1 OR in_value==2 OR out_value=1 OR out_value==2),1,0)
| eval other = if(in_value!=1 AND in_value!=2 AND out_value!=1 AND out_value!=2,1,0)
| table success fail other duration in_value out_value

and you can probably see what's going wrong. Particularly consider what should happen if any of the fields do not exist...

jbat
Engager

This helped - I needed to account for fields not existing. Thanks!

0 Karma

sowings
Splunk Employee
Splunk Employee

Help us understand the relationship (if any) between in_value and out_value.

The fact that you get 0 for Other suggests that the statement with all ANDs cannot be logically true, that is, an event cannot simultaneously have those two fields !=1 AND !=2. Are 1 and 2 the only possible values? Are the values of in_value and out_value linked?

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