topic Re: Forcing a zero count in Time Chart in Splunk Search

Forcing a zero count in Time Chart

watsm10 — Mon, 13 May 2013 14:19:43 GMT

I'm producing a report for some service owners. It is designed to give them a breakdown of successes and failures split by the user of our service.

I have the following search:
index=my5msummary | timechart span=1h count by user useother=f limit=100
Which gives the following result:

       _time                       User 1  User 2  User 3  User 4

1   12/05/2013 00:00:00.000         10232   3159    218     1606

2   12/05/2013 01:00:00.000         6043    2670    100     940

3   12/05/2013 02:00:00.000         3252    1472    61      548

...

Unfortunately User 1 doesn't operate on a Sunday. So User 1 disappears from the table of results.

       _time                        User 2  User 3  User 4

1   12/05/2013 00:00:00.000         3159    218     1606

2   12/05/2013 01:00:00.000         2670    100     940

3   12/05/2013 02:00:00.000         1472    61      548

...

What I would like to do is show a count of 0 for User 1 which doesn't operate.

       _time                       User 1  User 2  User 3  User 4

1   12/05/2013 00:00:00.000         0       3159    218     1606

2   12/05/2013 01:00:00.000         0       2670    100     940

3   12/05/2013 02:00:00.000         0       1472    61      548

...

Is there a way to pre-define the "User" field? I've tried eval and inputlookup to no avail. It still doesn't try to count the number of transaction for User 1 (which would be 0).

I would be grateful for any pointers. Thanks.

Re: Forcing a zero count in Time Chart

kristian_kolb — Mon, 13 May 2013 14:23:49 GMT

fillnull

http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/Fillnull

Re: Forcing a zero count in Time Chart

watsm10 — Mon, 13 May 2013 14:31:36 GMT

Thanks, this is close to what I'm after. Is there a way to do this in a multi-value field? Something like | fillnull value=0 User=User1 User=User2.... and so on?

Re: Forcing a zero count in Time Chart

jhartmann9854 — Tue, 01 Dec 2015 21:40:45 GMT

Hi, Did you ever figure this out?

Re: Forcing a zero count in Time Chart

bwlm — Fri, 25 Oct 2019 16:03:17 GMT

Same here... looking for a solution. Fillnull only works if you have empty field events for the bin times.

Update: Actually in my simple case, changing from "| bin _time | stats count by _time" to "| timechart count" did work and fill in empty time bins with the value "0" for charting.

Re: Forcing a zero count in Time Chart

woodcock — Fri, 25 Oct 2019 20:19:01 GMT

Like this (you would probably use |inputlookup MyListOfUsers.csv instead of the |makeresults stuff):

index=my5msummary
| append
[| makeresults 
| eval raw="user=1 user=2 user=3 user=4 user=5"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| eval user= "User " . user
| table user]
| timechart span=1h count(some field name that always exists in index=mySummary here) BY user useother=f limit=100

Re: Forcing a zero count in Time Chart

bwlm — Fri, 25 Oct 2019 23:58:23 GMT

Very interesting concept and approach... this looks like it would work. I just had to reference the "kv" is an alias for the extract command and only works on "_raw" data (in this case, in a subsearch).

Re: Forcing a zero count in Time Chart

woodcock — Sat, 26 Oct 2019 00:06:38 GMT

If it works, come back here and click Accept to close the question.

Re: Forcing a zero count in Time Chart

bwlm — Sat, 26 Oct 2019 02:16:43 GMT

This is not my question so unfortunately I cannot accept the answer, though I did award points! I am unable to reconstruct the use case with timechart (e.g. index=wineventlogs sourctype=wineventlog:security EventCode=4624 | timechart span=1h count by user ) ... in that case I do get the "0" valued filled in for each time span. So I am not sure how the OP's "index=my5msummary" is set up.

Re: Forcing a zero count in Time Chart

woodcock — Sat, 26 Oct 2019 04:08:35 GMT

Sorry, did not notice that you are not OP.

Re: Forcing a zero count in Time Chart

entpnerd — Wed, 30 Oct 2019 23:53:37 GMT

New link to fillnull doc: https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Fillnull