|
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; } |
