Splunk Search

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

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

Tags (5)
1 Solution
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
``````
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
``````
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

Legend

Try the updated query

Builder

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

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