topic How to group 3 months data, making it average data over 2 years? in Splunk Search

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

hmohta — Tue, 11 Oct 2022 13:17:03 GMT

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

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

gcusello — Mon, 10 Oct 2022 06:21:31 GMT

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

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

hmohta — Mon, 10 Oct 2022 06:26:56 GMT

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

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

gcusello — Mon, 10 Oct 2022 06:36:22 GMT

Hi @hmohta,

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

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

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

hmohta — Mon, 10 Oct 2022 06:44:43 GMT

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

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

gcusello — Mon, 10 Oct 2022 06:47:33 GMT

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

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

hmohta — Mon, 10 Oct 2022 06:49:32 GMT

Like I said yields, 0 statistics

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

gcusello — Mon, 10 Oct 2022 06:59:39 GMT

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

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

hmohta — Mon, 10 Oct 2022 09:17:56 GMT

I get no values, no statistics.

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

gcusello — Mon, 10 Oct 2022 09:35:02 GMT

Hi @hmohta,

sorry, please try this:

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

Ciao.

Giuseppe

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

hmohta — Tue, 11 Oct 2022 05:58:05 GMT

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

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

gcusello — Tue, 11 Oct 2022 07:06:53 GMT

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

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

hmohta — Tue, 11 Oct 2022 23:35:07 GMT

@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

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

gcusello — Wed, 12 Oct 2022 06:38:22 GMT

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 😉

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

hmohta — Thu, 13 Oct 2022 03:19:26 GMT

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.