Splunk Search
Highlighted

## How to construct a search to display each user's average of a certain max of distinct count of a field values for last 7 days?

Builder

I am trying to construct a search from almost days to display each user's average of a certain max of distinct count of a field values for last 7 days. like as below

user max(dc(A)) avg(max(dc((A)))
user1 3 4
user2 6 3
user3 5 6

where avg(max(dc((A))) is last 7 days avg(max(dc(A))) for each user

I'd reached somewhat in constructing the query as below for one day

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

which displayed as below

user max(dc(A)) avg(max(dc((A)))
user1 3 4
user2 6 4
user3 5 4

Where I was getting the average of all the users max(dc(A)) instead of each user's max(dc(A)) for last 7 days

I'd also posted simillar question in which i haven't explained the question well so posting it again with detail explanation. Thank you

Tags (5)
1 Solution
Highlighted

## Re: How to construct a search to display each user's average of a certain max of distinct count of a field values for last 7 days?

Legend

You appear to be missing a `by` clause in your `eventstats` See if this gives you your desired results

``````base search | bin span=1h _time | stats dc(ABC) as dUniqueCIFs by _time user| eventstats avg(dUniqueCIFs) as avgdUniqueCIFs by user |stats max(dUniqueCIFs) as max_dUniqueCIFs max(avgdUniqueCIFs) as avgdUniqueCIFs by user
``````
Highlighted

Builder

Highlighted

Legend