How to group 3 months data, making it average data over 2 years?

[hmohta]

Hello

The dates I have are in form of Week Starting: for example WeekStarting = 04/04/2022 , 11/04/2022 and so on. I am unable to group data where business now requires to see 3 months rolling avg figures for the last 2 years.

How can I achieve this? 

My search:
index=AB, source=AB
| search (WeekStarting="2021*" OR WeekStarting="2022*")
| chart avg(DeviceCount) by WeekStarting

It should be visualized as a 3 month data analysis

I am also trying to use timewrap  span =1 month by Device count but no statistics appear!! Please help

[hmohta]

Thank you @gcusello . you mention : 

| eval Year=substr(WeekStarting,6,4)

What does 6,4 stand for? Sorry I am new to Splunk and dont understand. Does it mean the Week like Week 6 or Week 4. Do I have to break it over 56 Weeks!? I am confused

[gcusello]

Hi @hmohta,

as you can read ad https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/TextFunctions#substr.28X.2CY.2CZ....

6 is the starting point of the substring and 4 is the lenght of the substring.

In this way you can extarct the year from the StartingWeek field values to use to group results.

Ciao.

Giuseppe

[hmohta]

Sorry this search is not yielding any results for me. Not sure I understand.

[gcusello]

Hi @hmohta,

please run this search and tell me the results:

index=AB, source=AB
| eval Year=substr(WeekStarting,6,4)
| search Year IN ("2021","2022")
| table Year WeekStarting DeviceCount

Ciao.

Giuseppe

[hmohta]

Like I said yields, 0 statistics

[gcusello]

Hi @hmohta,

what is the result of my last test search?

have you values i the listed fields or not?

I need this information to understand if the error is in the field format or in the field extraction.

Ciao.

Giuseppe

[hmohta]

I get no values, no statistics.

[gcusello]

Hi @hmohta,

sorry, please try this:

index=AB, source=AB
| eval Year=substr(WeekStarting,6,4)
| table Year WeekStarting DeviceCount

Ciao.

Giuseppe

[hmohta]

Unfortunately it yields no results at all. 0 values/statistics

[gcusello]

Hi @hmohta,

please try this, without comma:

index=AB source=AB
| eval Year=substr(WeekStarting,6,4)
| table Year WeekStarting DeviceCount

if also this search haven't nay result, you should run only the main search and check if the two fields to display are present: WeekStarting, DeviceCount.

Ciao.

Giuseppe

[hmohta · Accepted solution]

@gcusello  thankyou  so much for trying, still no results and yes I did do the main search and these events do occur. I think I will move on from this question and look how I can produce moving average just like we do in excel. thanks for your help

[gcusello]

Hi @hmohta,

I don't like that you leave Splunk for Excel, I often use Splunk to elaborate data instead Excel!

Anyway, after the main search

index=AB sourcetype=AB

have you the fields WeekStarting, DeviceCount or not?

if not it isn't correct the logic you'r using and you have to find another one, maybe using different fields.

Then check the fieldnames, are they correct? remember that fieldnames are case sensitive.

could you share a scrennshot of the main search with the intersting fields?

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉

[hmohta]

I wasn't leaving Splunk for Excel, all I said was I will close this query as I have not achieved what I am after and then ask another question which is what " is used in Excel", but use it in Splunk.

I am aware of the fields being case sensitive, unfortunately nothing works!!

Thankyou for trying.

[gcusello]

Hi @hmohta,

you have to extract the year from the WeekStarting field,

you have meny methods to do this: eval substr, rex, eval strftime. 

using the first, something like this:

index=AB, source=AB
| eval Year=substr(WeekStarting,6,4)
| search Year IN ("2021","2022")
| stats avg(DeviceCount) BY Year

Obivously, if you have dates already grouped by week, there's the error of the first and the last week of the year.

Ciao.

Giuseppe