Forcing a zero count in Time Chart

[watsm10]

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.

[woodcock]

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

[bwlm]

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).

[woodcock]

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

[bwlm]

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.

[woodcock]

Sorry, did not notice that you are not OP.

[jhartmann9854]

Hi, Did you ever figure this out?

[bwlm]

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.

[kristian_kolb]

fillnull

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

/K

[entpnerd]

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

[watsm10]

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?