SQLBitsPS.psm1
|
#Region './Public/Get-SQLBitsEmptySession.ps1' -1 function Get-SQLBitsEmptySession { <# .SYNOPSIS A helper function to get the number of empty sessions at SQLBits .DESCRIPTION Just a helper function to get the number of empty sessions at SQLBits .PARAMETER output The type of output required. Valid values are Raw(default), Grouped or Total .EXAMPLE Get-SQLBitsEmptySessionCount Day StartTime EndTime EmptySessions --- --------- ------- ------------- Thursday 10:10 11:00 1 Thursday 11:30 11:50 3 Thursday 12:00 12:50 1 Thursday 13:40 14:00 0 Thursday 14:10 15:00 0 Returns the number of empty sessions at SQLBits per time slot as a psobject .EXAMPLE Get-SQLBitsEmptySessionCount -output Grouped Name Count Thursday 4 Friday 3 Saturday 3 Returns the number of empty sessions at SQLBits per day .EXAMPLE Get-SQLBitsEmptySessionCount -output Total 10 Returns the total number of empty sessions at SQLBits .NOTES Rob Sewell 2023 #> [cmdletbinding()] Param( [Parameter()] [ValidateSet('Raw','Grouped', 'Total')] [string] $output = 'Raw', [Parameter()] [switch]$excludeZeros ) $Schedule = Get-SQLBitsSchedule -output object $KeyNotes = 'Keynote by The Community', 'Opening Keynote' $plenarysessions = 'Registration', 'Quick Break', 'Closing Keynote and Prize Giving', 'End - TearDown', 'Coffee Break', 'Lunch', 'Free Time', 'Prize Giving', 'Party', 'Pub Quiz', 'Keynote by the community', 'End - TearDown' $sessionss = $Schedule | Where-Object {($_.'Gate 1'.Trim() -notin $plenarysessions ) -and ($_.'Gate 1'.Trim() -notlike 'KeyNote by the community*') -and ($_.'Gate 1'.Trim() -notlike 'SQLBits 2024 Opening Keynote*') }| Select-Object * -ExcludeProperty 'Gate 13','Experts Lounge' $rawOutput = foreach ($time in $sessionss) { $SessionCount = ($time.psobject.Properties.Where{$_.Value -eq ' ' }).Count $Message = "{0} {1} has {2} empty sessions" -f $time.Day, $time.StartTime, $SessionCount Write-PSFMessage $message -Level Verbose [pscustomobject]@{ Day = $time.Day StartTime = $time.StartTime EndTime = $time.EndTime EmptySessions = $SessionCount } } switch ($output) { 'Raw' { if($excludeZeros){ $rawOutput | Where-Object { $_.EmptySessions -gt 0 } } else { $rawOutput } } 'Grouped' { if($excludeZeros){ Write-Output "Can't exclude zeros when grouping" } $Summary = @{Name='EmptySessions';Expression={($_.Group | Measure-Object -Property EmptySessions -Sum).Sum}} $rawOutput | Group-Object Day | Select-Object Name, $Summary } 'Total' { if($excludeZeros){ Write-Output "Can't exclude zeros when summing" } ($rawOutput | Measure-Object -Property EmptySessions -Sum).Sum } Default { $RawOutput } } } #EndRegion './Public/Get-SQLBitsEmptySession.ps1' 107 #Region './Public/Get-SqlBitsPanelSessions.ps1' -1 function Get-SqlBitsPanelSessions { <# .SYNOPSIS Gets all the SQLBits sessions that have more than one speaker .DESCRIPTION Gets all the SQLBits sessions that have more than one speaker .PARAMETER ExcludeCommunityCorner Exclude the Community Corner sessions .PARAMETER includeSponsorSessions Do we want to include the sessions here .EXAMPLE Get-SqlBitsPanelSessions Returns all the SQLBits sessions that have more than one speaker .EXAMPLE Get-SqlBitsPanelSessions -ExcludeCommunityCorner Returns all the SQLBits sessions that have more than one speaker excluding the Community Corner sessions .NOTES Rob Sewell, 2020 #> [CmdletBinding()] param ( [Parameter()] [switch] $ExcludeCommunityCorner, [switch] $includeSponsorSessions ) if (-not $includeSponsorSessions) { $excludedSessions = @( "Data Modernization in a Hybrid World", "Azure Stack HCI and Microsoft Hybrid Data Services", "The Microsoft Intelligent Data Platform and Dell" ) } else { $excludedSessions = @() } $speakerCount = @{Name = 'NumberOfSpeakers'; Expression = { ($_.Speakers -split ',').Count } } switch ($ExcludeCommunityCorner) { $true { Get-SQLBitsSession | where room -NE 'Community Corner' | where title -NotIn $excludedSessions | select title, room, $speakerCount, startsAt, endsAt | where 'NumberOfSpeakers' -GT 2 } $false { Get-SQLBitsSession | where title -NotIn $excludedSessions | select title, room, $speakerCount, startsAt, endsAt | where 'NumberOfSpeakers' -GT 2 } Default {} } } #EndRegion './Public/Get-SqlBitsPanelSessions.ps1' 59 #Region './Public/Get-SQLBitsSchedule.ps1' -1 function Get-SQLBitsSchedule { <# .SYNOPSIS Gets the SQLBits Schedule from the Sessionize API .DESCRIPTION Gets the SQLBits Schedule from the Sessionize API and outputs to json, excel, psobject, html or csv .PARAMETER output The type of output required. Valid values are json, excel, psobject, html or csv .PARAMETER search A wild card search best used to find a speaker .PARAMETER fileDirectory The directory to save the output file to - defaults to Env:Temp .PARAMETER Show Whether to open the output file after it has been created .EXAMPLE Get-SQLBitsSchedule -output Excel -Show Gets the SQLBits Schedule from the Sessionize API and outputs to excel, opens the file and saves it to the default temp directory .EXAMPLE Get-SQLBitsSchedule -output Raw Gets the SQLBits Schedule from the Sessionize API and outputs as json on the screen .EXAMPLE Get-SQLBitsSchedule -output csv -Show Gets the SQLBits Schedule from the Sessionize API and outputs to csv, opens the file and saves it to the default temp directory .EXAMPLE Get-SQLBitsSchedule -output object Gets the SQLBits Schedule from the Sessionize API and outputs as a psobject on the screen .EXAMPLE Get-SQLBitsSchedule -output html -Show Gets the SQLBits Schedule from the Sessionize API and outputs to html, opens the file and saves it to the default temp directory .EXAMPLE Get-SQLBitsSchedule -search Buck -output object | ft Day StartTime EndTime Room speakers Session --- --------- ------- ---- -------- ------- Thursday 14:10 15:00 MR 1B Buck Woody Arc, Arc and Arc: De-mystifying Azure Arc for Data… Friday 12:00 12:50 Expo Room 2 Buck Woody The Microsoft Intelligent Data Platform… Gets the SQLBits Schedule from the Sessionize API searches fro Buck and outputs an object .NOTES Author: Rob Sewell December 2022 #> [CmdletBinding()] param ( [Parameter()] [ValidateSet('raw', 'excel', 'object', 'csv', 'html')] $output = 'excel', [string] $search, [string] $fileDirectory = $env:TEMP, [switch] $show ) $BaseUri = 'https://sessionize.com/api/v2/8utc2qgu/view' $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss' #TODO Add other options $filter = 'Schedule' switch ($filter) { 'All' { $uri = '{0}/All' -f $BaseUri } 'Schedule' { $uri = '{0}/All' -f $BaseUri } 'Sessions' { $uri = '{0}/sessions' -f $BaseUri } 'speakers' { $uri = '{0}/speakers' -f $BaseUri } Default { $uri = '{0}/All' -f $BaseUri } } $Data = Invoke-RestMethod -Uri $uri if (-not $Data) { Write-Warning 'No data returned from Sessionize API' return } $rooms = ($data.rooms | Sort-Object name) if (-not $rooms) { Write-Warning 'No rooms returned from Sessionize API' return } $speakers = $data.speakers if (-not $speakers) { Write-Warning 'No speakers returned from Sessionize API' return } # Thank you Shane - https://nocolumnname.blog/2020/10/29/pivot-in-powershell/ $props = @( @{ Name = 'Day' ; Expression = { $Psitem.Group[0].startsAt.DayOfWeek } } @{ Name = 'Date' ; Expression = { $Psitem.Group[0].startsAt.tolongdatestring() } } @{ Name = 'StartTime' ; Expression = { $Psitem.Group[0].startsAt.ToShortTimeString() } } @{ Name = 'EndTime' ; Expression = { $Psitem.Group[0].EndsAt.ToShortTimeString() } } foreach ($room in $rooms) { $rn = $room.Name @{ Name = $rn Expression = { '{0} {1}' -f @( ($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).title, (($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).speakers.ForEach{ $speakers | Where-Object id -EQ $_ }.FullName -join ' ') ) }.GetNewClosure() } } ) if ($IsCoreCLR) { $rawsessions = $Data.sessions } else { $rawsessions = $Data.sessions | select -Property id, title, @{Name = 'startsAt'; expression = { [datetime]$_.startsAt } } , @{Name = 'endsAt'; expression = { [datetime]$_.endsAt } }, roomID, speakers } $sessions = $rawsessions | Group-Object -Property StartsAt | Select-Object $props # if we have a search filter, filter the sessions if ($search) { $Results = @{Name = 'Results'; Expression = { $_.psobject.properties.Value -like "*$search*" } } $RoomSearch = @{Name = 'Room'; Expression = { ($_.psobject.properties | Where-Object { $_.Value -like "*$search*" } ).Name } } $speakerSearch = @{Name = 'speakers'; Expression = { ($_.Results -Split "`n")[1] } } $Session = @{Name = 'Session'; Expression = { ($_.Results -Split "`n")[0] } } $sessions = $sessions | Select-Object -Property *, $RoomSearch, $Results | Where-Object { $null -ne $_.Results } | Select-Object -Property Day, StartTime, EndTime, Room, $speakerSearch, $Session } switch ($output) { 'Raw' { $Data } 'object' { $sessions } 'Excel' { if (Get-Module -Name ImportExcel -ErrorAction SilentlyContinue -ListAvailable) { if ($filter -eq 'Schedule') { $FilePath = '{0}\SQLBitsSchedule{1}_{2}.xlsx' -f $fileDirectory, $filter, $Date $sessions | Group-Object Day | ForEach-Object { $worksheetName = $_.Name $excel = $_.Group | Export-Excel -Path $FilePath -WorksheetName $worksheetName -AutoSize -FreezePane 2, 5 -PassThru 1..15 | ForEach-Object { Set-ExcelRow -ExcelPackage $excel -WorksheetName $worksheetName -Row $_ -Height 30 -WrapText } $rulesparam = @{ Address = $excel.Workbook.Worksheets[$WorkSheetName].Dimension.Address WorkSheet = $excel.Workbook.Worksheets[$WorkSheetName] } Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Coffee Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Quick Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Keynote",$E1)))' -BackgroundColor BlueViolet -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Lunch",$E1)))' -BackgroundColor Chocolate -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Prize",$E1)))' -BackgroundColor PowderBlue -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Free Time",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Registration",$E1)))' -BackgroundColor DarkOrange -ForegroundColor White -StopIfTrue Close-ExcelPackage $excel } if ($Show) { Invoke-Item $filepath } else { Write-Output "Excel file saved to $FilePath" } } } else { Write-Warning 'You need to install ImportExcel to use this option but here is a CSV instead' $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation if ($Show) { Invoke-Item $filepath } else { Write-Output "Csv file saved to $FilePath" } } } 'CSv' { $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation if ($Show) { Invoke-Item $filepath } else { Write-Output "Csv file saved to $FilePath" } } 'html' { $FilePath = '{0}\SQLBits_{1}_{2}.html' -f $fileDirectory, $filter, $Date $sessions | ConvertTo-Html | Out-File $FilePath if ($Show) { Invoke-Item $filepath } else { Write-Output "Html file saved to $FilePath" } } Default { } } } #EndRegion './Public/Get-SQLBitsSchedule.ps1' 233 #Region './Public/Get-SqlBitsServiceSession.ps1' -1 function Get-SqlBitsServiceSession { <# .SYNOPSIS Gets the Service sessions from the Sessionize API. .DESCRIPTION Gets all the Service sessions from the Sessionize API and outputs to json, excel, psobject, html or csv .PARAMETER type The type parameter accepts a string that specifies the type of the session. Valid options are 'Break', 'Sponsor50', 'Sponsor20', 'Microsoft', 'Lunch', 'Other', 'All'. The default value is 'All'. .PARAMETER show The show parameter accepts a string that specifies the level of detail of the session information. Valid options are 'Brief', 'Detailed', 'All'. The default value is 'Detailed'. .PARAMETER day The day parameter accepts a string that specifies the day of the session. Valid options are 'Wed', 'Thurs', 'Fri', 'Sat', 'All'. The default value is 'All'. .EXAMPLE Get-SqlBitsServiceSession -type 'Sponsor50' -show 'Detailed' This example retrieves all 'Sponsor50' type sessions with 'Detailed' information for all days. .EXAMPLE Get-SqlBitsServiceSession -type 'Break' -show 'Detailed' -day 'Wed' This example retrieves all Breaks with 'Detailed' information for 'Wednesday'. .EXAMPLE Get-SqlBitsServiceSession -type 'Microsoft' -show 'Brief' -day 'All' This example retrieves all 'Microsoft' sessions with 'Brief' information for all days. .NOTES Rob Sewell #> [CmdletBinding()] param ( [string] [ValidateSet('Break', 'Sponsor50', 'Sponsor20', 'Microsoft', 'Lunch', 'Other', 'All')] $type = 'All', [string] [ValidateSet('Brief', 'Detailed', 'All')] $show = 'Detailed', [string] [ValidateSet('Wed', 'Thurs', 'Fri', 'Sat', 'All')] $day = 'All' ) $BaseUri = 'https://sessionize.com/api/v2/8utc2qgu/view/GridSmart' $Data = Invoke-RestMethod -Uri $BaseUri if (-not $Data) { Write-Warning 'No data returned from Sessionize API' return } $filteredSessions = $data | ForEach-Object { $_.rooms | ForEach-Object { $_.sessions | ForEach-Object { if ($_.IsServiceSession) { [PSCustomObject]@{ id = $_.id title = $_.title description = $_.description Day = $_.startsAt.DayOfWeek startsAt = $_.startsAt endsAt = $_.endsAt isServiceSession = $_.isServiceSession isPlenumSession = $_.isPlenumSession speakers = $_.speakers categories = $_.categories roomId = $_.roomId room = $_.room liveUrl = $_.liveUrl recordingUrl = $_.recordingUrl status = $_.status isInformed = $_.isInformed isConfirmed = $_.isConfirmed } } } } } switch ($day) { 'Wed' { $filteredSessions = $filteredSessions | Where-Object { $_.Day -eq 'Wednesday' } } 'Thurs' { $filteredSessions = $filteredSessions | Where-Object { $_.Day -eq 'Thursday' } } 'Fri' { $filteredSessions = $filteredSessions | Where-Object { $_.Day -eq 'Friday' } } 'Sat' { $filteredSessions = $filteredSessions | Where-Object { $_.Day -eq 'Saturday' } } 'All' { } } switch ($type) { 'Break' { $output = $filteredSessions | Where-Object { $_.title -like '*break*' } } 'Sponsor50' { $output = $filteredSessions | Where-Object { $_.title -like '*Sponsor*Session*50*' } } 'Sponsor20' { $output = $filteredSessions | Where-Object { $_.title -like '*Sponsor*Session*20*' } } 'Microsoft' { $output = $filteredSessions | Where-Object { $_.title -like '*Microsoft*' } } 'Lunch' { $output = $filteredSessions | Where-Object { $_.title -like '*lunch*' } } 'Other' { $output = $filteredSessions | Where-Object { $_.title -notlike '*lunch*' } | Where-Object { $_.title -notlike '*Microsoft*' } | Where-Object { $_.title -notlike '*Sponsor*' } } 'All' { $output = $filteredSessions } Default { $output = $filteredSessions } } switch ($show) { 'Brief' { $Output | Select-Object Day, title, startsAt } 'Detailed' { $output | Select-Object Day, title, description, startsAt, endsAt } 'All' { $output } } } #EndRegion './Public/Get-SqlBitsServiceSession.ps1' 129 #Region './Public/Get-SqlBitsSession.ps1' -1 function Get-SQLBitsSession { <# .SYNOPSIS Gets the sessions from the Sessionize API. .DESCRIPTION Gets all the sessions from the Sessionize API and outputs to json, excel, psobject, html or csv .PARAMETER search searches all properties for the search term .PARAMETER all returns all the sessions .EXAMPLE Get-SQLBitsSession -search 'Power Bi' Searches for the term 'Power Bi' in all the sessions .EXAMPLE Get-SQLBitsSession -search 'Community Corner' Searches for all the sessions in the Community Corner .EXAMPLE Get-SQLBitsSession -search 'Rie Merritt' Searches for all the sessions by Rie Merritt .NOTES Rob Sewell #> [CmdletBinding()] param ( [string] $search, [switch] $all ) $BaseUri = 'https://sessionize.com/api/v2/8utc2qgu/view' $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss' #TODO Add other options $filter = 'Sessions' switch ($filter) { 'All' { $uri = '{0}/All' -f $BaseUri } 'Schedule' { $uri = '{0}/All' -f $BaseUri } 'Sessions' { $uri = '{0}/sessions' -f $BaseUri } 'speakers' { $uri = '{0}/speakers' -f $BaseUri } Default { $uri = '{0}/All' -f $BaseUri } } $Data = Invoke-RestMethod -Uri $uri if (-not $Data) { Write-Warning 'No data returned from Sessionize API' return } $sessions = ($data.sessions | Sort-Object id) if ($IsCoreCLR) { $rawsessions = $sessions } else { $rawsessions = $sessions | select -Property id, title, @{Name = 'startsAt'; expression = { [datetime]$_.startsAt } } , @{Name = 'endsAt'; expression = { [datetime]$_.endsAt } }, roomID, speakers } $Speakers = @{Name='Speakers'; Expression = {$PsItem.speakers.name -join ', '}} $PrimaryTheme = @{Name='PrimaryTheme'; Expression = {($PsItem.categories | Where Name -eq 'Primary Theme').categoryItems.name}} $SessionLength = @{Name='SessionLength'; Expression = {($PsItem.categories | Where name -eq 'Session Length').categoryItems.name -replace ' sessoin', ''}} $sessionizeUrl = @{Name='sessionizeUrl'; Expression = {'https://sessionize.com/app/organizer/session/12744/{0}' -f $PSItem.id}} if($all){ $rawsessions | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room,id, $sessionizeUrl }else{ if ($search) { $Results = @{Name = 'Results'; Expression = { $_.psobject.properties.Value -like "*$search*" } } $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select-Object -Property *, $Results | Where-Object { $null -ne $_.Results } | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room,id } else { $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room,id } } } #EndRegion './Public/Get-SqlBitsSession.ps1' 102 #Region './Public/Get-SQLBitsSpeakers.ps1' -1 function Get-SQLBitsSpeakers { <# .SYNOPSIS Returns the SQLBits Speakers from the Sessionize API .DESCRIPTION This function returns the SQLBits Speakers from the Sessionize API .PARAMETER search Filters the results by the search term .PARAMETER remote A switch to filter the results to only remote speakers .PARAMETER full Returns the full object as output .EXAMPLE Get-SQLBitsSpeakers Returns all the SQLBits Speakers from the Sessionize API .EXAMPLE Get-SQLBitsSpeakers -search 'Rob' Returns all the SQLBits Speakers from the Sessionize API that contain Rob in their name .EXAMPLE Get-SQLBitsSpeakers -remote Returns all the SQLBits Speakers from the Sessionize API that are remote .EXAMPLE Get-SQLBitsSpeakers -full Returns all the SQLBits Speakers from the Sessionize API as a full object .NOTES Rob Sewell - January 2023 #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute( <#Category#>'PSUseSingularNouns',<#CheckId#>$null, Justification = 'because my beard is glorious' )] param ( [Parameter(Mandatory = $false)] [string]$search, [switch]$remote, [switch]$full ) begin { $BaseUri = 'https://sessionize.com/api/v2/8utc2qgu/view' $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss' $uri = '{0}/speakers' -f $BaseUri $sessionuri = '{0}/sessions' -f $BaseUri $AllSessions = Invoke-RestMethod -Uri $sessionuri $Isremote = @{Name='IsRemote';Expression={($_.categories | Where-Object {$_.id -eq '44351';}).categoryItems.name}} $CompanyName = @{Name='CompanyName';Expression={($_.questionAnswers | Where-Object {$_.id -eq 43369}).Answer}} $LinkedIn = @{Name='LinkedIn';Expression={($_.links | Where-Object {$_.linktype -eq 'LinkedIn'}).url}} $Sessionize = @{Name='Sessionize';Expression={($_.links | Where-Object {$_.linktype -eq 'Sessionize'}).url}} $Blog = @{Name='Blog';Expression={($_.links | Where-Object {$_.linktype -eq 'Blog'}).url}} $Facebook = @{Name='Facebook';Expression={($_.links | Where-Object {$_.linktype -eq 'Facebook'}).url}} $Twitter = @{Name='Twitter';Expression={($_.links | Where-Object {$_.linktype -eq 'Twitter'}).url}} $CompanyWebsite = @{Name='Company Website';Expression={($_.links | Where-Object {$_.linktype -eq 'Company Website'}).url}} $Other = @{Name='Other';Expression={($_.links | Where-Object {$_.linktype -eq 'Other'}) | ForEach-Object { $_ }}} $SessionNames = @{ Name='SessionDetails';Expression={$_.sessions | ForEach-Object { $id = $_.id $Session = $AllSessions.Sessions|Where-Object{$_.id -eq $id} [PSCustomObject]@{ Name = $_.name.Trim() Room = $Session.Room Starts = $Session.startsAt Ends = $Session.endsAt } } } } $Data = Invoke-RestMethod -Uri $uri $Data = $Data|Select-Object *,$CompanyName,$Isremote,$LinkedIn,$Sessionize,$Blog,$Facebook,$Twitter,$CompanyWebsite,$Other,$sessionNames if (-not $Data) { Write-Warning 'No data returned from Sessionize API' return } } process { if($search) { $Data = $Data | Where-Object { $_.fullName -like "*$search*" } } if($remote) { $Data = $Data | Where-Object { $_.IsRemote -eq 'Remote' } } if($full) { $Data | Select-Object -ExcludeProperty id,isTopSpeaker,questionAnswers,categories,links } else { $Data | Select-Object fullName, companyName,tagLine, $SessionNames } } end { } } #EndRegion './Public/Get-SQLBitsSpeakers.ps1' 110 #Region './Public/Get-SqlBitsTDSession.ps1' -1 function Get-SQLBitsTDSession { <# .SYNOPSIS Gets the TD sessions from the Sessionize API. .DESCRIPTION Gets all the TD sessions from the Sessionize API and outputs to json, excel, psobject, html or csv .PARAMETER search searches all properties for the search term .PARAMETER all returns all the sessions .EXAMPLE Get-SQLBitsTD Session -search 'Power Bi' Searches for the term 'Power Bi' in all the sessions .EXAMPLE Get-SQLBitsTDSession -search 'Rie Merritt' Searches for all the sessions by Rie Merritt .NOTES Rob Sewell #> [CmdletBinding()] param ( [string] $search, [switch] $all ) $BaseUri = 'https://sessionize.com/api/v2/sydgl43f/view/' $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss' #TODO Add other options $filter = 'Sessions' switch ($filter) { 'All' { $uri = '{0}/All' -f $BaseUri } 'Schedule' { $uri = '{0}/All' -f $BaseUri } 'Sessions' { $uri = '{0}/sessions' -f $BaseUri } 'speakers' { $uri = '{0}/speakers' -f $BaseUri } Default { $uri = '{0}/All' -f $BaseUri } } $Data = Invoke-RestMethod -Uri $uri if (-not $Data) { Write-Warning 'No data returned from Sessionize API' return } $sessions = ($data.sessions | Sort-Object id) if ($IsCoreCLR) { $rawsessions = $sessions } else { $rawsessions = $sessions | select -Property id, title, @{Name = 'startsAt'; expression = { [datetime]$_.startsAt } } , @{Name = 'endsAt'; expression = { [datetime]$_.endsAt } }, roomID, speakers } $Speakers = @{Name='Speakers'; Expression = {$PsItem.speakers.name -join ', '}} $PrimaryTheme = @{Name='PrimaryTheme'; Expression = {($PsItem.categories | Where Name -eq 'Primary Theme').categoryItems.name}} $SessionLength = @{Name='SessionLength'; Expression = {($PsItem.categories | Where id -eq 34075).categoryItems.name -replace ' sessoin', ''}} if($all){ $rawsessions | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room }else{ if ($search) { $Results = @{Name = 'Results'; Expression = { $_.psobject.properties.Value -like "*$search*" } } $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select-Object -Property *, $Results | Where-Object { $null -ne $_.Results } | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room,id } else { $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room,id } } } #EndRegion './Public/Get-SqlBitsTDSession.ps1' 97 #Region './Public/Get-SQLBitsTDSpeakers.ps1' -1 function Get-SQLBitsTDSpeakers { <# .SYNOPSIS Returns the SQLBits TD Speakers from the Sessionize API .DESCRIPTION This function returns the SQLBits TD Speakers from the Sessionize API .PARAMETER search Filters the results by the search term .PARAMETER remote A switch to filter the results to only remote speakers .PARAMETER full Returns the full object as output .EXAMPLE Get-SQLBitsTDSpeakers Returns all the SQLBits Speakers from the Sessionize API .EXAMPLE Get-SQLBitsTDSpeakers -search 'Rob' Returns all the SQLBits Speakers from the Sessionize API that contain Rob in their name .EXAMPLE Get-SQLBitsTDSpeakers -remote Returns all the SQLBits Speakers from the Sessionize API that are remote .EXAMPLE Get-SQLBitsTDSpeakers -full Returns all the SQLBits Speakers from the Sessionize API as a full object .NOTES Rob Sewell - January 2023 #> [CmdletBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute( <#Category#>'PSUseSingularNouns',<#CheckId#>$null, Justification = 'because my beard is glorious' )] param ( [Parameter(Mandatory = $false)] [string]$search, [switch]$remote, [switch]$full ) begin { $BaseUri = 'https://sessionize.com/api/v2/sydgl43f/view' $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss' $uri = '{0}/speakers' -f $BaseUri $sessionuri = '{0}/sessions' -f $BaseUri $AllSessions = Invoke-RestMethod -Uri $sessionuri $Isremote = @{Name='IsRemote';Expression={($_.categories | Where-Object {$_.id -eq '44351';}).categoryItems.name}} $CompanyName = @{Name='CompanyName';Expression={($_.questionAnswers | Where-Object {$_.id -eq 43369}).Answer}} $LinkedIn = @{Name='LinkedIn';Expression={($_.links | Where-Object {$_.linktype -eq 'LinkedIn'}).url}} $Sessionize = @{Name='Sessionize';Expression={($_.links | Where-Object {$_.linktype -eq 'Sessionize'}).url}} $Blog = @{Name='Blog';Expression={($_.links | Where-Object {$_.linktype -eq 'Blog'}).url}} $Facebook = @{Name='Facebook';Expression={($_.links | Where-Object {$_.linktype -eq 'Facebook'}).url}} $Twitter = @{Name='Twitter';Expression={($_.links | Where-Object {$_.linktype -eq 'Twitter'}).url}} $CompanyWebsite = @{Name='Company Website';Expression={($_.links | Where-Object {$_.linktype -eq 'Company Website'}).url}} $Other = @{Name='Other';Expression={($_.links | Where-Object {$_.linktype -eq 'Other'}) | ForEach-Object { $_ }}} $SessionNames = @{ Name='SessionNames';Expression={$_.sessions | ForEach-Object { $id = $_.id [PSCustomObject]@{ Name = $_.name Room = ($AllSessions.Sessions|Where-Object{$_.id -eq $id}).Room } } } } $Data = Invoke-RestMethod -Uri $uri $Data = $Data|Select-Object *,$CompanyName,$Isremote,$LinkedIn,$Sessionize,$Blog,$Facebook,$Twitter,$CompanyWebsite,$Other,$sessionNames if (-not $Data) { Write-Warning 'No data returned from Sessionize API' return } } process { if($search) { $Data = $Data | Where-Object { $_.fullName -like "*$search*" } } if($remote) { $Data = $Data | Where-Object { $_.IsRemote -eq 'Remote' } } if($full) { $Data | Select-Object -ExcludeProperty id,isTopSpeaker,questionAnswers,categories,links } else { $Data | Select-Object fullName, companyName,tagLine, $SessionNames } } end { } } #EndRegion './Public/Get-SQLBitsTDSpeakers.ps1' 107 #Region './Public/Get-SQLBitsTrainingDay.ps1' -1 function Get-SQLBitsTrainingDay { <# .SYNOPSIS Gets the SQLBits Training Day Schedule from the Sessionize API .DESCRIPTION Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to json, excel, psobject, html or csv .PARAMETER Output The type of output required. Valid values are json, excel, psobject, html or csv .PARAMETER fileDirectory The directory to save the output file to - defaults to Env:Temp .PARAMETER Show Whether to open the output file after it has been created .EXAMPLE Get-SQLBitsTrainingDay -Output Excel -Show Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to excel, opens the file and saves it to the default temp directory .EXAMPLE Get-SQLBitsTrainingDay -Output Raw Gets the SQLBits Training Day Schedule from the Sessionize API and outputs as json on the screen .EXAMPLE Get-SQLBitsTrainingDay -Output csv -Show Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to csv, opens the file and saves it to the default temp directory .EXAMPLE Get-SQLBitsTrainingDay -Output object Gets the SQLBits Training Day Schedule from the Sessionize API and outputs as a psobject on the screen .EXAMPLE Get-SQLBitsTrainingDay -Output html -Show Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to html, opens the file and saves it to the default temp directory .NOTES Author: Rob Sewell December 2022 #> [CmdletBinding()] param ( [Parameter()] [ValidateSet('raw', 'excel', 'object', 'csv', 'html')] $Output = 'excel', [string] $fileDirectory = $env:TEMP, [switch] $Show ) $BaseUri = 'https://sessionize.com/api/v2/sydgl43f/view' $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss' #TODO Add other options $filter = 'Schedule' switch ($filter) { # 'All' { # $uri = '{0}/All' -f $BaseUri # } 'Schedule' { $uri = '{0}/All' -f $BaseUri } 'Sessions' { $uri = '{0}/sessions' -f $BaseUri } 'Speakers' { $uri = '{0}/speakers' -f $BaseUri } Default { $uri = '{0}/All' -f $BaseUri } } $Data = Invoke-RestMethod -Uri $uri if(-not $Data){ Write-Warning 'No data returned from Sessionize API' return } $rooms = ($data.rooms | Sort-Object name) if(-not $rooms){ Write-Warning 'No rooms returned from Sessionize API' return } $Speakers = $data.speakers if(-not $Speakers){ Write-Warning 'No Speakers returned from Sessionize API' return } # Thank you Shane - https://nocolumnname.blog/2020/10/29/pivot-in-powershell/ $props = @( @{ Name = 'Day' ; Expression = { $Psitem.Group[0].startsAt.DayOfWeek } } @{ Name = 'Date' ; Expression = { $Psitem.Group[0].startsAt.tolongdatestring() } } @{ Name = 'StartTime' ; Expression = { $Psitem.Group[0].startsAt.ToShortTimeString() } } @{ Name = 'EndTime' ; Expression = { $Psitem.Group[0].EndsAt.ToShortTimeString() } } foreach ($room in $rooms) { $rn = $room.Name @{ Name = $rn Expression = { '{0} {1}' -f @( ($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).title, (($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).Speakers.ForEach{ $Speakers | Where-Object id -EQ $_ }.FullName -join ' ') ) }.GetNewClosure() } } ) if($IsCoreCLR){ $rawsessions = $Data.sessions } else { $rawsessions = $Data.sessions | Select -Property id, title,@{Name = 'startsAt';expression = {[datetime]$_.startsAt}} , @{Name = 'endsAt';expression = {[datetime]$_.endsAt}}, roomID, speakers } $sessions = $rawsessions | Group-Object -Property StartsAt | Select-Object $props switch ($output) { 'Raw' { $Data } 'object' { $sessions } 'Excel' { if (Get-Module -Name ImportExcel -ErrorAction SilentlyContinue -ListAvailable) { if ($filter -eq 'Schedule') { $FilePath = '{0}\SQLBitsSchedule{1}_{2}.xlsx' -f $fileDirectory, $filter, $Date $sessions | Group-Object Day | ForEach-Object { $worksheetName = $_.Name $excel = $_.Group | Export-Excel -Path $FilePath -WorksheetName $worksheetName -AutoSize -FreezePane 2, 5 -PassThru 1..15 | ForEach-Object { Set-ExcelRow -ExcelPackage $excel -WorksheetName $worksheetName -Row $_ -Height 30 -WrapText } $rulesparam = @{ Address = $excel.Workbook.Worksheets[$WorkSheetName].Dimension.Address WorkSheet = $excel.Workbook.Worksheets[$WorkSheetName] } Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Coffee Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Quick Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Keynote",$E1)))' -BackgroundColor BlueViolet -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Lunch",$E1)))' -BackgroundColor Chocolate -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Prize",$E1)))' -BackgroundColor PowderBlue -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Free Time",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue Add-ConditionalFormatting @rulesparam -RuleType 'Expression' -ConditionValue 'NOT(ISERROR(FIND("Registration",$E1)))' -BackgroundColor DarkOrange -ForegroundColor White -StopIfTrue Close-ExcelPackage $excel } if ($Show) { Invoke-Item $filepath }else { Write-Output "Excel file saved to $FilePath" } } } else { Write-Warning 'You need to install ImportExcel to use this option but here is a CSV instead' $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation if ($Show) { Invoke-Item $filepath }else { Write-Output "Csv file saved to $FilePath" } } } 'CSv' { $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation if ($Show) { Invoke-Item $filepath }else { Write-Output "Csv file saved to $FilePath" } } 'html' { $FilePath = '{0}\SQLBits_{1}_{2}.html' -f $fileDirectory, $filter, $Date $sessions | ConvertTo-Html | out-file $FilePath if ($Show) { Invoke-Item $filepath }else { Write-Output "Html file saved to $FilePath" } } Default { } } } #EndRegion './Public/Get-SQLBitsTrainingDay.ps1' 203 |