Splunk Search

Compare monthly group average against user daily results

mikev
Path Finder

We have dashboards that show the average of user work for the last month this could be for any of the various departments. We also have a box-plot dashboard which will show which users have extreme entries (higher than their normal range) using the information from the box-plot there is a heat map dash that is utilized to show the exact entries, this is exported to manually produce those individuals for further review.

To try and have Splunk produce the report automatically each month we have tried about every combination to make all of the searches into one, probably trying to make it to difficult. Here is a sampling of the data from the heat map.

UserID  2/1/16  2/2/16  2/3/16  2/4/16  2/5/16
Auser   0   10  40  32  55
Buser   56  48  27  94  53
Cuser   3   0   14  3   3
Duser   145 34  246 118 60
Euser   0   0   0   0   0
Fuser   0   3   4   3   0
Guser   51  76  121 154 120
Huser   12  65  19  2   0
Juser   12  7   24  8   15
Kuser   0   97  0   13  1
Luser   0   0   6   0   1
Muser   296 202 308 87  0

We add each day user counts – which in this case is a timestamp so each time they enter something it writes a new timestamp. Then for the month we take all of those counts and divide them by the number of users for them month to get the average for the group. We want to see which of those users were above the monthly average *4 and display the user, the date, and their entries. For example the average for last month was 52 * 4 = 208 so we would want to see any users above 208.

User        Date        Count
Duser       2/3/16  246
Muser       2/1/16  296
             2/3/16 308

A lot of explanation but I think necessary. The search that gets us closest follows:

index="foo" Classification="bar" 
| eval combined_lookup=ClientID."-".PersonID
| dedup UserID combined_lookup                  
| timechart span=24h count(DateOfAccess) as Daily_Count dc(UserID) as User_Count
| eventstats sum(Daily_Count) as daytot sum(User_Count) as usertot  
| eval Daily_Avg=round(daytot/usertot,0) 
| appendcols [search index="foo" Classification="bar"
| eval combined_lookup=ClientID."-".PersonID
| dedup UserID combined_lookup 
| eval Date=strftime(_time, "%D") 
| chart count by UserID Date ] 
| where UserID > Daily_Avg * 4

Problem is the results don’t give me the format above, it provides a day by day total with a 1 user listed as above not multiple users listed.

_time           daily avg   UserId      usertot    (should be total for the day)
2016-02-09          200     Muser       653

I would prefer to not do the appendcols but could not think of another way to re-use the value of UserID. UserID counts are both individual and group counts.

Any guidance is appreciated.

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index="foo" Classification="bar" 
| eval combined_lookup=ClientID."-".PersonID
| dedup UserID combined_lookup 
| bucket span=1d _time  | eval Date=strftime(_time, "%D")
| stats count as Count by UserID, Date
| eventstats sum(Count) as daytot count as usertot by Date
| eval daytot=daytot/usertot
| eventstats sum(daytot) as daytot count as usertot 
| eval Daily_Avg=round(daytot/usertot,0) 
| where Count>(Daily_Avg*4)
| stats list(Date) as Date list(Count) as Count by UserID

View solution in original post

somesoni2
Revered Legend

Give this a try

index="foo" Classification="bar" 
| eval combined_lookup=ClientID."-".PersonID
| dedup UserID combined_lookup 
| bucket span=1d _time  | eval Date=strftime(_time, "%D")
| stats count as Count by UserID, Date
| eventstats sum(Count) as daytot count as usertot by Date
| eval daytot=daytot/usertot
| eventstats sum(daytot) as daytot count as usertot 
| eval Daily_Avg=round(daytot/usertot,0) 
| where Count>(Daily_Avg*4)
| stats list(Date) as Date list(Count) as Count by UserID

View solution in original post

mikev
Path Finder

I'm off-site, I'll let you know on Monday

0 Karma

mikev
Path Finder

Was able to get back to this, and it looks good. For sure was making it more difficult than it needed to be. Appreciate the answer and help!

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!