Thanks for the mission Cassy Freeman. These are getting trickier each month!
My solution isn't elegant (limited time) but it works, and it could be modified to be much more elegant.
Here are the parameters I worked within:
- I decided I only want to populate a calendar for the upcoming month.
- The starting position was calculating the second Tuesday of the month
- Then I'd populate all other days using that "second Tuesday" as ground zero
- I wanted to have all the server patch times dynamic. e.g. if Server F needed to be changed to the Wed after patching Tuesday then this should be easy.
Calculating '2nd Tuesday'
To populate the patch calendar for the upcoming month, I created a site workflow that would run on the first of the month.
I then set a date variable and set it to "date the action was executed". NB for testing purposes, I could easily change this date to 1 Feb 2017 (or 1 March 2017) etc.
Then I needed to figure out what day of the week we were currently on. Andrew Glasser's Date and Time Quick Reference Guide came in handy once again. I chose the format string "dddd" which would give me the weekday name spelt out fully. Then it was some basic math to figure out when the 2nd Tuesday was going to occur. If "today" was Tuesday then it must be the first Tuesday so we only need to add 7 days whereas if "today" was Wednesday then we'd need to add 13 days.
Making the patching dates and servers dynamic
I created a list of servers and the number of days after patching Tuesday when their patch was due. If the server was due on patching Tuesday then I put 0 as the date offset.
With that in place, it'll be pretty easy to change when a server is patched. It's also really easy to add a new server. There is one major thing that could be improved - making "Patching Tuesday" dynamic. At the moment it requires a workflow change.
Populating the patching calendar
This bit was the easy bit - query the list of servers, grab their names and dates and plonk them into collections then iterate through the collections. The calculate date action is awesome here - all I needed to do is add the date offset to the Patching Tuesday date. Once that's all calculated, then populate the calendar and repeat for the next server.