All Apps and Add-ons

How to filter out days in a date range

niyaz006
Path Finder

Hi,

I have a list of tasks and their start and end date in an index. And list of holidays in a lookup file. I want to find the difference between start and end date. Also this should exclude the day if there are any holidays in between start and end date.

TaskStartEndDays
A5-Jan-205-Feb-20 
B15-Jan-205-Mar-20 
C1-Apr-2030-Apr-20 

 

Holidays:
1-Jan-20
27-Jan-20
10-Apr-20
11-Apr-20
12-Apr-20
13-Apr-20
....

Am currently during this using appendcols and mv commands which is making it very slow. Am looking for a direct/easier/perf optimized  solution

| appendcols
[ inputlookup Holidays.csv
| tableHolidays ]
| reverse
| eventstats list(Holidays) as Holidays delim="|"
| nomv Holidays
| eval between= mvrange(start, end, "1d"), date_diff = strftime(between,"%d/%m/%y %H:%M:%S")
| mvexpand date_diff
| where NOT match(date_diff, Holidays)
| stats list(date_diff) AS date_diff BY tasks

Labels (2)
0 Karma

to4kawa
Ultra Champion

sample:

 

| makeresults
| eval _raw="Task	Start	End	Days
A	5-Jan-20	5-Feb-20	 
B	15-Jan-20	5-Mar-20	 
C	1-Apr-20	30-Apr-20"
| multikv forceheader=1
| table Task	Start	End	Days
| eval Start_epoch=strptime(Start,"%d-%B-%y"), End_epoch=strptime(End,"%d-%B-%y")
| eval date_range=mvrange(Start_epoch,End_epoch,60*60*24)
| appendcols [|makeresults
| eval _raw="Holidays
1-Jan-20
27-Jan-20
10-Apr-20
11-Apr-20
12-Apr-20
13-Apr-20"
| multikv forceheader=1 
| eval query=strptime(Holidays,"%d-%B-%y")
| stats values(query) as Holidays ]
| filldown Holidays
| stats values(Task) as Task values(Holidays) as Holidays by date_range
| where !match(Holidays,date_range)
| stats count by Task

 

Your query is not full. so I can't make the query.

 

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...