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

[cc3658]

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 😃

[DalJeanis · Accepted solution]

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.

[DalJeanis]

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.

[cc3658]

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

[DalJeanis]

You are quite welcome! It was a fun challenge.

[nabeel652]

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