How to autofill rows in a table, even if there are no values produced by streamstats?

[dimoklis]

Hello,

I have an output table like below from a streamstats call on my events:

period    total   cummulative_total
1             14           14
3             15           29
4              5           34
6             10           44

where periods are time spans of 2h and total and cumulative total are sums from stats and streamstats respectively.

What I would like to ask is whether there is a way to autocomplete data rows for a full set of periods. in the example above periods 2 , 5 ,7....12 are missing as you see. I would like to end up with a table like below:

period    total   cummulative_total
 1             14           14
 2              0           14
 3             15           29
 4              5           34
 5              0           34
 6             10           44
 .              .           .
 .              .           .

so period 2 and 5 as for example take totals as 0, but keep cumulative ones from previous period stats.

Many thanks in advance,
Dimoklis.

[somesoni2 · Accepted solution]

Try something like this

your base search | timechart span=2h sum(quantity) as total by id | untable _time id total | streamstats sum(total) as cumulative_total by id| eval period=strftime(_time,"%H")

[dimoklis]

Thanks somesoni2, found it yesterday using the same logic as you suggested!

[lguinn2]

Can you post your search?

[dimoklis]

hi and thanks @Iguinn, please see below:

...|bucket _time span=2h |stats sum(quantity) as total by id, _time | streamstats sum(total) as cumulative_total by id| eval period=strftime(_time,"%H")

PS. Ihave tried with timechart which can automatically fill the gaps in time and values but my data gets transposed. I need to maintain the table layout as per the example if possible

[sundareshr]

Is total number of periods a constant?

[dimoklis]

hi sundareshr, yes it is. these are 2h time spans within a day string from 00,02,04,...22 (12 total)

[sundareshr]

Have you tried using the timechart command

| timechart span=2h count as total | streamstats sum(total) as cummulative_total

Look at the fixedrange usenull and cont options for this command

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Timechart