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
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?
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
Hey thanks, for the reply
I edited my question could you see if you can help me now?
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
so i have tried it, the regex work but it didn't save the value in the new fields (Year, Month, Day)
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