https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228514#M67586 <P>Hello,</P> <P>I have an output table like below from a streamstats call on my events:</P> <PRE><CODE>period total cummulative_total 1 14 14 3 15 29 4 5 34 6 10 44 </CODE></PRE> <P>where periods are time spans of 2h and total and cumulative total are sums from stats and streamstats respectively. </P> <P>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:</P> <PRE><CODE>period total cummulative_total 1 14 14 2 0 14 3 15 29 4 5 34 5 0 34 6 10 44 . . . . . . </CODE></PRE> <P>so period 2 and 5 as for example take totals as 0, but keep cumulative ones from previous period stats.</P> <P>Many thanks in advance,<BR /> Dimoklis.</P> Mon, 11 Jan 2016 14:25:35 GMT dimoklis 2016-01-11T14:25:35Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228514#M67586 <P>Hello,</P> <P>I have an output table like below from a streamstats call on my events:</P> <PRE><CODE>period total cummulative_total 1 14 14 3 15 29 4 5 34 6 10 44 </CODE></PRE> <P>where periods are time spans of 2h and total and cumulative total are sums from stats and streamstats respectively. </P> <P>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:</P> <PRE><CODE>period total cummulative_total 1 14 14 2 0 14 3 15 29 4 5 34 5 0 34 6 10 44 . . . . . . </CODE></PRE> <P>so period 2 and 5 as for example take totals as 0, but keep cumulative ones from previous period stats.</P> <P>Many thanks in advance,<BR /> Dimoklis.</P> Mon, 11 Jan 2016 14:25:35 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228514#M67586 dimoklis 2016-01-11T14:25:35Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228515#M67587 <P>Is total number of periods a constant?</P> Mon, 11 Jan 2016 15:19:11 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228515#M67587 sundareshr 2016-01-11T15:19:11Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228516#M67588 <P>hi sundareshr, yes it is. these are 2h time spans within a day string from 00,02,04,...22 (12 total)</P> Mon, 11 Jan 2016 15:33:52 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228516#M67588 dimoklis 2016-01-11T15:33:52Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228517#M67589 <P>Have you tried using the <CODE>timechart</CODE> command</P> <PRE><CODE>| timechart span=2h count as total | streamstats sum(total) as cummulative_total </CODE></PRE> <P>Look at the <CODE>fixedrange</CODE> <CODE>usenull</CODE> and <CODE>cont</CODE> options for this command</P> <P><A href="http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Timechart">http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Timechart</A></P> Mon, 11 Jan 2016 15:40:15 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228517#M67589 sundareshr 2016-01-11T15:40:15Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228518#M67590 <P>Can you post your search?</P> Tue, 12 Jan 2016 06:32:33 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228518#M67590 lguinn2 2016-01-12T06:32:33Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228519#M67591 <P>hi and thanks @Iguinn, please see below:</P> <PRE><CODE>...|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") </CODE></PRE> <P>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</P> Tue, 12 Jan 2016 16:43:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228519#M67591 dimoklis 2016-01-12T16:43:08Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228520#M67592 <P>Try something like this</P> <PRE><CODE>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") </CODE></PRE> Tue, 12 Jan 2016 17:35:26 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228520#M67592 somesoni2 2016-01-12T17:35:26Z https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228521#M67593 <P>Thanks somesoni2, found it yesterday using the same logic as you suggested!</P> Wed, 13 Jan 2016 10:25:16 GMT https://community.splunk.com/t5/Splunk-Search/How-to-autofill-rows-in-a-table-even-if-there-are-no-values/m-p/228521#M67593 dimoklis 2016-01-13T10:25:16Z