How to copy figures across empty time buckets?

[szal]

I'm generating a timechart that is supposed to display a daily figure which is an accumulation of total logged in hours by users.

I have two problems, firstly some users may login a couple of days and never again which has the result of their total hours not getting added to subsequent days.
Secondly there may be some days (like the 16th & 17th below) that have had no login events whatsoever which has the result of an empty bucket where I'd like to see the total from the previous day instead.

The table of results I'm getting is something like this:
_time OpSys_1
2015-09-12 0
2015-09-13 24
2015-09-14 48
2015-09-15 72
2015-09-16
2015-09-17
2015-09-18 1
2015-09-19 82
2015-09-20 152

These are the results I'd like:
_time OpSys_1
2015-09-12 0
2015-09-13 24
2015-09-14 48
2015-09-15 72
2015-09-16 72
2015-09-17 72
2015-09-18 73 (1 + 72)
2015-09-19 155 (82 + 73)
2015-09-20 307 (152 + 155)

Query:
index=Agate |
streamstats first(LoggedInTotal) as VeryFirstLoggedInTotal last(LoggedInTotal) as VeryLastLoggedInTotal by User OpSys |
bucket _time span=1d |
fields _time VeryFirstLoggedInTotal VeryLastLoggedInTotal LoggedInTotal User OpSys |
stats first(VeryFirstLoggedInTotal) as FirstLoggedInTotal last(LoggedInTotal) as LastLoggedInTotal last(VeryLastLoggedInTotal) as NewVeryLastLoggedInTotal by User OpSys _time |
eval LastMinusFirst_LoggedInTotal=if(isnotnull(LastLoggedInTotal),tonumber(mvindex(split(LastLoggedInTotal, ":"),0))-tonumber(mvindex(split(FirstLoggedInTotal, ":"),0)),tonumber(mvindex(split(NewVeryLastLoggedInTotal, ":"),0))-tonumber(mvindex(split(FirstLoggedInTotal, ":"),0))) |
streamstats sum(LastMinusFirst_LoggedInTotal) as sum_LoggedInTotal_by_OpSys by _time OpSys |
timechart span=1d limit=0 last(sum_LoggedInTotal_by_OpSys) by OpSys

[somesoni2 · Accepted solution]

I think you're almost there, Assuming your current query which gave first output is working just fine, try this (see the last line added)

    index=Agate |  streamstats first(LoggedInTotal) as VeryFirstLoggedInTotal last(LoggedInTotal) as VeryLastLoggedInTotal by User OpSys |   bucket _time span=1d |  fields _time VeryFirstLoggedInTotal VeryLastLoggedInTotal LoggedInTotal User OpSys | 
    stats first(VeryFirstLoggedInTotal) as FirstLoggedInTotal last(LoggedInTotal) as LastLoggedInTotal last(VeryLastLoggedInTotal) as NewVeryLastLoggedInTotal by User OpSys _time | 
    eval LastMinusFirst_LoggedInTotal=if(isnotnull(LastLoggedInTotal),tonumber(mvindex(split(LastLoggedInTotal, ":"),0))-tonumber(mvindex(split(FirstLoggedInTotal, ":"),0)),tonumber(mvindex(split(NewVeryLastLoggedInTotal, ":"),0))-tonumber(mvindex(split(FirstLoggedInTotal, ":"),0))) | 
    streamstats sum(LastMinusFirst_LoggedInTotal) as sum_LoggedInTotal_by_OpSys by _time OpSys | 
    timechart span=1d limit=0 last(sum_LoggedInTotal_by_OpSys) by OpSys
| streamstats sum(*) as *

[szal]

In order to get this working I needed to change the calculation method used for LastMinusFirst_LoggedInTotal so that it didn't accumulate the total for each user across buckets - this part is done by the suggested answer from somesoni2.

[szal]

table 1 above is derived from this data:

User    OpSys   _time   FirstLoggedInTotal  LastLoggedInTotal   NewVeryLastLoggedInTotal    LastMinusFirst_LoggedInTotal
user_2  OpSys_1 2015-09-12  00:02:12    00:02:12    00:02:12    0
user_3  OpSys_1 2015-09-12  00:02:14    00:02:11    00:02:11    0
user_4  OpSys_1 2015-09-12  00:02:17    00:02:04    00:02:04    0
user_2  OpSys_1 2015-09-13  00:02:12    24:02:17    24:02:17    24
user_2  OpSys_1 2015-09-14  00:02:12    48:03:03    48:03:03    48
user_2  OpSys_1 2015-09-15  00:02:12    72:03:34    72:03:34    72
user_1  OpSys_1 2015-09-18  00:02:18    00:02:18    00:02:18    0
user_3  OpSys_1 2015-09-18  00:02:14    01:15:07    01:15:07    1
user_4  OpSys_1 2015-09-18  00:02:17                00:02:04    0
user_5  OpSys_1 2015-09-18  00:02:20    00:02:20    00:02:20    0
user_1  OpSys_1 2015-09-19  00:02:18    38:17:36    38:17:36    38
user_3  OpSys_1 2015-09-19  00:02:14    18:25:58    18:25:58    18
user_4  OpSys_1 2015-09-19  00:02:17    02:12:04    02:12:04    2
user_5  OpSys_1 2015-09-19  00:02:20    24:02:27    24:02:27    24
user_1  OpSys_1 2015-09-20  00:02:18    62:19:15    62:19:15    62
user_3  OpSys_1 2015-09-20  00:02:14    42:26:03    42:26:03    42
user_4  OpSys_1 2015-09-20  00:02:17    00:36:55    00:36:55    0
user_5  OpSys_1 2015-09-20  00:02:20    48:03:01    48:03:01    48

[szal]

Looking at this data closely the results on day 2015-09-19 should be 154 hours and on 2015-09-20 should be 224 hours.

_time              user_1   user_2  user_3  user_4  user_5  totals
2015-09-12  0           0           0           0           0           0
2015-09-13  0          24           0           0           0           24
2015-09-14  0          48           0           0           0           48
2015-09-15  0          72           0           0           0           72
2015-09-16  0          72           0           0           0           72
2015-09-17  0          72           0           0           0           72
2015-09-18  0          72           1           0           0           73
2015-09-19  38         72           18          2           24          154
2015-09-20  62         72           42          0           48          224

BTW - I've also noticed that user_4 on 2015-09-20 has zero hours - clearly it should be at least 2 hours and points to a bug in the data supplied by that user on that day.

[szal]

Thanks for your fast response!... adding the last line
| streamstats sum(*) as *
to the end of the query does have the desired effect but the totals for each day are now much larger than they should be.
I can see that the first empty bucket now has taken the previous bucket total and doubled it - which is wrong.
In the above example 2015-09-16 72 is now 2015-09-16 144

[somesoni2]

Sorry about that. I guess I would need to understand your data better. The problem is that first half of your data (table 1, before 2015-09-16), everything is cumulative already and everything after that is not (this is based on your expected table 2). Is this correct?? If it's correct can I assume, anything after the null/0 values, I need to accumulate and before that is already accumulated?