Splunk Search

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

Explorer

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.

1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

Explorer

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

Legend

Can you post your search?

0 Karma

Explorer

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

0 Karma

Legend

Is total number of periods a constant?

0 Karma

Explorer

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

0 Karma

Legend

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!