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
... View more