topic Re: How to modify my search to include historic count to my current day's count and also average? in Splunk Search

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

pavanae — Tue, 29 Sep 2020 11:56:04 GMT

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

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

jkat54 — Sun, 27 Nov 2016 13:24:19 GMT

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.

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

pavanae — Tue, 29 Sep 2020 11:56:09 GMT

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

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

somesoni2 — Mon, 28 Nov 2016 05:05:29 GMT

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

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

gokadroid — Mon, 28 Nov 2016 05:08:33 GMT

Welcome back!!

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

gcusello — Mon, 28 Nov 2016 08:36:47 GMT

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

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

pavanae — Mon, 28 Nov 2016 15:46:28 GMT

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

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

pavanae — Mon, 28 Nov 2016 20:19:58 GMT

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.

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

gcusello — Tue, 29 Nov 2016 09:29:18 GMT

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

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

puneethgowda — Tue, 29 Nov 2016 09:35:06 GMT

try join type=outer

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

somesoni2 — Tue, 29 Sep 2020 11:57:02 GMT

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.