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

[iamsplunker31]

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

[iamsplunker31 · Accepted solution]

|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+60*60*24*7
| eval label="Colomn2"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-14d@d latest=-14d@d
| eval _time=_time+60*60*24*14
| eval label="Colomn3"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-21@d latest=-21@d
| eval _time=_time+60*60*24*21
| eval label="Colomn4"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-28@d latest=-28@d
| eval _time=_time+60*60*24*28
| 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

[woodcock]

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

[ramgnisiv]

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

[mayurr98]

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

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

[iamsplunker31]

@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+60*60*24*7
| eval label="Colomn2"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval _time=_time+60*60*24*14
| eval label="Colomn3"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval _time=_time+60*60*24*21
| eval label="Colomn4"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=XX latest=XX
| eval _time=_time+60*60*24*28
| 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"

[mayurr98]

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

[iamsplunker31]

@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.

[mayurr98]

could you please send me your entire query?

[iamsplunker31]

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+60*60*24*7
| eval label="Colomn2"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-14d@d latest=-14d@d
| eval _time=_time+60*60*24*14
| eval label="Colomn3"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-21@d latest=-21@d
| eval _time=_time+60*60*24*21
| eval label="Colomn4"
| fields -_raw _time apple label ]
[ search index=foo sourcetype=bar earliest=-28@d latest=-28@d
| eval _time=_time+60*60*24*28
| 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"