How can I get my inputlookup to exclude holidays?

[tkwaller_2]

Hello I have a search that I use to calculate days between 2 dates.

The search is like this:

    |index=dev_tsv "BO Type"="assessments"  | rename "BO ID" as id| convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Date Started") AS starttime  mktime("Step Date Completed") AS endtime mktime("Step Due Date") AS cumulativeDueDate mktime("Step Actual Due Date") AS actualDueDate

    |eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
    |convert ctime(dueDateRange) timeformat="%+" 
    | eval pastDueDays =mvcount(mvfilter(NOT match(dueDateRange,"(Sun|Sat).*")))
    | mvexpand pastDueDays  | search NOT [| inputlookup exclude_holidays  | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S") | rename holiday_date as pastDueDays ]

    |eval pastDueDays=if(isnull(pastDueDays),"0", pastDueDays)
    |dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"

    | table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" pastDueDays

Right now it works but does not exclude the exclude_holiday dates from the lookup it just excludes weekends.

The csfv is simply holiday_date like:

holiday_date
2018-01-01 00:00:00
2018-01-15 00:00:00
2018-02-19 00:00:00
2018-05-28 00:00:00
2018-07-04 00:00:00
2018-09-03 00:00:00
2018-10-08 00:00:00
2018-11-12 00:00:00
2018-11-22 00:00:00
2018-12-25 00:00:00

Any ideas what would be preventing me from being able to use the lookup and exclude those dates?

[somesoni2]

Make the format of timestamp from the lookup table, same as your data.

| inputlookup exclude_holidays  | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S") | rename holiday_date as pastDueDays | eval pastDueDays=strftime(pastDueDays, "%+") 

[tkwaller_2]

@somesoni2 Here's What I have to fix but haven't yet figured out how.
In this search

index=dev_tsv "BO Type"="assessments"  
 |dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"
 | table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" 
 | convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Due Date") AS cumulativeDueDate 
 | eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
 | mvexpand dueDateRange
 | convert ctime(dueDateRange) timeformat="%+" 
 | where  NOT match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup exclude_holidays  | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S") | rename holiday_date as dueDateRange | eval dueDateRange=strftime(dueDateRange, "%+") ]

The initial value of dueDateRange here is formatted like
"%Y-%m-%d %H:%M:%S.%6N"
after input lookup it is
"%Y-%m-%d %H:%M:%S"
but the "%H:%M:%S" portion is always
"00:00:00" so I don't think they are being excluded as the fields in the actual data have valid "%H:%M:%S"
for example:
"2018-07-04 07:51:25.966000"

Any thoughts on how I can get this to work by not using the time inside the inputlookup value? Is it possbile to wildcard there?

[tkwaller_2]

so heres whats up just not sure how to fix it. So whats happening is pastDueDays is a count that gets expanded not timestamps like "Mon Jan 2 00:00:00 GMT 2012" . I'm guessing that is why the holidays aren't being excluded. It should probably count again after those are excluded right? How would I fix that though?

[somesoni2]

Didn't see that mvcount before. So my question, how is the field cumulativeDueDate calculated? Is it a field that comes in the raw data itself?

[tkwaller_2]

Yes that is correct, It is a field that's in the raw data.

[somesoni2]

Give this a try. (cleaned up a bit to only keep what's required in final output)

index=dev_tsv "BO Type"="assessments"  
|dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"
| table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" 
| convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Due Date") AS cumulativeDueDate 
| eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
| mvexpand dueDateRange
| convert ctime(dueDateRange) timeformat="%+" 
| where  match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup exclude_holidays  | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S") | rename holiday_date as dueDateRange | eval dueDateRange=strftime(dueDateRange, "%+") ]
| stats count as pastDueDays by "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order"

[tkwaller_2]

Yeah that doesnt return anything since
match(dueDateRange,"(Sun|Sat).")
needs to be something like
(mvfilter(NOT match(dueDateRange,"(Sun|Sat)."))
but that wont work

[somesoni2]

Yup.. forgot about including the NOT.. change where clause like this

 | where  NOT match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup..rest

[tkwaller_2]

For some reason that still returns nothing, hmmmm

[tkwaller_2]

I know why its not working but havent gotten it fixed yet. Its because the time in the timestamp is 00:00:00 but the time in the actual timestamps wont be that, it will be actual H:M:S so I'll have to fix that somehow.

[tkwaller_2]

returns nothing because
| where match(dueDateRange,"(Sun|Sat).*")

We want this to be NOT SAT|SUN