AQTools.psm1
|
<#
.NOTES -------------------------------------------------------------------------------- Code generated by: SAPIEN Technologies, Inc., PowerShell Studio 2019 v5.6.167 Generated on: 9/28/2019 4:34 PM Generated by: Craig Moore (McMurdo Sr Systems Administrator) Organization: United States Antarctic Program -------------------------------------------------------------------------------- .DESCRIPTION Script generated by PowerShell Studio 2019 #> <# =========================================================================== Created on: 9/28/2019 1:27 PM Created by: Craig Moore (McMurdo Sr Systems Administrator) Organization: United States Antarctic Program Filename: AQTools.psm1 ------------------------------------------------------------------------- Module Name: AQTools =========================================================================== #> <# .EXTERNALHELP AQTools.psm1-Help.xml #> function Get-MCMListDocumentation { [CmdletBinding(DefaultParameterSetName = 'None')] [OutputType([System.IO.File], ParameterSetName = 'None')] [OutputType([System.IO.File], ParameterSetName = 'Logding')] [OutputType([System.IO.File], ParameterSetName = 'Operations')] [OutputType([System.IO.File], ParameterSetName = 'WorkPhone')] [OutputType([System.IO.File], ParameterSetName = 'Pager')] param ( [Parameter(Mandatory = $true)] [ValidateScript({ if ($_ -notmatch "(\.xlsx)") { throw "The file extension must be .xlsx" } return $true })] [Alias('SaveTo')] [System.IO.FileInfo] $Path, [switch] $Show, [Parameter(ParameterSetName = 'Logding')] [switch] $Logding, [Parameter(ParameterSetName = 'Operations')] [switch] $Operations, [Parameter(ParameterSetName = 'WorkPhone')] [switch] $WorkPhone, [Parameter(ParameterSetName = 'Pager')] [switch] $Pager ) BEGIN { # Time $Date = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' # Cleanup old documentents with the same name. $TestPath = Get-Item -Path $Path -ErrorAction SilentlyContinue if ($null -ne $TestPath) { Write-Verbose -Message "Removing $Path" Export-Excel -Path $Path -KillExcel Remove-Item $Path } Write-Verbose -Message "Documenting all residents in McMurdo from Active Directory" } PROCESS { switch ($PSCmdlet.ParameterSetName) { "Logding" { $Residents = Get-ADUser -Filter "(extensionAttribute10 -like '*')" -Properties * $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ Name = $_.Name.ToUpper() Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Phone = $_.extensionAttribute12 DaySleeper = $_.employeeType Title = $_.title } } # Master List $Dorm = "Lodging" $Document = $Export | Select-Object Name, Dorm, Room, Phone, DaySleeper, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "All McMurdo Lodging- Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $Document.Workbook.Worksheets[$Dorm].Row(1).Merged = $true $Document.Workbook.Worksheets[$Dorm].Row(1).Height = 25.0 # Colunm 1 (Name) $Document.Workbook.Worksheets[$Dorm].Column(1).style.font.bold = $true $Document.Workbook.Worksheets[$Dorm].Column(1).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(1).width = 40.0 # Colunm 2 (Dorm) $Document.Workbook.Worksheets[$Dorm].Column(2).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(2).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Room) $Document.Workbook.Worksheets[$Dorm].Column(3).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(3).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(3).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Phone) $Document.Workbook.Worksheets[$Dorm].Column(4).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(4).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(4).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(4).Style.HorizontalAlignment = "Center" # Colunm 5 (DaySleeper) $Document.Workbook.Worksheets[$Dorm].Column(5).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(5).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(5).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(5).Style.HorizontalAlignment = "Center" # Colunm 6 (Title) $Document.Workbook.Worksheets[$Dorm].Column(6).Style.VerticalAlignment = "Top" # Save formatting $Document.Save() $Document.Dispose() # Each Dorm $Dorms = "155", "166", "188", "201", "202", "203A", "203B", "203C", "206", "207", "208", "209", "210", "211" # For empty dorms $Empty = "THERE ARE NO RESIDENTS ASSIGNED TO THIS DORM" foreach ($Dorm in $Dorms) { # Gather basic information Write-Verbose -Message "Dorm $Dorm" $Residents = Get-ADUser -Filter "(extensionAttribute10 -like '$Dorm')" -Properties * if ($null -eq $Residents) { Write-Verbose -Message " Dorm is empty" $Empty | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Dark3 -Title "Dorm $Dorm - Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue } else { # Gather basic information $Date = Get-Date Write-Verbose -Message " Documenting Dorm" $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ Name = $_.Name.ToUpper() Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Phone = $_.extensionAttribute12 DaySleeper = $_.employeeType Title = $_.title } } $Document = $Export | Select-Object Name, Dorm, Room, Phone, DaySleeper, Title | Sort-Object Room | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "Dorm $Dorm - Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $Document.Workbook.Worksheets[$Dorm].Row(1).Merged = $true $Document.Workbook.Worksheets[$Dorm].Row(1).Height = 25.0 # Colunm 1 (Name) $Document.Workbook.Worksheets[$Dorm].Column(1).style.font.bold = $true $Document.Workbook.Worksheets[$Dorm].Column(1).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(1).width = 40.0 # Colunm 2 (Dorm) $Document.Workbook.Worksheets[$Dorm].Column(2).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(2).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Room) $Document.Workbook.Worksheets[$Dorm].Column(3).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(3).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(3).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Phone) $Document.Workbook.Worksheets[$Dorm].Column(4).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(4).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(4).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(4).Style.HorizontalAlignment = "Center" # Colunm 5 (DaySleeper) $Document.Workbook.Worksheets[$Dorm].Column(5).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(5).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(5).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(5).Style.HorizontalAlignment = "Center" # Colunm 6 (Title) $Document.Workbook.Worksheets[$Dorm].Column(6).Style.VerticalAlignment = "Top" # Save formatting $Document.Save() $Document.Dispose() } } } "Operations" { $Residents = Get-ADUser -Filter { extensionAttribute10 -like '*' -and (pager -like '*' -or facsimileTelephoneNumber -like '4*') } -Properties * Write-Verbose -Message "Documenting all residents in McMurdo from Active Directory" $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ Name = $_.Name.ToUpper() Title = $_.title Pager = $_.pager WorkPhone = $_.facsimileTelephoneNumber } } # Master List $Dorm = "Operations" $Document = $Export | Select-Object Name, Pager, WorkPhone, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "All McMurdo Operations - Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $Document.Workbook.Worksheets[$Dorm].Row(1).Merged = $true $Document.Workbook.Worksheets[$Dorm].Row(1).Height = 25.0 # Colunm 1 (Name) $Document.Workbook.Worksheets[$Dorm].Column(1).style.font.bold = $true $Document.Workbook.Worksheets[$Dorm].Column(1).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(1).width = 40.0 # Colunm 2 (Pager) $Document.Workbook.Worksheets[$Dorm].Column(2).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(2).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(2).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (WorkPhone) $Document.Workbook.Worksheets[$Dorm].Column(3).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(3).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(3).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Title) $Document.Workbook.Worksheets[$Dorm].Column(4).Style.VerticalAlignment = "Top" # Save formatting $Document.Save() $Document.Dispose() } "WorkPhone" { $Residents = Get-ADUser -Filter { extensionAttribute10 -like '*' -and (pager -like '*' -or facsimileTelephoneNumber -like '4*') } -Properties * Write-Verbose -Message "Documenting all residents in McMurdo from Active Directory" $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ Name = $_.Name.ToUpper() Title = $_.title WorkPhone = $_.facsimileTelephoneNumber } } # Master List $Dorm = "WorkPhone" $Document = $Export | Select-Object Name, WorkPhone, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "All McMurdo Work Phones - Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $Document.Workbook.Worksheets[$Dorm].Row(1).Merged = $true $Document.Workbook.Worksheets[$Dorm].Row(1).Height = 25.0 # Colunm 1 (Name) $Document.Workbook.Worksheets[$Dorm].Column(1).style.font.bold = $true $Document.Workbook.Worksheets[$Dorm].Column(1).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(1).width = 40.0 # Colunm 2 (WorkPhone) $Document.Workbook.Worksheets[$Dorm].Column(2).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(2).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(2).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Title) $Document.Workbook.Worksheets[$Dorm].Column(3).Style.VerticalAlignment = "Top" # Save formatting $Document.Save() $Document.Dispose() } "Pager" { $Residents = Get-ADUser -Filter { extensionAttribute10 -like '*' -and (pager -like '*' -or facsimileTelephoneNumber -like '4*') } -Properties * Write-Verbose -Message "Documenting all residents in McMurdo from Active Directory" $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ Name = $_.Name.ToUpper() Title = $_.title Pager = $_.pager } } # Master List $Dorm = "Pager" $Document = $Export | Select-Object Name, Pager, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "All McMurdo Pagers - Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $Document.Workbook.Worksheets[$Dorm].Row(1).Merged = $true $Document.Workbook.Worksheets[$Dorm].Row(1).Height = 25.0 # Colunm 1 (Name) $Document.Workbook.Worksheets[$Dorm].Column(1).style.font.bold = $true $Document.Workbook.Worksheets[$Dorm].Column(1).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(1).width = 40.0 # Colunm 2 (Pager) $Document.Workbook.Worksheets[$Dorm].Column(2).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(2).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(2).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(2).Style.HorizontalAlignment = "Center" # Colunm 4 (Title) $Document.Workbook.Worksheets[$Dorm].Column(3).Style.VerticalAlignment = "Top" # Save formatting $Document.Save() $Document.Dispose() } default { # Master List $Residents = Get-ADUser -Filter "(extensionAttribute10 -like '*')" -Properties * $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ Name = $_.Name.ToUpper() Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Phone = $_.extensionAttribute12 DaySleeper = $_.employeeType Pager = $_.pager WorkPhone = $_.facsimileTelephoneNumber Title = $_.title } } $Dorm = "All" $Document = $Export | Select-Object Name, Dorm, Room, Phone, DaySleeper, Pager, WorkPhone, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM_$Dorm" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "All McMurdo - Last Updated $Date" -TitleBold -TitleSize 20 -WorksheetName $Dorm -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $Document.Workbook.Worksheets[$Dorm].Row(1).Merged = $true $Document.Workbook.Worksheets[$Dorm].Row(1).Height = 25.0 # Colunm 1 (Name) $Document.Workbook.Worksheets[$Dorm].Column(1).style.font.bold = $true $Document.Workbook.Worksheets[$Dorm].Column(1).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(1).width = 40.0 # Colunm 2 (Dorm) $Document.Workbook.Worksheets[$Dorm].Column(2).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(2).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Room) $Document.Workbook.Worksheets[$Dorm].Column(3).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(3).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(3).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Phone) $Document.Workbook.Worksheets[$Dorm].Column(4).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(4).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(4).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(4).Style.HorizontalAlignment = "Center" # Colunm 5 (DaySleeper) $Document.Workbook.Worksheets[$Dorm].Column(5).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(5).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(5).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(5).Style.HorizontalAlignment = "Center" # Colunm 6 (Pager) $Document.Workbook.Worksheets[$Dorm].Column(6).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(6).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(6).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(6).Style.HorizontalAlignment = "Center" # Colunm 7 (WorkPhone) $Document.Workbook.Worksheets[$Dorm].Column(7).width = 14.0 $Document.Workbook.Worksheets[$Dorm].Column(7).Style.wraptext = $true $Document.Workbook.Worksheets[$Dorm].Column(7).Style.VerticalAlignment = "Top" $Document.Workbook.Worksheets[$Dorm].Column(7).Style.HorizontalAlignment = "Center" # Colunm 8 (Title) $Document.Workbook.Worksheets[$Dorm].Column(8).Style.VerticalAlignment = "Top" # Save formatting $Document.Save() $Document.Dispose() } } } END { # Open spredsheet when done if ($Show) { Write-Verbose "Opening document at $Path" Invoke-Item $Path } } } <# .EXTERNALHELP AQTools.psm1-Help.xml .PARAMETER Path The save location to the .xlsx document. Later to be used by Set-FutureRoom function #> function Get-FutureList { [CmdletBinding(DefaultParameterSetName = 'None')] [OutputType([System.IO.File], ParameterSetName = 'None')] [OutputType([System.IO.File], ParameterSetName = 'Logding')] [OutputType([System.IO.File], ParameterSetName = 'Operations')] [OutputType([System.IO.File], ParameterSetName = 'WorkPhone')] [OutputType([System.IO.File], ParameterSetName = 'Pager')] param ( [Parameter(Mandatory = $true, HelpMessage = 'The save location to the .xlsx document. Later to be used by Set-FutureRoom function')] [ValidateScript({ if ($_ -notmatch "(\.xlsx)") { throw "The file extension must be .xlsx" } return $true })] [System.IO.FileInfo] $Path, [switch] $Show, [Parameter(Mandatory = $true)] [array] $OU, [Parameter(ParameterSetName = 'Logding')] [switch] $Logding, [Parameter(ParameterSetName = 'Operations')] [switch] $Operations, [Parameter(ParameterSetName = 'WorkPhone')] [switch] $WorkPhone, [Parameter(ParameterSetName = 'Pager')] [switch] $Pager ) BEGIN { # Remove old file $TestPath = Get-Item -Path $Path -ErrorAction SilentlyContinue if ($null -ne $TestPath) { Write-Verbose -Message "Removing $Path" Export-Excel -Path $Path -KillExcel Remove-Item $Path } $Date = Get-Date -Format "yyyy-mm-dd HH:mm:ss" $Residents = Foreach ($OUs in $OU) { Write-Verbose -Message "Processing $OUs" Get-ADUser -Filter { extensionAttribute4 -like "*" } -Properties * -SearchBase $OUs } } PROCESS { switch ($PSCmdlet.ParameterSetName) { "Logding" { $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ SamAccountName = $_.SamAccountName Name = $_.Name.ToUpper() Flight = $_.extensionAttribute4.ToUpper() Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Phone = $_.extensionAttribute12 DaySleeper = $_.employeeType Title = $_.title } } Write-Verbose -Message "Creating $Path" # Create Excel document $Excel = $Export | Select-Object SamAccountName, Name, Flight, Dorm, Room, Phone, DaySleeper, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "FromAD" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium20 -Title "Created $Date" -TitleBold -TitleSize 20 -WorksheetName "From_AD" -KillExcel -PassThru # Format the sheet # Row 1 (Title) $Excel.Workbook.Worksheets["From_AD"].Row(1).Merged = $true $Excel.Workbook.Worksheets["From_AD"].Row(1).Height = 25.0 # Column 1 (SamAccountName) $Excel.Workbook.Worksheets["From_AD"].Column(1).Hidden = $true # Column 2 (Name) $Excel.Workbook.Worksheets["From_AD"].Column(2).style.font.bold = $true # Column 3 (Flight) $Excel.Workbook.Worksheets["From_AD"].Column(3).Style.HorizontalAlignment = "Center" # Column 4 (Dorm) $Excel.Workbook.Worksheets["From_AD"].Column(4).Style.HorizontalAlignment = "Center" # Column 5 (Room) $Excel.Workbook.Worksheets["From_AD"].Column(5).Style.HorizontalAlignment = "Center" # Column 6 (Phone) $Excel.Workbook.Worksheets["From_AD"].Column(6).Style.HorizontalAlignment = "Center" # Column 7 (DaySleeper) $Excel.Workbook.Worksheets["From_AD"].Column(7).Style.HorizontalAlignment = "Center" # Column 8 (Title) $Excel.Workbook.Worksheets["From_AD"].Column(8).Style.HorizontalAlignment = "Left" # Row 2 $Excel.Workbook.Worksheets["From_AD"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $Excel.Save() $Excel.Dispose() } "Operations" { $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ SamAccountName = $_.SamAccountName Name = $_.Name.ToUpper() Flight = $_.extensionAttribute4.ToUpper() Pager = $_.pager WorkPhone = $_.facsimileTelephoneNumber Title = $_.title } } Write-Verbose -Message "Creating $Path" # Create Excel document $Excel = $Export | Select-Object SamAccountName, Name, Flight, Pager, WorkPhone, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "FromAD" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium20 -Title "Created $Date" -TitleBold -TitleSize 20 -WorksheetName "From_AD" -KillExcel -PassThru # Format the sheet # Row 1 (Title) $Excel.Workbook.Worksheets["From_AD"].Row(1).Merged = $true $Excel.Workbook.Worksheets["From_AD"].Row(1).Height = 25.0 # Column 1 (SamAccountName) $Excel.Workbook.Worksheets["From_AD"].Column(1).Hidden = $true # Column 2 (Name) $Excel.Workbook.Worksheets["From_AD"].Column(2).style.font.bold = $true # Column 3 (Flight) $Excel.Workbook.Worksheets["From_AD"].Column(3).Style.HorizontalAlignment = "Center" # Column 4 (Pager) $Excel.Workbook.Worksheets["From_AD"].Column(4).Style.HorizontalAlignment = "Center" # Column 5 (WorkPhone) $Excel.Workbook.Worksheets["From_AD"].Column(5).Style.HorizontalAlignment = "Center" # Column 6 (Title) $Excel.Workbook.Worksheets["From_AD"].Column(6).Style.HorizontalAlignment = "Left" # Row 2 $Excel.Workbook.Worksheets["From_AD"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $Excel.Save() $Excel.Dispose() } "WorkPhone" { $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ SamAccountName = $_.SamAccountName Name = $_.Name.ToUpper() Flight = $_.extensionAttribute4.ToUpper() WorkPhone = $_.facsimileTelephoneNumber Title = $_.title } } Write-Verbose -Message "Creating $Path" # Create Excel document $Excel = $Export | Select-Object SamAccountName, Name, Flight, WorkPhone, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "FromAD" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium20 -Title "Created $Date" -TitleBold -TitleSize 20 -WorksheetName "From_AD" -KillExcel -PassThru # Format the sheet # Row 1 (Title) $Excel.Workbook.Worksheets["From_AD"].Row(1).Merged = $true $Excel.Workbook.Worksheets["From_AD"].Row(1).Height = 25.0 # Column 1 (SamAccountName) $Excel.Workbook.Worksheets["From_AD"].Column(1).Hidden = $true # Column 2 (Name) $Excel.Workbook.Worksheets["From_AD"].Column(2).style.font.bold = $true # Column 3 (Flight) $Excel.Workbook.Worksheets["From_AD"].Column(3).Style.HorizontalAlignment = "Center" # Column 4 (WorkPhone) $Excel.Workbook.Worksheets["From_AD"].Column(4).Style.HorizontalAlignment = "Center" # Column 5 (Title) $Excel.Workbook.Worksheets["From_AD"].Column(5).Style.HorizontalAlignment = "Left" # Row 2 $Excel.Workbook.Worksheets["From_AD"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $Excel.Save() $Excel.Dispose() } "Pager" { $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ SamAccountName = $_.SamAccountName Name = $_.Name.ToUpper() Flight = $_.extensionAttribute4.ToUpper() Pager = $_.pager Title = $_.title } } Write-Verbose -Message "Creating $Path" # Create Excel document $Excel = $Export | Select-Object SamAccountName, Name, Flight, Pager, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "FromAD" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium20 -Title "Created $Date" -TitleBold -TitleSize 20 -WorksheetName "From_AD" -KillExcel -PassThru # Format the sheet # Row 1 (Title) $Excel.Workbook.Worksheets["From_AD"].Row(1).Merged = $true $Excel.Workbook.Worksheets["From_AD"].Row(1).Height = 25.0 # Column 1 (SamAccountName) $Excel.Workbook.Worksheets["From_AD"].Column(1).Hidden = $true # Column 2 (Name) $Excel.Workbook.Worksheets["From_AD"].Column(2).style.font.bold = $true # Column 3 (Flight) $Excel.Workbook.Worksheets["From_AD"].Column(3).Style.HorizontalAlignment = "Center" # Column 4 (Pager) $Excel.Workbook.Worksheets["From_AD"].Column(4).Style.HorizontalAlignment = "Center" # Column 5 (Title) $Excel.Workbook.Worksheets["From_AD"].Column(5).Style.HorizontalAlignment = "Left" # Row 2 $Excel.Workbook.Worksheets["From_AD"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $Excel.Save() $Excel.Dispose() } default { $Export = $Residents | ForEach-Object { New-Object System.Management.Automation.PSObject -Property @{ SamAccountName = $_.SamAccountName Name = $_.Name.ToUpper() Flight = $_.extensionAttribute4.ToUpper() Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Phone = $_.extensionAttribute12 DaySleeper = $_.employeeType Title = $_.title Pager = $_.pager WorkPhone = $_.facsimileTelephoneNumber } } Write-Verbose -Message "Creating $Path" # Create Excel document $Excel = $Export | Select-Object SamAccountName, Name, Flight, Dorm, Room, Phone, DaySleeper, Pager, WorkPhone, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "FromAD" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium20 -Title "Created $Date" -TitleBold -TitleSize 20 -WorksheetName "From_AD" -KillExcel -PassThru # Format the sheet # Row 1 (Title) $Excel.Workbook.Worksheets["From_AD"].Row(1).Merged = $true $Excel.Workbook.Worksheets["From_AD"].Row(1).Height = 25.0 # Column 1 (SamAccountName) $Excel.Workbook.Worksheets["From_AD"].Column(1).Hidden = $true # Column 2 (Name) $Excel.Workbook.Worksheets["From_AD"].Column(2).style.font.bold = $true # Column 3 (Flight) $Excel.Workbook.Worksheets["From_AD"].Column(3).Style.HorizontalAlignment = "Center" # Column 4 (Dorm) $Excel.Workbook.Worksheets["From_AD"].Column(4).Style.HorizontalAlignment = "Center" # Column 5 (Room) $Excel.Workbook.Worksheets["From_AD"].Column(5).Style.HorizontalAlignment = "Center" # Column 6 (Phone) $Excel.Workbook.Worksheets["From_AD"].Column(6).Style.HorizontalAlignment = "Center" # Column 7 (DaySleeper) $Excel.Workbook.Worksheets["From_AD"].Column(7).Style.HorizontalAlignment = "Center" # Column 8 (Pager) $Excel.Workbook.Worksheets["From_AD"].Column(8).Style.HorizontalAlignment = "Center" # Column 9 (WorkPhone) $Excel.Workbook.Worksheets["From_AD"].Column(9).Style.HorizontalAlignment = "Center" # Column 10 (Title) $Excel.Workbook.Worksheets["From_AD"].Column(10).Style.HorizontalAlignment = "Left" # Row 2 $Excel.Workbook.Worksheets["From_AD"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $Excel.Save() $Excel.Dispose() } } } END { # Open spredsheet when done if ($Show) { Write-Verbose -Message "Open $Path" Invoke-Item $Path } } } <# .EXTERNALHELP AQTools.psm1-Help.xml #> function Set-FutureList { [CmdletBinding(DefaultParameterSetName = 'None')] param ( [Parameter(Mandatory = $true, HelpMessage = 'Path to the Excel document created by Get-FutureRoom.')] [ValidateScript({ if ($_ -notmatch "(\.xlsx)") { throw "The file extension must be .xlsx" } return $true })] [System.IO.FileInfo] $Path, [Parameter(ParameterSetName = 'Logding')] [switch] $Logding, [Parameter(ParameterSetName = 'Operations')] [switch] $Operations, [Parameter(ParameterSetName = 'WorkPhone')] [switch] $WorkPhone, [Parameter(ParameterSetName = 'Pager')] [switch] $Pager, [switch] $Force ) switch ($PSCmdlet.ParameterSetName) { "Operations" { Import-Excel -Path $Path -StartRow 2 | ForEach-Object { $User = $_.SamAccountName $Display = $_.Name Write-Verbose -Message "$($Display)" # Remove Operations attributes cleared on Excel Document if ([string]::IsNullOrEmpty($_.Pager)) { Set-ADUser -Identity $User -Clear "Pager" Write-Verbose -Message " Clear Pager" } if ([string]::IsNullOrEmpty($_.WorkPhone)) { Set-ADUser -Identity $User -Clear "facsimileTelephoneNumber" Write-Verbose -Message " Clear WorkPhone" } # Adding Operations attributes created in the Excel document if (-not ([string]::IsNullOrEmpty($_.Pager))) { Set-ADUser -Identity $User -Replace @{ pager = "$($_.Pager)" } Write-Verbose -Message " Add Pager" } if (-not ([string]::IsNullOrEmpty($_.WorkPhone))) { Set-ADUser -Identity $User -Replace @{ facsimileTelephoneNumber = "$($_.WorkPhone)" } Write-Verbose -Message " Add WorkPhone" } } } "WorkPhone" { Import-Excel -Path $Path -StartRow 2 | ForEach-Object { $User = $_.SamAccountName $Display = $_.Name Write-Verbose -Message "$($Display)" # Remove Operations attributes cleared on Excel Document if ([string]::IsNullOrEmpty($_.WorkPhone)) { Set-ADUser -Identity $User -Clear "facsimileTelephoneNumber" Write-Verbose -Message " Clear WorkPhone" } # Adding Operations attributes created in the Excel document if (-not ([string]::IsNullOrEmpty($_.WorkPhone))) { Set-ADUser -Identity $User -Replace @{ facsimileTelephoneNumber = "$($_.WorkPhone)" } Write-Verbose -Message " Add WorkPhone" } } } "Pager" { Import-Excel -Path $Path -StartRow 2 | ForEach-Object { $User = $_.SamAccountName $Display = $_.Name Write-Verbose -Message "$($Display)" # Remove Operations attributes cleared on Excel Document if ([string]::IsNullOrEmpty($_.Pager)) { Set-ADUser -Identity $User -Clear "Pager" Write-Verbose -Message " Clear Pager" } # Adding Operations attributes created in the Excel document if (-not ([string]::IsNullOrEmpty($_.Pager))) { Set-ADUser -Identity $User -Replace @{ pager = "$($_.Pager)" } Write-Verbose -Message " Add Pager" } } } "Logding" { Import-Excel -Path $Path -StartRow 2 | ForEach-Object { $User = $_.SamAccountName $Display = $_.Name Write-Verbose -Message "$($Display)" # Remove Lodging attributes cleared on Excel Document if ([string]::IsNullOrEmpty($_.Dorm)) { Set-ADUser -Identity $User -Clear "extensionAttribute10" Write-Verbose -Message " Clear Dorm" } if ([string]::IsNullOrEmpty($_.Room)) { Set-ADUser -Identity $User -Clear "extensionAttribute11" Write-Verbose -Message " Clear Room" } if ([string]::IsNullOrEmpty($_.Phone)) { Set-ADUser -Identity $User -Clear "extensionAttribute12" Write-Verbose -Message " Clear Phone" } if ([string]::IsNullOrEmpty($_.DaySleeper)) { Set-ADUser -Identity $User -Clear "employeeType" Write-Verbose -Message " Clear DaySleeper" } # Adding Lodging attributes created in the Excel document if (-not ([string]::IsNullOrEmpty($_.Dorm))) { Set-ADUser -Identity $User -Replace @{ extensionAttribute10 = "$($_.Dorm)" } Write-Verbose -Message " Add Dorm" } if (-not ([string]::IsNullOrEmpty($_.Room))) { Set-ADUser -Identity $User -Replace @{ extensionAttribute11 = "$($_.Room)" } Write-Verbose -Message " Add Room" } if (-not ([string]::IsNullOrEmpty($_.Phone))) { Set-ADUser -Identity $User -Replace @{ extensionAttribute12 = "$($_.Phone)" } Write-Verbose -Message " Add Phone" } if (-not ([string]::IsNullOrEmpty($_.DaySleeper))) { Set-ADUser -Identity $User -Replace @{ employeeType = "$($_.DaySleeper)" } Write-Verbose -Message " Add DaySleeper" } } } default { Import-Excel -Path $Path -StartRow 2 | ForEach-Object { $User = $_.SamAccountName $Display = $_.Name Write-Verbose -Message "$($Display)" # Remove attributes cleared on Excel Document if ([string]::IsNullOrEmpty($_.Dorm)) { Set-ADUser -Identity $User -Clear "extensionAttribute10" Write-Verbose -Message " Clear Dorm" } if ([string]::IsNullOrEmpty($_.Room)) { Set-ADUser -Identity $User -Clear "extensionAttribute11" Write-Verbose -Message " Clear Room" } if ([string]::IsNullOrEmpty($_.Phone)) { Set-ADUser -Identity $User -Clear "extensionAttribute12" Write-Verbose -Message " Clear Phone" } if ([string]::IsNullOrEmpty($_.DaySleeper)) { Set-ADUser -Identity $User -Clear "employeeType" Write-Verbose -Message " Clear DaySleeper" } if ([string]::IsNullOrEmpty($_.Pager)) { Set-ADUser -Identity $User -Clear "Pager" Write-Verbose -Message " Clear Pager" } if ([string]::IsNullOrEmpty($_.WorkPhone)) { Set-ADUser -Identity $User -Clear "facsimileTelephoneNumber" Write-Verbose -Message " Clear WorkPhone" } # Adding attributes created in the Excel document if (-not ([string]::IsNullOrEmpty($_.Dorm))) { Set-ADUser -Identity $User -Replace @{ extensionAttribute10 = "$($_.Dorm)" } Write-Verbose -Message " Add Dorm" } if (-not ([string]::IsNullOrEmpty($_.Room))) { Set-ADUser -Identity $User -Replace @{ extensionAttribute11 = "$($_.Room)" } Write-Verbose -Message " Add Room" } if (-not ([string]::IsNullOrEmpty($_.Phone))) { Set-ADUser -Identity $User -Replace @{ extensionAttribute12 = "$($_.Phone)" } Write-Verbose -Message " Add Phone" } if (-not ([string]::IsNullOrEmpty($_.DaySleeper))) { Set-ADUser -Identity $User -Replace @{ employeeType = "$($_.DaySleeper)" } Write-Verbose -Message " Add DaySleeper" } if (-not ([string]::IsNullOrEmpty($_.Pager))) { Set-ADUser -Identity $User -Replace @{ pager = "$($_.Pager)" } Write-Verbose -Message " Add Pager" } if (-not ([string]::IsNullOrEmpty($_.WorkPhone))) { Set-ADUser -Identity $User -Replace @{ facsimileTelephoneNumber = "$($_.WorkPhone)" } Write-Verbose -Message " Add WorkPhone" } } } } } |