How to change SPL comparison dates in dashboard with a CASE clause dynamically?

[gazuluagac]

Hello, I'm new to Splunk and I was searching and trying many solutions before asking here, but I'm really stuck.

I have my first assignment at work, there is a dashboard with a CASE clause comparing dates- those dates are Canadian holidays. Currently, those dates are written in the SPL, but I need to replace them with something dynamically as a lookup.

So I just created a lookup like this:

date,day
2022-01-03,New Year's Day
2022-01-17,Martin Luther King Jr. Day
2022-02-21,Family Day
2022-04-15,Good Friday
2022-05-23,Victoria Day
2022-05-30,Memorial Day
2022-06-20,Juneteenth National Independence Day
2022-07-01,Canada Day
2022-07-04,Independence Day
2022-08-01,Civic Holiday
2022-09-05,Labour Day
2022-10-10,Canada Thanksgiving Day
2022-11-24,US Thanksgiving Day
2022-12-26,Christmas Day
2022-12-27,Boxing Day

The Dashboard looks like this:

index=main_index sourcetype=main_sourcetype

| eval secondDayOfMonth = strftime(strptime(StartTime, "%Y%m%d-%H:%M:%S"), "%Y-%m-%d")

| eval CuttOffHour=case(					
						secondDayOfMonth="2022-01-03" , 1,
						secondDayOfMonth="2022-01-17" , 2,
						secondDayOfMonth="2022-02-21" , 3,
						secondDayOfMonth="2022-04-15" , 4,
						secondDayOfMonth="2022-05-23" , 5,
						secondDayOfMonth="2022-05-30" , 6,
						secondDayOfMonth="2022-06-20" , 7,
						secondDayOfMonth="2022-07-01" , 8,
						secondDayOfMonth="2022-07-04" , 9,
						secondDayOfMonth="2022-08-01" , 10,
						secondDayOfMonth="2022-09-05" , 11,
						secondDayOfMonth="2022-10-10" , 12,
						secondDayOfMonth="2022-11-24" , 13,
						secondDayOfMonth="2022-12-26" , 14,
						secondDayOfMonth="2022-12-27" , 15,
				)
				
| table secondDayOfMonth CuttOffHour
| rename secondDayOfMonth as "SECOND DAY OFF MONTH" CuttOffHour as "CUT OFF HOUR" 

I was trying different solutions like:

| eval CuttOffHour=case(					
						secondDayOfMonth=[inputlookup holydays_lookup] , 1,

| eval CuttOffHour=case(					
						secondDayOfMonth=search[inputlookup holydays_lookup] , 1,

 And other 10 attempts always return an error.

Help me please, I want to learn.

[gazuluagac · Accepted solution]

Solution:

index=main_index sourcetype=main_sourcetype

| eval secondDayOfMonth = strftime(strptime(StartTime, "%Y%m%d-%H:%M:%S"), "%Y-%m-%d")

| lookup gwms_exclude_holidays_2022.csv date as secondDayOfMonth OUTPUT date as Holiday

| eval CuttOffHour=case(
					AppName="ADSR - PureFacts" AND weekGroup="Tuesday-Friday", 09, 
					AppName="ADSR - PureFacts" AND weekGroup="Saturday", 10,
					
					AppName="ADSR - Performance (Aspire)" AND weekGroup="Tuesday-Friday" AND secondDayOfMonth=Holiday, 12, 
					AppName="ADSR - Performance (Aspire)" AND weekGroup="Saturday" AND secondDayOfMonth=Holiday, 12
)
| table secondDayOfMonth CuttOffHour
| rename secondDayOfMonth as "SECOND DAY OFF MONTH" CuttOffHour as "CUT OFF HOUR"