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
...
I would be grateful for any pointers. Thanks.
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
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).
If it works, come back here and click Accept
to close the question.
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.
Sorry, did not notice that you are not OP.
Hi, Did you ever figure this out?
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.
New link to fillnull doc: https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Fillnull
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?