topic Re: Summary index queries in Knowledge Management

Summary index queries

deepak02 — Tue, 29 Sep 2020 14:56:09 GMT

Hi,

I am using Splunk for a web application that sells pots.

WHAT I HAVE: Query for maximum no of calls for each pot type,

WHAT I NEED: Use a summary index for the above queries.
I have created the below query for summary index,

THIS QUERY WORKS:
(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf
| eval count=1
| timechart per_second(count) as Calls_Per_Second by pot_type
| table *
| fields - _time

Please help.

Thanks,
Deepak

Re: Summary index queries

richgalloway — Tue, 29 Sep 2020 14:56:12 GMT

You seem to be confused about summary indexes. To use a summary index, a scheduled search will write summary results to an index ("sales_summary") using the collect command. A separate search then reads from that summary index using index=sales_summary.

What exactly are trying to accomplish? Perhaps we can suggest another way to get there.

Re: Summary index queries

DalJeanis — Wed, 19 Jul 2017 00:13:32 GMT

Your problem has nothing to do with summary indexes, just understanding how to use stats. Any of the following could have used timechart for part of the solution -- timechart can implicitly do the bin and the per_second calculation for you, but it also has a couple of finnicky things about it, and I figure you need your answer more than you need to practice timechart.

Use this if you want to know the max actual calls in a real second, assuming there is an actual field called count on the record.

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type=*
| fields count, _time, pot_type
| rename count as Count
| bin _time span=1s
| stats sum(Count) as Calls_Per_Second by pot_type _time
| stats max(Calls_Per_Second) as MaxCalls by pot_type

Use this if you want to know the max actual calls in a real second, assuming there is NOT an actual field called count on the record.

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type=*
| fields _time, pot_type
| bin _time span=1s
| stats count as Calls_Per_Second by pot_type _time
| stats max(Calls_Per_Second) as MaxCalls by pot_type

Use this if you want to know the Peak number of calls in any 5 minute period, and want that peak expressed in terms of calls per second, assuming there is an actual field called count on the record.

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type=*
| fields count, _time, pot_type
| rename count as Count
| bin _time span=5m
| stats sum(Count) as Calls_Per_Second by pot_type _time
| eval Calls_Per_Second = round(Calls_Per_Second/300,0)
| stats max(Calls_Per_Second) as MaxCalls by pot_type

Use this if you want to know the Peak number of calls in any 5 minute period, and want that peak expressed in terms of calls per second, assuming there is NOT an actual field called count on the record.

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type=*
| fields _time, pot_type
| bin _time span=5m
| stats count as Calls_Per_Second by pot_type _time
| eval Calls_Per_Second = round(Calls_Per_Second/300,0)
| stats max(Calls_Per_Second) as MaxCalls by pot_type

Just for comparison, a timechart version...

Use this if you want to know the max actual calls in a real second, assuming there is an actual field called count on the record.

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type=*
| table count, _time, pot_type
| rename count as Count
| timechart span=1s per_second(Count) as Calls_Per_Second by pot_type
| stats max(Calls_Per_Second) as MaxCalls by pot_type

If there is no such field as count , change the rename command to

| eval Count = 1

If you want the average per_second value across 5 minutes, change the timechart span value to

| timechart span=5m per_second(Count) as Calls_Per_Second by pot_type

Oh, by the way, every one of those versions gets rid of the _time, so you don't know when that max value actually happened. If you want to retain the _time, change the last line to these three ...

| eventstats max(Calls_Per_Second) as MaxCalls by pot_type 
| where Calls_Per_Second = MaxCalls 
| sort 0 pot_type _time