Splunk Search

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

harishalipaka
Motivator

Hi Splunkers ,

my_data_image

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.

Thanks
Harish
Tags (1)
0 Karma

woodcock
Esteemed Legend

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

woodcock
Esteemed Legend

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

poete
Builder

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

harishalipaka
Motivator

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

Thanks
Harish
0 Karma

poete
Builder

Hi @harishalipaka,

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

0 Karma

harishalipaka
Motivator

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

Thanks
Harish
0 Karma

poete
Builder

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]
....
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...