How to count rows that have non-zero values

[mahbs]

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!

[woodcock]

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

[DalJeanis]

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

[somesoni2 · Accepted solution]

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.