source: //thistimeimeanit.com/wp-content/uploads/2011/06/officejuggler.jpg
One of the nightmare scenarios that a SQL Saturday organizer faces is selecting a speaker that is already going to another event on the same day. The speaker can only be at one event… and the site doesn’t really help us out with letting us know about these over-committed speakers.
So, I wrote this little PowerShell script to compare your selected speakers with the speakers selected at other SQL Saturday events that are on the same day. Now, by “selected speaker”, I mean those speakers that have been approved in the “Manage Sessions” section of the admin area of your event (those sessions don’t need to be on the schedule yet). Those sessions are then visible in the XML feed that is generated. This script will load the speakers for your event into an array, and then get the sessions for all of the other SQL Saturdays. If the other event is the same day as yours, it then compares the speakers. Finally, it will output those speakers that are selected in your event that are also selected in another event (aka the over-committed speakers) and the event that they are speaking at.
This script is based upon the script that Steve Jones’ wrote, however this script loads the information directly from the SQLSaturday.com web site each time that it runs. It could have used saved files (like Steve did), but this will be a bit easier to get changed speakers when either event changes the speakers that are presenting.
This script does not handle duplicate names (from a speaker presenting more than one session) at any of the events, so you may end up with duplicate output lines for a speaker.
Update: script updated on 2/9/2016 to handle > 1 duplicate speaker.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
<# Author: Wayne Sheffield Purpose: Powershell script to see if there are any speakers that you have selected to speak at your SQL Saturday event that are speaking at any other event on the same day. To Use: 1. Set the "$MyEventNumber" variable below to your SQL Saturday event number. 2. Run script (when this script was run, there were 527 SQL Saturdays to evaluate. This took 3 minutes, 15 seconds) Optional: Set the $EventNumber variable to the lowest SQL Saturday event number that is the same day as your event. Initial idea for how to load this data from Steve Jones' blog post at //voiceofthedba.wordpress.com/2015/01/26/downloading-sql-saturday-data/. Debug options: If the @debug option is set to a non-zero value, then these actions will occur: 1: the list of speakers at your event will be output to the screen. 2: the "guide" node of your event will be output to the screen. 3: the event name of the events being loaded will be output to the screen. ******************************************************************************* MODIFICATION LOG ******************************************************************************* 2016-02-08 WGS Initial Creation. 2016-02-09 WGS Handle > 1 duplicate speaker. ******************************************************************************* #> cls; $MyEventNumber = 486; $debug = 1; $EventNumber = 500; $baseUrl = "//www.sqlsaturday.com/eventxml.aspx?sat="; $Failed = 0; $DupeSpeakers = @(); $MyEventSpeakers = New-Object System.Collections.ArrayList $doc = New-Object System.Xml.XmlDocument; #load in "My" event information and speakers $sourceURL = $BaseURL + $MyEventNumber; $doc.Load($sourceURL); $guide = $doc.SelectNodes("GuidebookXML/guide"); if ($debug -eq 2) {Write-Host $guide}; $MyEventName = $guide.name; $MyEventDate = $guide.startDate; foreach ($speaker in $doc.SelectNodes("GuidebookXML/speakers/speaker")) { $MyEventSpeakers.Add($speaker.name) | Out-Null; } Write-Host "My Event/Date: " $MyEventName $MyEventDate; if ($debug -eq 1) { # print out the list of speakers at my event $MyEventSpeakers | Sort-Object } Write-Host ""; while ($EventNumber -lt 9999) { if ($MyEventNumber -ne $EventNumber) { $sourceURL = $BaseURL + $EventNumber; if ($debug -eq 2) { Write-Host "Source URL: $sourceURL"; } # debug messages Try { $doc.Load($sourceURL); $event = $doc.SelectNodes("GuidebookXML/guide"); $EventName = $event.name; $EventDate = $event.startDate; if ($debug -eq 3) { Write-Host $EventName $EventDate; } if ($MyEventDate -eq $EventDate) { Write-Host "Checking speakers at: " $EventName; $speakers = $doc.SelectNodes("GuidebookXML/speakers/speaker"); foreach ($speaker in $speakers) { if ($MyEventSpeakers -contains $speaker.name) { #OMG - this speaker is speaking somewhere else!!! $EventSpeaker = New-Object System.Object; $EventSpeaker | Add-Member -type NoteProperty -name Event -Value $event.name; $EventSpeaker | Add-Member -type NoteProperty -name Speaker -Value $speaker.name; $DupeSpeakers += $EventSpeaker; } #check for speaker speaking somewhere else } # check each speaker } # same event date as my event } #try Catch { # At some point, the EventNumber will reach a point past any current SQL Saturdays. # Keep track of the number of times that this script could not load a file in, # and when it gets to 10 times, abort the script by setting the current EventNumber to 9999. $Failed = $Failed + 1; if ($Failed -eq 10) { $EventNumber = 9999 } #failed 10 times } #catch } # not my event $EventNumber = $EventNumber + 1; } #while loop #show results if any if ($DupeSpeakers.Count -gt 0) { $DupeSpeakers | Sort-Object Event, Speaker | Format-Table; } |
If anyone knows how to get a feed of the submitted sessions, I’ll create another script for comparing the sites for speakers that have submitted to multiple locations on the same day. I’d rather not even get to the selected phase if it can be avoided. If you know how to do this, just leave me a comment below please (or contact me directly).
