topic Re: How to remove rows with Zero value? in Splunk Search

How to remove rows with Zero value?

auaave — Fri, 09 Feb 2018 05:17:53 GMT

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

Re: How to remove rows with Zero value?

493669 — Fri, 09 Feb 2018 05:24:10 GMT

try this:

...|where DURATION!=0

Re: How to remove rows with Zero value?

auaave — Fri, 09 Feb 2018 05:29:21 GMT

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

Re: How to remove rows with Zero value?

starcher — Sat, 10 Feb 2018 18:31:03 GMT

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)

Re: How to remove rows with Zero value?

ddrillic — Sun, 11 Feb 2018 00:56:09 GMT

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

Re: How to remove rows with Zero value?

niketn — Sun, 11 Feb 2018 05:24:43 GMT

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

Re: How to remove rows with Zero value?

auaave — Mon, 12 Feb 2018 22:30:38 GMT

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

Re: How to remove rows with Zero value?

auaave — Mon, 12 Feb 2018 22:31:26 GMT

@ddrillic, thanks a lot for your help! 🙂

Re: How to remove rows with Zero value?

auaave — Mon, 12 Feb 2018 22:32:49 GMT

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

Re: How to remove rows with Zero value?

niketn — Tue, 13 Feb 2018 15:02:03 GMT

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

Re: How to remove rows with Zero value?

SG — Wed, 26 May 2021 11:45:35 GMT

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