topic How to omit from a timechart series that include only zeroes? in Splunk Search

How to omit from a timechart series that include only zeroes?

anssntaco — Wed, 21 Dec 2011 21:52:42 GMT

I'd like to timechart throughput, by queue, in a message broker:

source="jms-queue" queue_name="SNMPTrapsQueue*" | timechart span=5m per_minute(count_delta) by queue_name

The majority of these queues remain empty. For those queues, throughput is represented by a series of zeroes. I'd like the chart to ignore those series (i.e. the timechart would only display active queues).

Any suggestions?

Re: How to omit from a timechart series that include only zeroes?

lguinn2 — Tue, 27 Dec 2011 12:56:48 GMT

Try

source="jms-queue" 
[search source="jms-queue" earliest=-1h | stats sum(throughput) as total by queue_name | 
  where total > 0 | fields + queue_name] 
| timechart span=5m per_minute(count_delta) by queue_name

The second and third lines define a sub-search, which will return a list of queue_names. The subsearch sums all the throughput for each queue, and only returns the queue_names with > 0 throughput. You will need to replace throughput in the second line with the proper field name. Note that you may also need to change the earliest=-1h to match your requirements (and perhaps add latest= as well).

Re: How to omit from a timechart series that include only zeroes?

Graham_Hanningt — Fri, 29 Apr 2016 08:22:01 GMT

I had the same question, saw the accepted answer, and decided to develop my own solution.

Some reasons not to add my answer:

The question is several years old
An answer has already been accepted
The accepted answer was by someone with high karma points
I'm a Splunk newbie, so I might well be missing something here

Reasons to do it:

My answer performs processing after timechart, which might be more performant than the accepted answer (Lisa, your thoughts on this?)
Even if my answer is wrong, I hope I'll learn something from subsequent comments, probably from @lguinn 🙂

My answer (with line breaks for readability):

... | timechart ... by queue_name
| foreach * [eval nonzero_<<FIELD>> = if('<<FIELD>>'==0 OR "<<FIELD>>"==_time, null(), '<<FIELD>>')]
| fields _time nonzero_*
| rename nonzero_* as *

Here's the "trick": if you assign a null value by calling the function null(), the eval command does not create a field.

So, the foreach command only creates a nonzero_* field if the original field has at least one nonzero value.

The subsequent fields command only keeps the _time field and any nonzero_* fields.

The rename command reverts to the original field names.

Re: How to omit from a timechart series that include only zeroes?

steveyz — Fri, 29 Apr 2016 21:36:59 GMT

Your solution is more efficient that the accepted answer. However, note that expression for NULL in eval is actually a function call of null(). It probably seems to work because NULL is treated as a field with the name NULL, which probably doesn't exist in your environment.

Re: How to omit from a timechart series that include only zeroes?

steveyz — Fri, 29 Apr 2016 21:38:38 GMT

I believe the simplest solution to this is to simply filter out events where count_delta=0, e.g.

source="jms-queue" queue_name="SNMPTrapsQueue*" count_delta>0 | timechart span=5m per_minute(count_delta) by queue_name

Re: How to omit from a timechart series that include only zeroes?

Graham_Hanningt — Mon, 02 May 2016 02:24:07 GMT

Yes, for the original poster's specific use case, based on the information provided here, I agree.

However, while I came here looking for an answer to the same one-liner question, "How to omit from a timechart series that include only zeroes?", my use case is slightly different. In my case, each event represents a database request. Each event contains, among many other fields, nine "suspend clock" fields, representing the different ways in which a database request might be suspended. Requests can be suspended in a combination of these ways. I want to visualize these suspend time "components" together, on a single chart. But I want (as per the question) to omit any series whose values are all zero. So, I can't simply omit events with a zero value in one of these fields.

That was another reason for me to add my answer (although I was hesitant to do so, not wishing to offend or break etiquette): it occurred to me that other users might also have the same (one-liner) question, but with a use case closer to mine.

Re: How to omit from a timechart series that include only zeroes?

Graham_Hanningt — Mon, 02 May 2016 02:25:13 GMT

Face slap :-). Thanks, @steveyz. I've edited my answer and replaced NULL with null() (and tested that this still works).

Re: How to omit from a timechart series that include only zeroes?

lguinn2 — Tue, 10 May 2016 17:50:09 GMT

@Graham_Hannington - never be afraid to add to the discussion! Overall this is a friendly community, and things only get "touchy" around downvoting. (FYI, the general etiquette is to only downvote answers that are wrong or would cause harm if implemented.) Quite frequently, there is more than one way to do things in Splunk - I like it when someone finds a better answer. I love it when someone explains why the new answer is better.

In addition, older answers often need a "tune-up." And I hope that I've learned some stuff in the last 5 years, too!

Re: How to omit from a timechart series that include only zeroes?

lguinn2 — Tue, 10 May 2016 18:08:01 GMT

New answer: if I were writing this search today, I would definitely not use a subsearch. I would use @steveyz's solution if possible.
But here is another way to do it - which will work when the selection field does not already exist, or is not the appropriate way to select events:

source="jms-queue" 
| eventstats sum(throughput) as total by queue_name
| where total > 0 
| timechart span=5m per_minute(count_delta) by queue_name

This will definitely be faster than the original answer. While the "for each" solution may work, it seems overly complex and "feels procedural." I feel this solution aligns better with the "pipeline" perspective of Splunk .

Re: How to omit from a timechart series that include only zeroes?

steveyz — Tue, 10 May 2016 18:19:28 GMT

I would avoid eventstats if the search is over a lot of events. While the "for each" solution is seems overly complicated, it would be far more efficient performance wise (especially for a distributed deployment with many indexers) if dealing with more than a few hundred thousand events.

Re: How to omit from a timechart series that include only zeroes?

lguinn2 — Wed, 11 May 2016 17:52:22 GMT

What about this for efficiency and ease?

source="jms-queue" 
| bin _time span=5m
| stats per_minute(count_delta) as dpm by queue_name _time
| addtotals
| where total > 0
| fields - total
| xyseries _time queue_name dpm

One pass through the data, then eliminate queues that have total count_delta = 0. Finally, put the data in timechart format.