Search buckets span=18h

[timmalos]

Hi
I got a complex situation i'll try to explain best as possible:
I have some jobs events.
I group them by Policy.
I do my search from yesterday 18h to today 12h.
For each policy, i want the first job started, the last one finished and the interval.
This search to it pretty well:

index=infra_nb sourcetype=nbjobs|stats min(_time) as start,max(End) as end by Policy|eval diff=end-start

Here comes the troubles : I want the same search to process ,but this time in the past two months,having one result per "day". I need to group my jobs events by packets of 12AM-6PM=18 hours (which is easy with bins), packets starting each day at 6PM.(And this, i didnt find how to process)
So for each packets of events i do the previous search. I can then draw the evolution of the interval for each policy in respect to Time.

A solution would be to run the previous search each day as scheduled task, to store results into Splunk and then draw statistics from them, but i would avoir this if possible.

Thx for your help,

EDIT: I search a way to group events in "packets of events", 1packet per "day", but a "day" would be from 6PM to 12AM the next day, (i dont care other events from 12AM to 6PM).
In fact i need something like 'bucket spans=18h start=6PM'

[linu1988 · Accepted solution]

Hello,

I think it's very hard with the use of earliest and latest to do it for last 2 months. The best way i could think of is like this:

index=infra_nb sourcetype=nbjobs earliest=-2mon@mon latest=@mon|eval ignore_hour=strftime("_time","%H")|where ignore_hour < 12 AND ignore_hour > 18|eval month=strftime(_time,"%b")|eval day=strftime(_time,"%d")|eval mod_date=if(ignore_hour<18,day,day=day+1)|stats min(_time) as start,max(_time) as End by Policy,day,month|eval diff=End-start

Of-course you could tweak a little if there is any mistake OR any better solution can be suggested. Thanks

[timmalos]

Did some changes, here is the final search : index=infra_nb sourcetype=nbjobs |eval hour=strftime(_time,"%H")|search (hour<12)OR(hour>=18)|eval month=strftime(_time,"%b")|eval day=strftime(_time,"%d")|eval day=if(hour<12,day,day+1)|stats min(_time) as start,max(eval(_time+jobElapsedTime)) as End by Policy,day,month|eval diff=End-start

Thx a lot for your help !

[linu1988]

Did some changes, please give it a try

[timmalos]

Much better. But now it will calculate stats for events from 0h to 12h and 18h to 24h each day for each Policy.

What i need is 18h to 24h AND 0h to 12h the day after. All my troubles come from my time window is across two days.

[linu1988]

I have updated the answer, Could you please give it a try.

[timmalos]

I didnt explain well : I dont want one answer for the past two months, i want one answer per day during the past two months (Actually can be 1 or 3 months or even 1 week), but for me a day is not 0am to 12pm but 6pm to 12am if you see what i mean.(Or i just would have used bucket span=24h). I tried using transaction but cant manage to find a good way.