Knowledge Management

Summary index queries

deepak02
Path Finder

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,

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type="wood"
| fields count,_time
| timechart per_second(count) as Calls_Per_Second
| eval Calls_Per_Second=max(Calls_Per_Second)
| fields Calls_Per_Second
| stats max(Calls_Per_Second)

(index="sales_data") app="Pottery" sourcetype=app_Pottery_Perf pot_type="clay"
| fields count,_time
| timechart per_second(count) as Calls_Per_Second
| eval Calls_Per_Second=max(Calls_Per_Second)
| fields Calls_Per_Second
| stats max(Calls_Per_Second)

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

THIS QUERY DOESN'T:
(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
| search pot_type=wood
| eval Calls_Per_Second=max(Calls_Per_Second)
| fields Calls_Per_Second
| stats max(Calls_Per_Second)

Please help.

Thanks,
Deepak

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...