Splunk Search

How to copy figures across empty time buckets?

szal
Explorer

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

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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 *

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

szal
Explorer

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
0 Karma

szal
Explorer

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.

0 Karma

szal
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...