Dashboards & Visualizations
Highlighted

How to write a condition for dashboard to ignore "0" Values?

Path Finder

I have a dashboard with 6 colomns which displays the details of certain data. When we select specific time period with span of 5 min, colomn 1 has zeros for specific period say 10 pm to 2 am. I would like to write a condition when colomn 1 has zeros for specific time period "0" value should be ignored/remove the entire row

Please Advise

0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

SplunkTrust
SplunkTrust

have you tried | where column1!="0" AND timeperiod ?

Give us sample input of how does your table looks like for better query.

0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

Path Finder

@mayurr98 : Thank you for your response. | where column1!="0" didn't work for me . Here is the sample input

| multisearch
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval label="Colomn1"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval time=time+6060247
| eval label="Colomn2"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval time=time+60602414
| eval label="Colomn3"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval _time=
time+60
602421
| eval label="Colomn4"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval _time=
time+60
602428
| eval label="Colomn5"
| fields -_raw _time apple label]
| bin _time span=5m
| chart dc(apple) over _time by label
| fields _time "Colomn1" "Colomn2" "Colomn3" "Colomn4" "Colomn5"

0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

SplunkTrust
SplunkTrust

try this:

|  eval time=strftime(_time,"%H") 
|  where NOT (time>=10 AND time<=14 AND column1=0)

OR

|  eval time=strftime(_time,"%H") 
|  where time<10 AND time>14 AND column1!=0
0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

Path Finder

@mayurr98 : Tried both but it didn't work it's throwning an error

Error in 'where' command: Typechecking failed. The '==' operator received different types.
Error in 'where' command: Typechecking failed. The '!=' operator received different types.

0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

SplunkTrust
SplunkTrust

could you please send me your entire query?

0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

Path Finder

Hi @mayurr98 : Here is the entire query. Thanks

| multisearch
[ search index=foo sourcetype=bar earliest=-0d@d latest=now
| eval label="Colomn1"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-7d@d latest=-7d@d
| eval time=time+6060247
| eval label="Colomn2"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-14d@d latest=-14d@d
| eval time=time+60602414
| eval label="Colomn3"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-21@d latest=-21@d
| eval _time=
time+60
602421
| eval label="Colomn4"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-28@d latest=-28@d
| eval _time=
time+60
602428
| eval label="Colomn5"
| fields -_raw _time apple label]
| bin _time span=5m
| chart dc(apple) over _time by label
| eval "Colomn1 4 Week Avg"=('Colomn2'+Colomn3+Colomn4+Colomn5)/4
| eval "Colomn2 4 Week Avg"=abs('Colomn2'-'Colomn1')
| eval "Diff of Colomn1 and 4 Week Avg."=abs('Colomn5'-'Colomn1')
| eval Est
Impact= abs('Diff of Colomn1 and Colomn2'-'Colomn5.')
| fields _time "Colomn1" "Colomn2" "Colomn3" "Colomn4" "Colomn5"

0 Karma
Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

Esteemed Legend

Your search is way too complex and very inefficient. It needs to be completely rewritten. What exactly are you trying to do? For example, surely your XX values are not the same on each search, right? If so, your search makes no sense. Why are you fake-timeshifting _time? Are you aware of the timewrap command?
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timewrap

Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

Path Finder

Timewrap would get rid of line 1 - 21 in a single go.
For removing empty buckets from timespans, please consider the cont=false COMMAND

See this question+ answer: https://answers.splunk.com/answers/73385/how-to-remove-empty-buckets-in-timechart.html

Highlighted

Re: How to write a condition for dashboard to ignore "0" Values?

Path Finder

|search colomn1!=0 worked for me

| multisearch
[ search index=foo sourcetype=bar earliest=-0d@d latest=now
| eval label="Colomn1"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-7d@d latest=-7d@d
| eval time=time+6060247
| eval label="Colomn2"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-14d@d latest=-14d@d
| eval time=time+60602414
| eval label="Colomn3"
| fields -raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-21@d latest=-21@d
| eval _time=
time+60
602421
| eval label="Colomn4"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-28@d latest=-28@d
| eval _time=
time+60
602428
| eval label="Colomn5"
| fields -_raw _time apple label]
| bin _time span=5m
| chart dc(apple) over _time by label
| eval "Colomn1 4 Week Avg"=('Colomn2'+Colomn3+Colomn4+Colomn5)/4
| eval "Colomn2 4 Week Avg"=abs('Colomn2'-'Colomn1')
| eval "Diff of Colomn1 and 4 Week Avg."=abs('Colomn5'-'Colomn1')
| eval Est
Impact= abs('Diff of Colomn1 and Colomn2'-'Colomn5.')
| fields _time "Colomn1" "Colomn2" "Colomn3" "Colomn4" "Colomn5"
|search colomn1!=0

View solution in original post

0 Karma