topic Avg for 7 days and 15 days ....etc :) in Splunk Search

Avg for 7 days and 15 days ....etc :)

harishalipaka — Tue, 29 Sep 2020 20:21:33 GMT

Hi Splunkers ,

Here below is my data look like that .In that i want to get avg(sum_PBD) based on Date
Conditions:-
1. If results are <= 7 avg(sum_PBD) by eachday -----------------here i got 7 results
2.If results are <= 1 month avg(sum_PBD) by weekwise-----------------here i got 4 results
3.If results are <= 2 months avg(sum_PBD) by 15days -----------------here i got 4 results
4.If results are >= 6 months avg(sum_PBD) by monthwise-----------------here i got monthwise results

Thanks in Advance.

Re: Avg for 7 days and 15 days ....etc :)

poete — Tue, 29 Sep 2020 20:21:42 GMT

Hello @harishalipaka,

I think the following goes in your way. It takes advantage of the earliest, latest and span arguments. In the following, I am just counting the *ERROR*s in the _internal index.

index=_internal ERROR earliest=-7d@d latest=now()
| timechart count span=1d
| append 
    [search index=_internal ERROR earliest=-30d@d latest=-8d@d
| timechart count span=7d]
| append 
    [search index=_internal ERROR earliest=-60d@d latest=-31d@d
| timechart count span=15d]

Re: Avg for 7 days and 15 days ....etc :)

harishalipaka — Mon, 09 Jul 2018 12:51:49 GMT

hi @poete
Thanks for your reply .But i cann't get answer.
my data is from inputlookup not from index.

Re: Avg for 7 days and 15 days ....etc :)

poete — Mon, 09 Jul 2018 12:53:49 GMT

Hi @harishalipaka,

Sorry, I am not sure I understand. Why can't you replace index=_internal with |inputlookup?

Re: Avg for 7 days and 15 days ....etc :)

harishalipaka — Mon, 09 Jul 2018 13:25:01 GMT

this is my output results ,not direct from lookup.
I already filtered with date range .i had only Date column which one display in image.
I want to filter with that Date column only

Re: Avg for 7 days and 15 days ....etc :)

poete — Mon, 09 Jul 2018 14:57:01 GMT

OK @harishalipaka,, I think I got it.

So, you could do the following:

<your base search of lookup>| eval _time=strptime(Date,"%d/%m/%Y")|
search earliest=-7d@d latest=now()
 | timechart avg(sum_PBD) span=1d
 | append [<your base search of lookup>| eval _time=strptime(Date,"%d/%m/%Y")|
search earliest=-30d@d latest=-8d@d
 | timechart avg(sum_PBD) span=1w]
....

Re: Avg for 7 days and 15 days ....etc :)

woodcock — Fri, 13 Jul 2018 13:20:51 GMT

I think you need something like this:

Your Base Search Here
| bucket _time [| noop | stats count AS span
                | addinfo | eval timepickerSpanSeconds=(info_max_time - info_min_time)
                | eval span=case(
                      timepickerSpanSeconds>=(6*30*24*60*60), "1m",
                      timepickerSpanSeconds>=(2*30*24*60*60), "15d",
                      timepickerSpanSeconds>=(1*30*24*60*60), "1w",
                      true(), "1d")
               | table span | format "" "" "" "" "" ""]
| stats avg(sum_PBD) BY _time

Re: Avg for 7 days and 15 days ....etc :)

woodcock — Fri, 13 Jul 2018 13:26:50 GMT

If your data is from a lookup, try this:

| inputlookup LookupWith_Date_and_sumPDB
| eval _time = strptime(Date, "%m/%d/%Y")
| bucket _time [| inputlookup LookupWith_Date_and_sumPDB
                | eval _time = strptime(Date, "%m/%d/%Y")
                | stats range(_time) AS DateSpanSeconds
                | eval span=case(
                       DateSpanSeconds>=(6*30*24*60*60), "1m",
                       DateSpanSeconds>=(2*30*24*60*60), "15d",
                       DateSpanSeconds>=(1*30*24*60*60), "1w",
                       true(), "1d")
          | table span | format "" "" "" "" "" ""]
| stats avg(sum_PBD) BY _time