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.

0 Karma
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
| 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!

View solution in original post

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

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!