Splunk Search

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

dimoklis
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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
Explorer

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

lguinn2
Legend

Can you post your search?

0 Karma

dimoklis
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

sundareshr
Legend

Is total number of periods a constant?

0 Karma

dimoklis
Explorer

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

0 Karma

sundareshr
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
Get Updates on the Splunk Community!

Tech Talk | 3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...

Thank You for Celebrating CX Day with Splunk!

Yesterday the entire team at Splunk + Cisco joined the global celebration of CX Day - celebrating our ...

App Building 101 - Build Your First App!

WATCH RECORDING NOW   Tech Talk: App Dev Edition Splunk has tons of out-of-the-box functionality, and you’ve ...