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?

[pavanae]

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

[sundareshr · Accepted solution]

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

[pavanae]

didn't worked @sundareshr. displayed no results.

[sundareshr]

My bad, try now

[pavanae]

Awesome thanks @sundaresh.