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
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)
DURATION ** field will not be available in the sample search provided in the question since the
chart command has
by attributes which means the
values for ROBOTIDs will be available as fields and
@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
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 using
table, 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
Anytime @auaave, Splunk Answers is a wonderful community, it teaches us something everyday. Keep learning and keep helping others 🙂