Splunk Search

How to calculate and display in a table the average of each user's field values for n number of days?

pavanae
Builder

I have a search which gives the result as follows for one day

Query :- base search | stats dc(dCIF) as dUniqueCIFs by user |eventstats avg(dUniqueCIFs) as avgdUniqueCIFs |stats max(dUniqueCIFs) as max_dUniqueCIFs max(avgdUniqueCIFs) as avgdUniqueCIFs by user

Users     dc(users_activity_count)     average(All_users_activity_count)
A                             2                                2              
B                             3                                2               
C                             1                                2                

Similarly how can I calculate the average of each users_activity count for n number of days like below

Users     dc(users_activity_count)     average(All_users_activity_count)   average(each_users_activity_count_forlast7days)
A                             2                               2                      A's average for last 7 days     
B                             3                               2                      B's .......
C                             1                               2                      C's.........

Where average(each_users_activity_count_forlast7days) should display the average of each user for last 7 days, which is user A's Average,B's,C's....

0 Karma
1 Solution

sundareshr
Legend

Try something like this

base search earliest=-7d@d
| bin span=1d _time
| stats dc(dCIF) as dUniqueCIFs by _time user 
| eval when=if(_time<relative_time(now(), "@d"), "7days", "Today")
| eventstats avg(dUniqueCIFs) as avgdUniqueCIFs by when
| table user dUniqueCIFs 7days Today 
| dedup user

View solution in original post

0 Karma

sundareshr
Legend

Try something like this

base search earliest=-7d@d
| bin span=1d _time
| stats dc(dCIF) as dUniqueCIFs by _time user 
| eval when=if(_time<relative_time(now(), "@d"), "7days", "Today")
| eventstats avg(dUniqueCIFs) as avgdUniqueCIFs by when
| table user dUniqueCIFs 7days Today 
| dedup user
0 Karma

pavanae
Builder

didn't worked. My query is as follows

base search | stats dc(dCIF) as dUniqueCIFs by user |eventstats avg(dUniqueCIFs) as avgdUniqueCIFs |stats max(dUniqueCIFs) as max_dUniqueCIFs max(avgdUniqueCIFs) as avgdUniqueCIFs by user

Based on your suggestion. I'd modified and the result successfull displayed the dUniqueCIFs which I mentioned as dc(users_activity_count) in my question but didn't seen the average and 7days average. @sundareshr

0 Karma

sundareshr
Legend

Try the updated query

0 Karma

pavanae
Builder

still the same no results for 7days and today. @sundareshr

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...