How to search the top time, count and average by field without using a subsearch or summary indexing?

[msarro]

Hello everyone.
We have been tasked with creating a report that examines the call use patterns of 3 customers.

Each time a customer makes a call, it generates a record.

For each customer, we want to know the highest number of calls per second, when that occurred, and the average number of calls per second.

So far we have tried 2 approaches:

index=myindex (host=host1 OR host=host2) (Customer1 OR Customer2 OR Customer3)
|timechart span=1s count by Name_of_Customer|eventstats avg(C*) AS C*_Avg max(C*) AS C*_Max

Customer1-Customer3 are just strings in events that indicate the name of the customer, all are found in the Name_of_Customer field. This gave us a great chart with accurate data, but we were lost about how to only keep the 3 rows that showed the highest count times for each customer.

The second approach worked a little different:

index=myindex (host=host1 OR host=host2) (Customer1 OR Customer2 OR Customer3) | bucket span=1s _time | stats count by Name_of_Customer _time |eventstats avg(count) AS Avg max(count) AS Max by Name_of_Customer | sort 0 - Name_of_Customer count | dedup Name_of_Customer

This gives us almost exactly what we want, but the problem is because bucket doesn't include 1s buckets for times when no calls occurred, our average fields are pretty off what their actual values should be.

I have tried to find a way to add additional time buckets for times when there is no event, and came across http://answers.splunk.com/answers/149425/how-to-produce-empty-time-buckets.html but the method doesn't seem to work with bucket.

The goal is to do this without using a subsearch or summary indexing. It's a report that will be run once a day and emailed out to several people.

Is there a middle ground between the two approaches I'm missing? Something I haven't thought of?

[somesoni2 · Accepted solution]

Try this

index=myindex (host=host1 OR host=host2) (Customer1 OR Customer2 OR Customer3)
|timechart span=1s count by Name_of_Customer 
| untable _time Customer count
| eventstats avg(count) as Avg max(count) as Max by Customer 
| where count=Max | fields - count

This should give you fields

_time : time when max has occurred
Customer : name of the Customer
Avg : Avg/sec count for the Customer
Max: Max/sec count for the Customer

[msarro]

This works perfectly, thank you! I hadn't seen the untable command before, but it does exactly what we needed.