Splunk Search

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

pdumblet
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

lguinn2
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

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

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...