topic Re: How to count rows that have non-zero values in Splunk Search

How to count rows that have non-zero values

mahbs — Wed, 06 Dec 2017 16:54:42 GMT

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!

Re: How to count rows that have non-zero values

somesoni2 — Wed, 06 Dec 2017 19:00:49 GMT

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.

Re: How to count rows that have non-zero values

DalJeanis — Wed, 06 Dec 2017 22:15:53 GMT

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

Re: How to count rows that have non-zero values

woodcock — Fri, 08 Dec 2017 07:25:48 GMT

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