dynamic earliest time SPLUNK Query

[pradeep96674]

We have to implement following scenerio in splunk.

We are indexing a log "extractA" with _time as settlement day which can be 20 days ahead.

We are running a query to check the events on settlement Date with earliest as @d (all the event with current day as settlement day)

There are following two condition which we have to ensure :-

1. If settlement date is of Monday say 30 Jan 2017 then it is posted on Saturday 28 Jan 2017, but on Saturday we will not be getting "extractA" events from our query (index=abc sourcetype=extractA earliest=@d) as earliest time is @d and time on event will be 30 Jan 2017.

Posting happens only from Tuesday to saturday morning.

1. We are maintining a holiday calendar in holiday.csv file, on holidays for e.g if 30 Jan 2017 is a holiday in holiday.csv then earliest time should shift to 31st Jan 2017 and pick all the event with timestamp as 31Jan 2017.

Thanks
Pradeep

[somesoni2]

Give this a try (gentimes is just to generate a sample row. Assuming field name in holiday.csv is 'holiday')

index=pmtsdd sourcetype=extractA 
[| gentimes start=-1 | eval today=strftime(now(),"%Y-%m-%d") | eval wday=strftime(now(),"%a") 
| eval earliest=if(wday="Sat",relative_time(now(),"+2d@d"),relative_time(now(),"@d") | eval earliest_h=strftime(earliest,"%Y-%m-%d") | lookup holiday.csv holiday as earliest_h OUTPUT holiday as isHoliday 
| eval earliest=if(isnotnull(isHoliday),relative_time(earliest,"+1d@d"),earliest) | table earliest]
| ...rest of the search...

For testing the logic, you can run just this (actual implementation should be like above though). Update the | eval now= for different variations.

| gentimes start=-1 | eval now=strptime("2017-01-26","%Y-%m-%d") 
| eval today=strftime(now,"%Y-%m-%d") | eval wday=strftime(now,"%a") 
| eval earliest=if(wday="Sat",relative_time(now,"+2d@d"),relative_time(now,"@d") 
| eval earliest_h=strftime(earliest,"%Y-%m-%d") 
| lookup holiday.csv holiday as earliest_h OUTPUT holiday as isHoliday 
| eval earliest=if(isnotnull(isHoliday),relative_time(earliest,"+1d@d"),earliest) | table earliest

[pradeep96674]

Thanks Somesoni. I will try your solution.

[pradeep96674]

Thanks @Somesoni for above solution.

I am still stuck in a scenario where there are 2 or more consecutive day holiday in holiday.csv.

The above solution work fine for 1 day holiday.
But if below if the condition then it fails again:-

1.On Saturday 4th Feb it should pick earliest time of 6th Feb
2. Take 6th and 7th Feb both as holiday in holiday.csv
3. Above query will check if 6th Feb is a Holiday in holiday.csv and if true it will pick earliest time as
7th Feb even though 7th is also a holiday in Holiday.csv.

[somesoni2]

What is the format of date in holiday.csv? For sun and mon, last sat should be the earliest (if not holiday) else it should be same day??

[pradeep96674]

Date format is :- Y-m-d

we are using below query to retrieve event :-

index=pmtsdd sourcetype=extractA earliest=@d

Events in extractA are like :-
"2017-01-30|Field1|Field2|Field3|Field4"

Events in "extractA" are indexed on settlement date (_time is the time mentioned on the field date in event)mentioned in first column.

In above scenario where 30 Jan is Monday but for Monday the query will run on Saturday morning and should pick events with Monday date.

above thing can be achieved by changing the earliesttime to 2 days ahead in case of Saturday.

Problem comes when we have to include a holiday.csv(this contains target holidays).
So if 30 Jan is a target holiday, then on Sat 28th Jan the events should consider event from 31st Jan 2017.(earliesttime should move ahead by 3 days ahead)

[somesoni2]

What is the difference between posted and retrieved date? If your logs for Mon Jan 30 is generated on Sat Jan 28, when will you try to retrieve it? Is it like, the settlement_date is only weekdays, on Tue, you get data for Tue, Wed for Web and for Mon we get on Sat?

[pradeep96674]

Logs are generated like :-
today a log file generates extract_26_jan.log with events :-
2017-01-30|filed1|field2|field3|field4
2017-01-30|filed1|field2|field3|field4
2017-01-31|filed1|field2|field3|field4
2017-01-31|filed1|field2|field3|field4

We will index these file with "_time" as values in first column.
So when we try to access logs with query :-
"index=pmtsdd sourcetype=extractA earliest=@d "
on Saturday i.e 28 th Jan 2017 nothing will come (For every Monday we have to run our query on Saturday).

And if we holiday on Monday then the next working day i.e Tuesday 31st should be earliest day.

Please let me know if any other information is required.

Thanks for replying .

[pradeep96674]

Date format is :- Y-m-d

Our query will be like :-
index=pmtsdd sourcetype=extractA earliest=@d

events in extractA is like below, with first field as settlementDate :-
"2017-01-30|abc123|abc123|abc123|abc123|abc123|abc123|abc123|abc123"

These event are indexed on settlement date so with above query it will be visible on 30th Jan, but as it is Monday so in this scenario it should get visible on saturday (changing the earliest time by 2 days ahead should solve this)
Problem arises when we include a holiday csv which stores dates (format- > Y-m-d) and if for e.g Monday 30 is also holiday then our earliest time should move forward to take event from 31st Jan 2017.

Please let me know if any other information is needed.

Thanks for replying.