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!
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.
I always check len(fieldname)>0
for this kind of thing because it handles all varieties of blankishness.
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
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.