Dashboards & Visualizations

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

iamsplunker31
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
1 Solution

iamsplunker31
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+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

View solution in original post

0 Karma

iamsplunker31
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+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

0 Karma

woodcock
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

ramgnisiv
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

mayurr98
Super Champion

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

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

0 Karma

iamsplunker31
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+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"

0 Karma

mayurr98
Super Champion

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

iamsplunker31
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

mayurr98
Super Champion

could you please send me your entire query?

0 Karma

iamsplunker31
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+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"

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...