How to check lookup based time configurations and set up alerts accordingly

[ayushmaan_22]

I have a requirement to check if a employee shift roster(lookup in Splunk) covers 24 hours in a day for each team. If it doesn't cover, I need to send out an alert to the respective team notifying them that their respective shift roster is not configured properly. Can anybody help me out as to how I can proceed in this.

The employee_shift_roster.csv looks something like this:

Start time	End time	Team	Employee Name	Available
8:00	5:30	Team A	Roger	Y
5:30	8:00	Team A	Federer	Y
8:00	5:30	Team B	Novak	Y
5:30	7:00	Team B	Djokovic	Y

Now the alert should go out to Team B stating that their shift roster is not configured properly because 24 hours are not cover in shift.

Thanks in advance for the help 🙂

[ayushmaan_22]

Hey @ITWhisperer 

Thanks for the detailed and helpful response. This looks promising. I will try this out and will update the thread with further findings.

[ITWhisperer]

Assuming your times will actually be 24-hour clock times (and poor Roger and Novak aren't on 21.5 hour shifts!), you could do something like this

| makeresults format=csv data="Start time,End time,Team,Employee Name,Available
8:00,17:30,Team A,Roger,Y
17:30,8:00,Team A,Federer,Y
8:00,17:30,Team B,Novak,Y
17:30,7:00,Team B,Djokovic,Y"
``` The lines above create some simulated data based on your example ```
``` Convert start and end times to minutes of the day (assuming times are strings) ```
| eval start=60*tonumber(mvindex(split('Start time',":"),0))+tonumber(mvindex(split('Start time',":"),1))
| eval end=60*tonumber(mvindex(split('End time',":"),0))+tonumber(mvindex(split('End time',":"),1))
``` Determine how many days the shift is part of ```
| eval days=if(start < end,1,2)
``` Duplicate the event for multiple days ```
| eval day=mvrange(0,days)
| mvexpand day
``` Adjust start minute if second day ```
| eval start=if(days<2,start,if(day==1,0,start))
``` Adjust end minute if first day ```
| eval end=if(days<2, end,if(day==0,24*60,end))
``` Determine minutes covered by shift pattern ```
| eval minutes=mvrange(start,end)
| stats dc(minutes) as cover by Team
``` Find which teams do not have every minute covered ```
| where cover < 24*60

Depending on how your shift times are defined, you may be able to adjust this to use 30 minute spans (as suggested by your example), but the principle is the same.