Splunk Search

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

Builder

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

alt text

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

Builder

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.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Communicator

try join type=outer

0 Karma

Revered Legend

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

Builder

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

0 Karma

Revered Legend

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.

0 Karma

Motivator

Welcome back!!

SplunkTrust
SplunkTrust

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.

0 Karma

Builder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!