Splunk Search

I need to get hourly reports starting at five minutes before the hour and get the previous week and the week previous to that for the same hour.

johnegracej
New Member

So I need a start/chart/timechart etc... that shows a distinct count of separate login ids from 7:55 - 8:54:59 then 8:55 - 9:54.59 and the previous week for that same hour, and the week before that for the same hour.

I seemed to be having good luck with timechart & chart as long as I start on the hour but when I try to adjust by five minutes everything goes crazy (probably my ignorance which I'm hoping I can overcome here). I would prefer to learn but I could also reverse engineer a good answer.

The closest I've come to a correct answer is:

index=logs* "EventStreamData.eventName"=RetrieveCustomerAccounts EventStreamData.args.request.apiKey=MAIN "EventStreamData.response.entries{}.product.productName"="Checking"
| eval week1=relative_time(now(),"-1w@w")
| eval Hour = strftime(_time,"%H:%M")
| timechart span=60m dc(EventStreamData.args.request.userId) as 360_Volume

But the output is on the hour not shifted by five minutes and something strange happens with the first block of data.

2020-04-10 05:00 709
2020-04-10 06:00 10502
2020-04-10 07:00 16122
2020-04-10 08:00 20273

Thanks in advance.

0 Karma

to4kawa
Ultra Champion

sample:

| makeresults count=2 
| streamstats count 
| eval _time = relative_time(_time,-1*count."d@d") 
| eval _time = if(count=1,_time - 1,_time)
| makecontinuous span=1min _time 
| append [ | makeresults count=2 
| streamstats count 
| eval _time = relative_time(_time,-1*count."d@d-1w") 
| eval _time = if(count=1,_time - 1, _time)
| makecontinuous span=1min _time ]
| eval count=random() % 10 
| fieldformat _time = strftime(_time, "%F %T") 
| eval minute=strftime(_time,"%M") ,week=strftime(_time,"%d")
| eval times=if(minute>54,"after","before") 
| streamstats dc(times) as flag window=2 
| streamstats count(eval(flag=2 AND times="after")) as session 
| stats min(_time) as _time sum(count) as total by session week
| sort _time 
| fields - session
| eventstats max(_time) as date
| eval week=if(match(strftime(date,"%d"),week),"today","weekago")
| xyseries _time week total
| eval times=strftime(_time,"%T")
| selfjoin times
| fields - times

This query aims to aggregate total between X:55:00 and Y:54:59.

recommend:

index=logs* "EventStreamData.eventName"=RetrieveCustomerAccounts EventStreamData.args.request.apiKey=MAIN "EventStreamData.response.entries{}.product.productName"="Checking" (earliest=-1d OR (earliest=-1w latest=-1w+1d))
| reverse
| eval minute=strftime(_time,"%M") ,week=strftime(_time,"%d")
| eval times=if(minute>54,"after","before") 
| streamstats dc(times) as flag window=2 
| streamstats count(eval(flag=2 AND times="after")) as session 
| stats min(_time) as _time dc(EventStreamData.args.request.userId) as total by session week
| sort _time 
| fields - session
| eventstats max(_time) as date
| eval week=if(match(strftime(date,"%d"),week),"360_Volume","360_Volume_weekago")
| xyseries _time week total
| eval times=strftime(_time,"%T")
| selfjoin times
| fields - times

please modify earliest and latest appropriately.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!