How to edit my search to exclude results with a value of 0 (zero) for a field?

[bobendorfer]

I want to exclude CompletedConnections with a value of 0 in the below string.

sourcetype ______________ | stats count(eval(connStatus=="CLIENT_ACCEPTED")) as ConnectionAttempt, count(eval(connStatus=="SERVER_CONNECTED")) as CompletedConnections by field1 field2 field3

I have attempted to add:

| where count>0       
| where CompletedConnections > 0     
| where NOT CompletedConnections="0"

The search does not spit out errors, but I am not getting any statistical results with the added where commands.

1. How can I exclude CompletedConnections with a 0 value?
2. Is there a better way to perform the stats command to exclude 0 values?

[javiergn · Accepted solution]

Try using search instead of where:

 sourcetype ______________ 
| stats count(eval(connStatus=="CLIENT_ACCEPTED")) as ConnectionAttempt, count(eval(connStatus=="SERVER_CONNECTED")) as CompletedConnections by field1 field2 field3
| search NOT CompletedConnections=0

With regards to why your other three filters don't work, I can only answer the first one:

 | where count>0  --> There's no field named count
 | where CompletedConnections > 0 --> Not sure without looking at your output. In principle looks correct to me. Maybe there are nulls somewhere?
 | where NOT CompletedConnections="0" --> Same as above. 

[sundareshr]

You could also maybe try | where isnotnull(CompletedConnections)

[bobendorfer]

Thanks for the replies.

After further review my Field3 was the cause of my lack of results.

The below where command worked.

| where NOT CompletedConnections="0"