Splunk Search

Why does the Average Event Count not calculate properly after using the "join" command in my search?

cc3658
Explorer

Here is the search that I am attempting: (Intended outcome: compare login successes from a 24 hour period to an average of login successes over a 30 day period. Complication: logs are stamped with UTC)

index=myindex type=user_auth acct=admin OR acct=temp res=success earliest=-24h
| eval local_date_hour = strftime(_time, "%H") (adjustment for logs stamped with UTC)
| stats count as Recent_Event_count by local_date_hour
|** join** local_date_hour [search type=user_auth acct=admin OR acct=temp res=success earliest=-30d@d
| eval local_date_hour = strftime(_time, "%H") (adjustment for logs stamped with UTC)
| stats count by local_date_hour
| addinfo
| eval days = round((info_max_time-info_min_time) / 86400)
| eval Average_Event_Count = round(count / days, 2)]
| fields local_date_hour, Average_Event_Count, Recent_Event_Count
| sort +local_date_hour

For some reason the "Average_Event_Count" is not calculating properly. For example if my Recent_Event_Count for hour 1 is 39766, the Average_Event_Count is showing 12022.07, which is a fraction of the anticipated results.

If I remove the join and perform each of the searches separately, expected results are returned for each count. This leads me to believe that the join is causing an issue.

I should also note that I am using a very similar search (without adjustments for UTC) for other dashboard panels and it seems to be calculating as expected. Even if I remove the adjustment for UTC, the problem remains. I am hoping there is an uncomplicated answer as I am a new user 😃

Tags (4)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

1) You should select your events on an hour and a day boundary, respectively.

2) watch your capitalization. Recent_event_count is capitalized two different ways in your code.
3) just in case there was anything odd about the results of addinfo, I just summed by date

Try this -

index=myindex type=user_auth acct=admin OR acct=temp res=success 
earliest=-24h latest=@h
| eval local_date_hour = strftime(_time, "%H") 
| stats count as Recent_Event_Count by local_date_hour
| join local_date_hour 

   [ search type=user_auth acct=admin OR acct=temp res=success 
     earliest=-30d@d latest=@d
   | eval local_date_hour = strftime(_time, "%H") 
   | eval local_date = relative_time(_time, "@d") 
   | stats count as HourlyCount by local_date_hour local_date
   | stats dc(local_date) as days, sum(HourlyCount) as HourlyCount by local_date_hour
   | eval Average_Event_Count = round(HourlyCount / days, 2)
   | fields local_date_hour Average_Event_Count
   ]

| fields local_date_hour, Average_Event_Count, Recent_Event_Count 
| sort 0 +local_date_hour

edited to use sort 0 instead of sort in case there were more than 100 values to be sorted.

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

1) You should select your events on an hour and a day boundary, respectively.

2) watch your capitalization. Recent_event_count is capitalized two different ways in your code.
3) just in case there was anything odd about the results of addinfo, I just summed by date

Try this -

index=myindex type=user_auth acct=admin OR acct=temp res=success 
earliest=-24h latest=@h
| eval local_date_hour = strftime(_time, "%H") 
| stats count as Recent_Event_Count by local_date_hour
| join local_date_hour 

   [ search type=user_auth acct=admin OR acct=temp res=success 
     earliest=-30d@d latest=@d
   | eval local_date_hour = strftime(_time, "%H") 
   | eval local_date = relative_time(_time, "@d") 
   | stats count as HourlyCount by local_date_hour local_date
   | stats dc(local_date) as days, sum(HourlyCount) as HourlyCount by local_date_hour
   | eval Average_Event_Count = round(HourlyCount / days, 2)
   | fields local_date_hour Average_Event_Count
   ]

| fields local_date_hour, Average_Event_Count, Recent_Event_Count 
| sort 0 +local_date_hour

edited to use sort 0 instead of sort in case there were more than 100 values to be sorted.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Although it occurs to me that since you have no need of knowing the number of days that went into the calculation, the middle subsearch part can be simplified to

index=myindex type=user_auth acct=admin OR acct=temp res=success 
 earliest=-24h latest=@h
 | eval local_date_hour = strftime(_time, "%H") 
 | stats count as Recent_Event_Count by local_date_hour
 | join local_date_hour 

       [ search type=user_auth acct=admin OR acct=temp res=success 
         earliest=-30d@d latest=@d
       | eval local_date_hour = strftime(_time, "%H") 
       | eval local_date = relative_time(_time, "@d") 
       | stats count as HourlyCount by local_date_hour local_date
       | stats avg(HourlyCount) as Average_Event_Count by local_date_hour
       | eval Average_Event_Count = round(Average_Event_Count, 2)
       ]

 | fields local_date_hour, Average_Event_Count, Recent_Event_Count 
 | sort 0 +local_date_hour

edited to use sort 0 instead of sort in case there were more than 100 values to be sorted.

0 Karma

cc3658
Explorer

DalJeanis,
I have tested your suggested query against my data and it appears to be returning the expected results. Thank you for your help!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

You are quite welcome! It was a fun challenge.

0 Karma

nabeel652
Builder

Not sure why it is not giving you desired results. I have tested your queries and they work fine.

0 Karma
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...