How to modify my search to include historic count to my current day's count and also average?

[pavanae]

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

[gcusello · Accepted solution]

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

[pavanae]

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.

[gcusello]

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

[puneethgowda]

try join type=outer

[somesoni2]

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

[pavanae]

for some reason I'm seeing all the dayone data as zero. @somesoni2 Updated in the question.

[somesoni2]

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.

[gokadroid]

Welcome back!!

[jkat54]

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.

[pavanae]

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