Splunk Search

How to count rows that have non-zero values

mahbs
Path Finder

Hi,

I'm trying to count the number of rows in a field that have a non-zero value. I've used replace to do that, but I get the feeling that Splunk is counting the rows that have zero values, which is not what I want it to do.

I know it's not working because I'm running two searches that have the same query, BUT, the source is different. Upon running, I'm getting identical results i.e. the number of rows for both sourcetype is the same.

Query: 1

index=xxx sourcetype=xxx source=yyy | table ITEM, DEPT, *_2017, *_2018 | replace "0" WITH "" IN 03_DEC_2017 | replace "0" WITH "" IN 04_DEC_2017 | replace "0" WITH "" IN 05_DEC_2017 | stats count(*_2017) by DEPT

Query2:

index=xxx sourcetype=xxx source=xxx| table ITEM, DEPT, *_2017, *_2018 | replace "0" WITH "" IN 03_DEC_2017 | replace "0" WITH "" IN 04_DEC_2017 | replace "0" WITH "" IN 05_DEC_2017 | stats count(*_2017) by DEPT

I need to find way of counting rows that are NOT "" OR ideally NOT 0.

Please help!

0 Karma
1 Solution

somesoni2
Revered Legend

Try like this.

index=xxx sourcetype=xxx source=yyy | table ITEM, DEPT, *_2017 
|  eval "03_DEC_2017"=if(tonumber('03_DEC_2017')=0,null(),tonumber('03_DEC_2017')) 
 |  eval "04_DEC_2017"=if(tonumber('04_DEC_2017')=0,null(),tonumber('04_DEC_2017')) 
 |  eval "05_DEC_2017"=if(tonumber('05_DEC_2017')=0,null(),tonumber('05_DEC_2017')) 
 | stats count(*_2017) by DEPT

The value "" that you were using in replace is a non-null value and is being counted using count function in stats. Changing it to null() would ensure it's ignored while doing stats count.

View solution in original post

woodcock
Esteemed Legend

I always check len(fieldname)>0 for this kind of thing because it handles all varieties of blankishness.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Try this...

 index=xxx sourcetype=xxx source=yyy 
| fields ITEM, DEPT, *_2017, *_2018 
| streamstats count as recno
| untable recno fieldname fieldvalue
| where fieldvalue!=0 OR NOT like(fieldname,"%2017")
| xyseries recno fieldname fieldvalue
| stats count(*_2017)  by DEPT 

Probably not this strategy, even if it does work. I don't generally useforeach for large files, but this seems a reasonable use case.

| makeresults | eval dept=mvrange(0,3)| mvexpand dept 
| eval george1 = 3 | eval george2 = 7 | eval george3 = 0 
| table dept george*

| foreach george* [|eval <<FIELD>>=case(<<FIELD>>>0,<<FIELD>>)] 
| stats count(george*) as * by dept
0 Karma

somesoni2
Revered Legend

Try like this.

index=xxx sourcetype=xxx source=yyy | table ITEM, DEPT, *_2017 
|  eval "03_DEC_2017"=if(tonumber('03_DEC_2017')=0,null(),tonumber('03_DEC_2017')) 
 |  eval "04_DEC_2017"=if(tonumber('04_DEC_2017')=0,null(),tonumber('04_DEC_2017')) 
 |  eval "05_DEC_2017"=if(tonumber('05_DEC_2017')=0,null(),tonumber('05_DEC_2017')) 
 | stats count(*_2017) by DEPT

The value "" that you were using in replace is a non-null value and is being counted using count function in stats. Changing it to null() would ensure it's ignored while doing stats count.

View solution in original post

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.