How to edit my search to calculate the average count of a field over the last 30 days in summary indexing?

[uhkc777]

Hi,

I saved one report and enabled summary indexing.
This is the saved search:

index=Test  |stats count(ip) as Count earliest=@d 

Now i want to calculate average count of a field over the last 30 days directly in summary indexing (not in Original Test index). Can someone help me how to write the search for that?

Thanks,

[somesoni2]

Try like this

index=your_summary_index_name source="Your Summary index search Name" earliest=-30@d
| stats avg(Count) as Avg_Count

[uhkc777]

Still no luck

[somesoni2]

What results you get when you run just this? (you should be seeing raw events with time and Count=XX)

 index=your_summary_index_name source="Your Summary index search Name" 

[uhkc777]

I'm getting today's count only and raw event like this:

11/15/16
12:00:00.000 AM
11/15/2016 00:00:00 -0500, search_name=sum, search_now=1479242400.000, info_min_time=1479186000.000, info_max_time=1479242400.000, info_search_time=1479242519.376, Count=34, time="11/15/16", type="sum"

[cmerriman]

|savedsearch searchName earliest=-30d@d|stats avg(Count) as avgCount

here's a doc on running saved searches in the search bar:
http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Savedsearch

[uhkc777]

I'm getting the same results as my saved search(today count only) but not over last 30 days.

[uhkc777]

when i give earliest=-30d@d also,I'm getting the count as 29 which is only for today,not the count over last 30 days.

[cmerriman]

try removing earliest from the |savedsearch syntax to run it for all time to see if you get more data back. You might need to change your summary index search to something like

 index=Test  earliest=@d |eval _time=strftime(_time,"%D") |stats count(ip) as Count by _time

OR

 index=Test  earliest=@d |bucket _time span=1d |stats count(ip) as Count by _time

to get the count by day and then do

 |savedsearch searchName earliest=-30d@d|stats avg(Count) as avgCount

if it doesn't have a _time field to query off of, that's probably the problem.

[uhkc777]

Saved search Query(sum):
index=Test|eval time=strftime(_time,"%D")|stats count(ip) as Count by time

configured as
earliest=@d latest=now

next query:
|savedsearch sum earliest=-30d@d |stats avg(Count) as Avg

still getting only today's avg

[cmerriman]

it needs to be _time, try

index=Test  |bucket _time span=1d |stats count(ip) as Count by _time

or

index=Test  |eval _time=strftime(_time,"%D") |stats count(ip) as Count by _time

otherwise Splunk probably isn't recognizing it as the time field

[uhkc777]

I tried it but still not getting

[cmerriman]

after changing the summary index search to include _time, have you tried @somesoni2 answer? That might work also.

index=your_summary_index_name source="Your Summary index search Name" earliest=-30@d| stats avg(Count) as Avg_Count

[uhkc777]

yes tried it but not getting desired one

[uhkc777]

No results found.

When i run the saved search query i'm getting the results but not as you said. No need to mention summary index in our query?

[cmerriman]

The first part is producing results from your saved search that is created from your summary index? What are the results you get?

I edited my query because I hadn't capitalized Countif that was the issue.