Splunk Search

How do I calculate the average over time when the count keeps changes every month?

k_harini
Communicator

I have to calculate average tickets over time for developer.

Since time is not _time and different time column, I did not use time chart

basesearch|eval Due_Time = strptime('Due By',"%d.%m.%Y") |bin Due_Time span=1mon |chart count over Due_Time by "Developer" usenull="f" useother="f"|eval Due_Time = strftime(Due_Time,"%b.%Y")|addtotals

Till here it's fine. But i want to calculate average over time. How do i capture count so that i can do total/count and arrive at average? The count keeps changing every month, I should not consider if the count is 0. eventstats avg(total) is not working here either.

can someone please help?

0 Karma
1 Solution

sundareshr
Legend

Try this

basesearch
|eval Due_Time = strptime('Due By',"%d.%m.%Y") 
| bin Due_Time span=1mon 
| chart count over Due_Time by "Developer" usenull="f" limit=0
| untable Due_Time Developer Counts
| eventstats avg(Counts) as Average by Due_Time
| eval Due_Time=Due_Time."#".Average
| chart values(Counts) as counts over Due_Time by Developer
| rex field="Due_Time" "(?<Due_Time>[^#]+)#(?<Average>.*)" 
| eval Due_Time = strftime(Due_Time,"%b.%Y")

View solution in original post

0 Karma

woodcock
Esteemed Legend

Just override _time, like this:

basesearch
| eval _time = strptime('Due By',"%d.%m.%Y")
| timechart span=1mon count by "Developer" usenull="f" useother="f"

k_harini
Communicator

Thanks a lot. I dint know this can be overridden.. This will help me with my other cases as well.

0 Karma

sundareshr
Legend

Try this

basesearch
|eval Due_Time = strptime('Due By',"%d.%m.%Y") 
| bin Due_Time span=1mon 
| chart count over Due_Time by "Developer" usenull="f" limit=0
| untable Due_Time Developer Counts
| eventstats avg(Counts) as Average by Due_Time
| eval Due_Time=Due_Time."#".Average
| chart values(Counts) as counts over Due_Time by Developer
| rex field="Due_Time" "(?<Due_Time>[^#]+)#(?<Average>.*)" 
| eval Due_Time = strftime(Due_Time,"%b.%Y")
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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