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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...