Splunk Search

## How to edit my search to calculate the average number of tickets per week based on categories?

Explorer

I am trying to determine the average number of tickets per week based on the unique number of categories for the tickets.

I can run a search with a stat count to get the number of tickets by week number:

``````index="tickets" | eval dateyearweek=strftime(_time,"%Y-%W") | eval Day1ofWeek = strftime(relative_time(_time,"@w1"),"%Y/%m/%d") | stats count by Day1ofWeek | sort -date_year | rename Day1ofWeek to "Week Starting"
``````

I can also run a search with stat dc(category) to get the unique number categories by week

``````index="tickets" | eval dateyearweek=strftime(_time,"%Y-%W") | eval Day1ofWeek = strftime(relative_time(_time,"@w1"),"%Y/%m/%d") | stats dc(category) by Day1ofWeek | sort -date_year | rename Day1ofWeek to "Week Starting"
``````

I am trying to determine the average number of tickets by category, but cannot figure out how to do the calculation.

Should I use an append to do a new search inside the first search with an eval to then do the calculation? I am a little stuck on the whole combining multiple search results.

Thanks.

Tags (4)
1 Solution
Legend

First, calculate the number of tickets by category for the week:

``````yoursearchhere
| stats count by category
``````

To get the average number of tickets across all categories

``````yoursearchhere
| stats count as Tickets by category
| appendpipe [ stats count as numCategories sum(Tickets) as TotalTickets
| eval Tickets = round(TotalTicket/numCategories,2)
| fields Tickets | eval category="* Average number of tickets" ]
``````

There are several ways to do this, but I think that this looks best. If you want to do this week-by-week:

``````yoursearchhere
| timechart span=1w count as Tickets by category
| appendpipe [ stats sum(*) as * count as TotalWeeks
| foreach * [eval '<<FIELD>>'=if(isnum('<<FIELD>>'),round('<<FIELD>>'/TotalWeeks,2),"") ]
| fields - TotalWeeks | eval category="* Average number of tickets" ]
``````

HTH!

Legend

First, calculate the number of tickets by category for the week:

``````yoursearchhere
| stats count by category
``````

To get the average number of tickets across all categories

``````yoursearchhere
| stats count as Tickets by category
| appendpipe [ stats count as numCategories sum(Tickets) as TotalTickets
| eval Tickets = round(TotalTicket/numCategories,2)
| fields Tickets | eval category="* Average number of tickets" ]
``````

There are several ways to do this, but I think that this looks best. If you want to do this week-by-week:

``````yoursearchhere
| timechart span=1w count as Tickets by category 