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

pdumblet — Wed, 11 Jan 2017 23:52:39 GMT

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.

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

lguinn2 — Thu, 12 Jan 2017 00:23:46 GMT

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!

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

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

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