Splunk Search
Highlighted

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

Explorer

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,

0 Karma
Highlighted

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

Super Champion
|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

0 Karma
Highlighted

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

Explorer

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?

0 Karma
Highlighted

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

Super Champion

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.

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Super Champion

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.

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Super Champion

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

0 Karma
Highlighted

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

Explorer

I tried it but still not getting

0 Karma