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

[pdumblet]

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.

[lguinn2 · Accepted solution]

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
| addtotals
| 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!