|
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 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
<# .SYNOPSIS Powershell script to see if there are if there are any SQL Saturday sessions that are about a specified phrase. .DESCRIPTION This script will search the SQL Saturday sessions, looking in both the session title and description, for the specified phrase. The search stops after being unable to load 10 events. Screen Output: Event Number, Speaker, Session Title, Session URL Optional parameters allow you to control which SQL Saturday event number to start looking at, and the starting and ending date to look through. The optional parameter $ExportFile allows you to export this information to a comma-separated file. When this is used, the event name and event description are also included in the export, and the URL inside the excel "HYPERLINK" function, allowing you to open the file with Excel and just click the link to see the session information. .PARAMETER $SearchTopic The topic that you want to search. Required entry. .PARAMETER $StartDate The first event date that you want to search the events. Default: 30 days prior to today. .PARAMETER $EndDate The last event date that you want to search the events. Default: 30 days after today. .PARAMETER $EventNumber The first event number that you want to check for matching dates. Default: 500 .PARAMETER $ExportFile Where you want to send a csv export file to. Default: empty string (export file not created) .PARAMETER $DebugLevel Control debug output messages while script is running. Default: 1 Values: 1: Display SQL Saturday URL as that SQL Saturday is processed. 2. Also display event name / date if that URL could be opened. 3. Also display session title for matched sessions. 4. Also display all session titles. .EXAMPLE .\Get-SQLSaturdaySessionTopicSearch.ps1 -SearchTopic 'Query Store' .EXAMPLE .\Get-SQLSaturdaySessionTopicSearch.ps1 -SearchTopic 'Query Store' -ExportFile 'C:\Temp\SQLSatSearchResults.csv' .NOTES Author: Wayne Sheffield Script Home Page: //blog.waynesheffield.com/wayne/get-sqlsaturdaysessiontopicsearch Initial idea for how to load this data is from Steve Jones' blog post at //voiceofthedba.wordpress.com/2015/01/26/downloading-sql-saturday-data/. ******************************************************************************* MODIFICATION LOG ******************************************************************************* 2016-06-24 WGS Initial Creation. ******************************************************************************* #> Param ( [Parameter(Mandatory=$true)] [string]$SearchTopic = '', [datetime]$StartDate = (Get-Date -Hour 0 -Minute 0 -Second 0).AddDays(-30), [datetime]$EndDate = (Get-Date -Hour 0 -Minute 0 -Second 0).AddDays(30), [int]$EventNumber = 500, [string]$ExportFile = '', [int]$DebugLevel = 1 ) cls; $baseUrl = "//www.sqlsaturday.com/eventxml.aspx?sat="; $Failed = 0; $EventList = @(); $MyEventSpeakers = New-Object System.Collections.ArrayList $doc = New-Object System.Xml.XmlDocument; $sourceURL = $BaseURL + $EventNumber; $doc.Load($sourceURL); $guide = $doc.SelectNodes("GuidebookXML/guide"); $EventDate = $guide.startDate; # add wildcards $SearchTopic = "*" + $SearchTopic + "*"; while ($EventNumber -lt 9999) { $sourceURL = $baseUrl + $EventNumber; if ($DebugLevel -ge 1) { Write-Host "Source URL: $sourceURL"; } # debug messages at level 1 try { $doc.Load($sourceURL); $event = $doc.SelectNodes("GuidebookXML/guide"); $EventName = $event.name; [datetime]$EventDate = $event.startDate; if ($DebugLevel -ge 2) { Write-Host $EventName $EventDate } # debug messages at level 2 if ($EventDate -ge $StartDate -and $EventDate -le $EndDate) { $sessions = $doc.SelectNodes("GuidebookXML/events/event") foreach ($session in $sessions) { if ($DebugLevel -ge 4) { Write-Host $session.title } # debug messages at level 4 if ($session.title -like $SearchTopic -or $session.description -like $SearchTopic) { if ($DebugLevel -ge 3) { Write-Host "Found a match: " + $session.title; } #message $Event = New-Object System.Object; $Event | Add-Member -type NoteProperty -Name Event -Value $EventNumber; $Event | Add-member -type NoteProperty -Name EventName -Value $EventName; $Event | Add-Member -type NoteProperty -Name Speaker -Value $session.speakers.speaker.name; $Event | Add-Member -type NoteProperty -Name Session -Value $session.title; $Event | Add-Member -type NoteProperty -Name Abstract -Value $session.description; $url = "//www.sqlsaturday.com/$EventNumber/Sessions/Details.aspx?sid=" + $session.importID; $Event | Add-Member -type NoteProperty -Name Url -Value $url; $EventList += $Event; } #if session has phrase } #foreach session } #if event in date range } #try catch { $Failed = $Failed + 1; if ($Failed -eq 10) { $EventNumber = 9999 } #if failed } #catch $EventNumber += 1; } #while #Show results if any if ($EventList.Count -gt 0) { $EventList | Sort-Object Event, Session | Format-Table -Autosize -Wrap -Property Event,Speaker,Session,Url; } #Export to csv if filename specified if ($ExportFile -gt '') { ForEach ($Event in $EventList) { $Event.Url = '=HYPERLINK("' + $Event.Url + '")' } $EventList | Sort-Object Event, Session | Export-csv $ExportFile -NoTypeInformation; } |
