Counting an event and then averaging it over a month

[scornell2]

Hello I'm very new to Splunk and have so far been consuming data as .csv files in order to test things out.

I have two main fields; USER_NAME and USER_NBR which are tied together to use (I have a SUBMIT_DATE one as well formatted in %d-%b-%y form). A User can submit multiple jobs in a day and the architects want a Daily Total by User Name and ID and a Monthly Average by User Name and ID in order to have a comparison to determine if they users are deviating over their norm.

The Daily Count was easy (index="03122018" | stats count as "User Submits" by USER_NBR, USER_NAME) however I cannot figure how to create a query that will count the events and average them over the month.

I want to stay away from a subsearch as I think that will add to much overhead to the search and I'm confident that the eval command is my answer but after bashing my head unsuccessfully against SPL for the last day I'm not grasping the answer.

Anything anyone can do to help would be greatly appreciated, thanks for reading.

[elliotproebstel · Accepted solution]

Here's the general approach I'd take:
Gather all events from the last 30 days in the base search

index="03122018" earliest=-30d@d latest=now

Using stats, count the number of events today and also count the number of events over the last 30 days by USER_NBR, USER_NAME

| stats count AS Monthly_Submits, count(eval(SUBMIT_DATE>=strftime(relative_time(now(), "@d"), "%d-%b-%y"))) AS Today_Submits BY USER_NBR, USER_NAME

Note that if you'd like to change "Today" to "Yesterday", for example, you'd change the "@d" value inside the relative_time function to "-1d@d". You can use any of the relative time modifiers here.

Calculate monthly average by dividing Monthly_Submits by 30

| eval Average_Submits=Monthly_Submits/30

[scornell2]

Thank you very much this was exactly what I was looking for. Now I will dive into how this works.

[elliotproebstel]

You're welcome. Now that I'm looking back, I see I forgot the BY clause in the stats call. I'm going to update the post to fix that.