How to remove rows with Zero value?

[auaave]

Hi guys,

I am making a dashboard with Error Duration per RobotId. Since the duration is in seconds, I rounded it to the nearest Minute. In doing this, if the error duration is less than 1 min the error duration is "0".

How can I get rid of the row where the description is equals to "0" duration?

Thanks a lot!

| eval DURATION=round(DURATION/60) 
| chart sum(DURATION) as "DURATION" over DESCRIPTION by ROBOTID
| addtotals | sort Total Desc

[niketn]

Everyone, ** DURATION ** field will not be available in the sample search provided in the question since the chart command has over and by attributes which means the values for ROBOTIDs will be available as fields and not DURATION.

@auaave, You should try the following based on couple of search optimization techniques:

1) Use DURATION, DESCRIPTION and ROBOTID in your base search filter to ensure only events with the three fields present are filtered.
2) Use stats first before eval. This will have two advantages:
(i) Performance improvement as eval should be applied on aggregated data rather than all events.
(ii) DURATION field will be available for filtering. So search filter can be applied upfront to remove the unwanted data.

<YourBaseSearch> DURATION=* DESCRIPTION=* ROBOTID=*
 | stats sum(DURATION) as DURATION by DESCRIPTION ROBOTID
 | eval DURATION=round(DURATION/60) 
 | search DURATION!=0
 | chart sum(DURATION) as "DURATION" over DESCRIPTION by ROBOTID
 | addtotals row=t col=f fieldname=Total
 | fillnull value=0
 | sort - Total Desc

Other changes like fillnull and sort - Total I have suggested based on ideal use case but they are not mandatory to be implemented based on what you are trying to display to the users. For example without fillnull value=0 if you are usingtable, it will show null values. However, if you are using chart, there is a Format Visualization option to fill Null values while displaying the chart (line or area).

Following is a run anywhere search similar to the one in the question based on Splunk's _internal index

index=_internal sourcetype=splunkd log_level=* component=*  date_second=*
| stats sum(date_second) as DURATION by component log_level
| eval DURATION=round(DURATION/60)
| search DURATION!=0
| chart sum(DURATION) as DURATION over component by log_level
| addtotals row=t col=f fieldname=Total
| fillnull value=0
| sort - Total component

Please try out and confirm. You can confirm the performance of this approach vs your current query in the Splunk Search Job Inspector. Do read the documentation on some of the query optimization techniques: http://docs.splunk.com/Documentation/Splunk/latest/Search/Quicktipsforoptimization

[auaave]

@niketnilay, thanks a lot for your help and advise. Learned a lot from and you and the query works well. 🙂

[niketn]

Anytime @auaave, Splunk Answers is a wonderful community, it teaches us something everyday. Keep learning and keep helping others 🙂

[493669 · Accepted solution]

try this:

...|where DURATION!=0

[SG]

Thanks for this idea, this helped me to resolve this issue soon.

[ddrillic]

Couple of cycles less ; -) with DURATION!=0 | eval DURATION=round(DURATION/60) |where DURATION!=0

[auaave]

@ddrillic, thanks a lot for your help! 🙂

[auaave]

@ starcher, yes you are right! I have updated the title. Thanks a lot for your help.

[auaave]

@493669, thanks! I used where Total!=0 instead

[starcher]

Just some additional information. The title of your post was about null values. Zero is a value. Null is no value at all. if you ever want to test strictly on null or not null without regards to value.

... | where isnull(DURATION)

... | where isnotnull(DURATION)