Hello everyone,
I have a search as follows which displays the usernames, their accessing application count on that day, and the average of total users average accessing application count
index=foo sourcetype = foo | | stats dc(A) as accessing_application_count by usernames |eventstats avg(accessing_application_count) as avg_accessing_application_count |stats max(accessing_application_count) as max_accessing_application_count max(avg_accessing_application_count) as avg_accessing_application_count by usernames
Which Displays something as follows
usernames max_accessing_application_count avg_accessing_application_count
abc 3 4.982456
def 0 4.982456
ghi 10 4.982456
Now I want to calculate similarly for each user's last 3 days max_accessing_application_count which should be calculated based on each day's max_accessing_application_count of last 3 days, and the average too. like below
usernames max_accessing_application_count last_3days_max_accessing_ avg_accessing_application_count last_7d_avg
abc 3 6 4.982456 7.8
def 0 4 4.982456 7.8
ghi 10 7 4.982456 7.8
average should be calculated as each days average for the last 3 days and that 3 days average of that.
Please suggest if you have any idea to help me regarding this query.
Updated :-
Hi pavanae,
try something like this:
index=foo sourcetype = foo
| stats dc(A) as accessing_application_count by usernames
| eventstats avg(accessing_application_count) as avg_accessing_application_count
| stats max(accessing_application_count) as max_accessing_application_count max(avg_accessing_application_count) as avg_accessing_application_count by usernames
| appendpipe [ search
index=foo sourcetype = foo earliest=-3d@d latest=now
| stats dc(A) as accessing_application_count by usernames
| eventstats avg(accessing_application_count) as avg_accessing_application_count
| stats max(accessing_application_count) as last_3days_max_accessing_count max(avg_accessing_application_count) as last_3days_avg_accessing_application_count by usernames
]
| table username max_accessing_application_count last_3days_max_accessing_count avg_accessing_application_count last_3days_avg_accessing_application_count
Bye.
Giuseppe
Hi pavanae,
try something like this:
index=foo sourcetype = foo
| stats dc(A) as accessing_application_count by usernames
| eventstats avg(accessing_application_count) as avg_accessing_application_count
| stats max(accessing_application_count) as max_accessing_application_count max(avg_accessing_application_count) as avg_accessing_application_count by usernames
| appendpipe [ search
index=foo sourcetype = foo earliest=-3d@d latest=now
| stats dc(A) as accessing_application_count by usernames
| eventstats avg(accessing_application_count) as avg_accessing_application_count
| stats max(accessing_application_count) as last_3days_max_accessing_count max(avg_accessing_application_count) as last_3days_avg_accessing_application_count by usernames
]
| table username max_accessing_application_count last_3days_max_accessing_count avg_accessing_application_count last_3days_avg_accessing_application_count
Bye.
Giuseppe
Thanks for the response @cusello. no results found with the query. also given a pipe before the appendcols, added the command "search" at the beginning of the search still no results or incomplete results.
Hi pavanae,
if you run separately the two searches, have you the expected results?
you should find the two searches that satisfy you need and then join them using appendpipe.
Bye.
Giuseppe
try join type=outer
How about this
earliest=-3d@d latest=now index=foo sourcetype = foo
| eval oneday=if(_time>relative_time(now(),"@d"),A,null())
| eval threeday=if(_time<relative_time(now(),"@d"),A,null())
| stats dc(oneday) as accessing_application_count dc(threeday) as last_3days_accessing_count by usernames
|eventstats avg(accessing_application_count) as avg_accessing_application_count avg(last_3days_accessing_count) as avg_last_3days_accessing_count |stats max(accessing_application_count) as max_accessing_application_count max(avg_accessing_application_count) as avg_accessing_application_count max(last_3days_accessing_count) as max_last_3days_accessing_count max(avg_last_3days_accessing_count) as avg_last_3days_accessing_count by usernames
for some reason I'm seeing all the dayone data as zero. @somesoni2 Updated in the question.
My answer is running on time range earliest=-3d@d latest=now
and field avg_accessing_application_count and max_accessing_application_count is showing data for today (using _time>relative_time(now(),"@d")
in eval ). If you're running with different time range, you might see 0 for dayone data. Check that.
Welcome back!!
Have you seen simple moving averages before?
... | trendline sma3(FieldName)
Above would give you the average of the last 3 days for FieldName on a trendline. But that's probably not what you're looking for.
... | streamstats window=3 avg(FieldName) as Sma3_FieldName | ...
Would probably do the trick for you though. You just need a couple of those and then x days worth of data. Take it all to a stats, table or chart.
Thank you for the response. haven't tried the trendline before But seen no results while I'm trying. I just added the streamstats as suggested after my query like below
earliest=-3d@d latest=@d My query...| streamstats window=3 avg(accessing_application_count) as Sma3_accessing_application_count | table usernames max_accessing_application_count avg_accessing_application_count Sma3_accessing_application_count
Please correct me. @jkat54