topic Re: How do you compare a previous average with the current average in Splunk Search

How do you compare a previous average with the current average

henderz — Thu, 30 Apr 2020 08:28:16 GMT

Hello
I am trying to compare my average events in current month to previous 3 month average (per day [1,2,3...31]) based on _time

For example:
Considering that the current month is October (10). I am trying to compare the current count of random numbers that I have received on the 10/1 and 10/2 to the average of the counts that I have received on the 1st and 2nd of September(09) and August(08).

That's how i tried to do it:

`soc_events`

| eval mytime=strftime(_time, "%Y/%m/%d") | table mytime

| rex field=mytime "("?<Year>\d+)/(?<Month\d+)/(?<Day>\d+)")"

| stats count as Count by Year,Month,Day | sort Year,Month,Day

| eventstats last(Month) as Current_Month last(Year) as Current_Year | where Month!=CurrentMonth OR Year!=Current_Year

| stats avg(Count) as DayAveravge values(Month) as Months by Day

but it says syntax error in rex : missing terminator

Re: How do you compare a previous average with the current average

MuS — Thu, 30 Apr 2020 08:43:40 GMT

Hi henderz,

please have a read about the timewrap command https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timewrap#Examples

This SPL command provides options to achieve your use case.

Hope this helps ...

cheers, MuS

Re: How do you compare a previous average with the current average

henderz — Thu, 30 Apr 2020 11:29:02 GMT

Hey thanks, for the reply
I edited my question could you see if you can help me now?

Re: How do you compare a previous average with the current average

MuS — Thu, 30 Apr 2020 21:48:28 GMT

Hi henderz,

it says your regex is not correct and the reason are the " inside the regex:

 | rex field=mytime "("?<Year>\d+)/(?<Month\d+)/(?<Day>\d+)")"

use this instead:

 | rex field=mytime "(\"?<Year>\d+)/(?<Month\d+)/(?<Day>\d+)\")"

cheers, MuS

Re: How do you compare a previous average with the current average

henderz — Fri, 01 May 2020 13:10:08 GMT

so i have tried it, the regex work but it didn't save the value in the new fields (Year, Month, Day)

Re: How do you compare a previous average with the current average

MuS — Fri, 01 May 2020 22:04:57 GMT

Okay, give this a try:

| makeresults count=1000 
| eval _time=now() - random() 
| eval mytime=strftime(_time, "%Y/%m/%d") 
| table mytime 
| rex field=mytime "(?<Year>\d+)/(?<Month>\d+)/(?<Day>\d+)" 
| stats count as Count by Year,Month,Day 
| sort Year,Month,Day 
| eventstats last(Month) as Current_Month last(Year) as Current_Year 
| where Month!=CurrentMonth OR Year!=Current_Year 
| stats avg(Count) as DayAveravge values(Month) as Months by Day

The regex did not really work, but I have no idea if the current result is what you expect ¯\_(ツ)_/¯

cheers, MuS

Re: How do you compare a previous average with the current average

to4kawa — Fri, 01 May 2020 22:19:47 GMT

sample:

| gentimes start=08/01/20 end=11/01/20
| eval _time=starttime, Month=strftime(_time,"%m"), Days=strftime(_time,"%d")
| chart count by Days Month

recommend:

`soc_events`
| eval Month=strftime(_time, "%m"), Day=strftime(_time,"%d")
| chart count as Count by Month,Day

result:

Day,08,09,10
----------
1,XX,YY,ZZ
2,XX,YY,ZZ
3,....

sorry, what's average?