MCMItPhoneLists.psm1
<#
.NOTES -------------------------------------------------------------------------------- Code generated by: SAPIEN Technologies, Inc., PowerShell Studio 2019 v5.6.167 Generated on: 8/24/2019 5:13 PM Generated by: Craig Moore (McMurdo Sr Systems Administrator) Organization: United States Antarctic Program -------------------------------------------------------------------------------- .DESCRIPTION Script generated by PowerShell Studio 2019 #> <# =========================================================================== Created on: 8/24/2019 3:36 PM Created by: Craig Moore (McMurdo Sr Systems Administrator) Organization: United States Antarctic Program Filename: MCMItPhoneLists.psm1 ------------------------------------------------------------------------- Module Name: MCMItPhoneLists =========================================================================== #> <# .EXTERNALHELP MCMItPhoneLists.psm1-Help.xml #> function Get-ItPhoneList { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateScript({ if ($_ -notmatch "(\.xlsx)") { throw "The file extension must be .xlsx" } return $true })] [System.IO.FileInfo] $Path, [switch] $Show ) 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" } PROCESS { Write-Verbose -Message 'Gathering McMurdo Station' $MCM = Get-ADUser -Filter "(extensionAttribute10 -like '*') -AND (department -like 'IT*')" -Properties * | ForEach-Object { New-Object psobject -Property @{ Name = $_.Name "Work Phone" = $_.facsimileTelephoneNumber Pager = $_.pager "Dorm Phone" = $_.extensionAttribute12 Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Title = $_.title.ToUpper() } } Write-Verbose -Message 'Gathering Denver Station' $DEN = Get-ADUser -Filter "(department -like 'IT*')" -SearchBase "OU=Denver-Contractor,OU=Users,OU=OU-DENVER,DC=usap,DC=gov" -Properties * | ForEach-Object { New-Object psobject -Property @{ Name = $_.Name "Work Phone" = $_.telephoneNumber Title = $_.title.ToUpper() "E-Mail" = $_.mail } } Write-Verbose -Message 'Gathering Christchurch Station' $CHC = Get-ADUser -Filter "(department -like 'IT*')" -SearchBase "OU=Christchurch-Contractor,OU=Users,OU=OU-CHRISTCHURCH,DC=usap,DC=gov" -Properties * | ForEach-Object { New-Object psobject -Property @{ Name = $_.Name "Work Phone" = $_.personalPager "IP Phone" = $_.ipPhone Direct = $_.telephoneNumber Moble = $_.mobile Title = $_.title.ToUpper() "E-Mail" = $_.mail } } Write-Verbose -Message 'Gathering Palmer Station' $PAL = Get-ADUser -Filter "(department -like 'IT*')" -SearchBase "OU=Palmer-Contractor,OU=Users,OU=OU-PALMER,DC=usap,DC=gov" -Properties * | ForEach-Object { New-Object psobject -Property @{ Name = $_.Name Title = $_.title.ToUpper() "E-Mail" = $_.mail } } Write-Verbose -Message 'Gathering Palmer Station' $SPO = Get-ADUser -Filter "(department -like 'IT*')" -SearchBase "OU=SouthPole-Contractor,OU=Users,OU=OU-SOUTHPOLE,DC=usap,DC=gov" -Properties * | ForEach-Object { New-Object psobject -Property @{ Name = $_.Name "Work Phone" = $_.extensionAttribute14 "Dorm Phone" = $_.extensionAttribute9 Title = $_.title.ToUpper() "E-Mail" = $_.mail } } } END { # McMurdo Write-Verbose -Message "Documenting McMurdo Station" $DocumentMCM = $MCM | Select-Object Name, "Work Phone", Pager, "Dorm Phone", Dorm, Room, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium16 -Title "McMurdo IT Phone List - Updated $Date" -TitleBold -TitleSize 20 -WorksheetName "MCM" -PassThru # Format the sheet # Title Row 1 $DocumentMCM.Workbook.Worksheets["MCM"].Row(1).Merged = $true $DocumentMCM.Workbook.Worksheets["MCM"].Row(1).Height = 25.0 # Colunm 1 (Name) $DocumentMCM.Workbook.Worksheets["MCM"].Column(1).style.font.bold = $true $DocumentMCM.Workbook.Worksheets["MCM"].Column(1).Width = 23.0 # Colunm 2 (Work Phone) $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).width = 13.0 $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Pager) $DocumentMCM.Workbook.Worksheets["MCM"].Column(3).width = 9.0 $DocumentMCM.Workbook.Worksheets["MCM"].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Dorm Phone) $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).width = 13.0 $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).Style.HorizontalAlignment = "Center" # Colunm 5 (Dorm) $DocumentMCM.Workbook.Worksheets["MCM"].Column(5).width = 9.0 $DocumentMCM.Workbook.Worksheets["MCM"].Column(5).Style.HorizontalAlignment = "Center" # Colunm 6 (Room) $DocumentMCM.Workbook.Worksheets["MCM"].Column(6).width = 9.0 $DocumentMCM.Workbook.Worksheets["MCM"].Column(6).Style.HorizontalAlignment = "Center" # Colunm 6 (Title) $DocumentMCM.Workbook.Worksheets["MCM"].Column(7).Width = 46.0 # Row 2 $DocumentMCM.Workbook.Worksheets["MCM"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $DocumentMCM.Save() $DocumentMCM.Dispose() # Denver Write-Verbose -Message "Documenting Denver Station" $DocumentDEN = $Den | Select-Object Name, "Work Phone", Title, "E-Mail" | Sort-Object Name | Export-Excel -Path $Path -TableName "DEN" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium17 -Title "Denver IT Phone List - Updated $Date" -TitleBold -TitleSize 20 -WorksheetName "DEN" -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $DocumentDEN.Workbook.Worksheets["DEN"].Row(1).Merged = $true $DocumentDEN.Workbook.Worksheets["DEN"].Row(1).Height = 25.0 # Colunm 1 (Name) $DocumentDEN.Workbook.Worksheets["DEN"].Column(1).style.font.bold = $true $DocumentDEN.Workbook.Worksheets["DEN"].Column(1).Width = 23.0 # Colunm 2 (Work Phone) $DocumentDEN.Workbook.Worksheets["DEN"].Column(2).width = 13.0 $DocumentDEN.Workbook.Worksheets["DEN"].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Title) $DocumentDEN.Workbook.Worksheets["DEN"].Column(3).width = 46.0 $DocumentDEN.Workbook.Worksheets["DEN"].Column(3).Style.HorizontalAlignment = "Left" # Colunm 4 (Email) $DocumentDEN.Workbook.Worksheets["DEN"].Column(4).width = 46.0 $DocumentDEN.Workbook.Worksheets["DEN"].Column(4).Style.HorizontalAlignment = "Left" # Row 2 $DocumentDEN.Workbook.Worksheets["DEN"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $DocumentDEN.Save() $DocumentDEN.Dispose() # Christchurch Write-Verbose -Message "Documenting Christchurch Station" $DocumentCHC = $CHC | Select-Object Name, "Work Phone", "IP Phone", Direct, Moble, Title, "E-Mail" | Sort-Object Name | Export-Excel -Path $Path -TableName "CHC" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium21 -Title "Christchurch IT Phone List - Updated $Date" -TitleBold -TitleSize 20 -WorksheetName "CHC" -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $DocumentCHC.Workbook.Worksheets["CHC"].Row(1).Merged = $true $DocumentCHC.Workbook.Worksheets["CHC"].Row(1).Height = 25.0 # Colunm 1 (Name) $DocumentCHC.Workbook.Worksheets["CHC"].Column(1).style.font.bold = $true $DocumentCHC.Workbook.Worksheets["CHC"].Column(1).Width = 23.0 # Colunm 2 (Work Phone) $DocumentCHC.Workbook.Worksheets["CHC"].Column(2).width = 13.0 $DocumentCHC.Workbook.Worksheets["CHC"].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (IP Phone) $DocumentCHC.Workbook.Worksheets["CHC"].Column(3).width = 13.0 $DocumentCHC.Workbook.Worksheets["CHC"].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Direct) $DocumentCHC.Workbook.Worksheets["CHC"].Column(4).width = 13.0 $DocumentCHC.Workbook.Worksheets["CHC"].Column(4).Style.HorizontalAlignment = "Center" # Colunm 5 (Mobile) $DocumentCHC.Workbook.Worksheets["CHC"].Column(5).width = 16.0 $DocumentCHC.Workbook.Worksheets["CHC"].Column(5).Style.HorizontalAlignment = "Center" # Colunm 6 (Title) $DocumentCHC.Workbook.Worksheets["CHC"].Column(6).width = 44.0 # Colunm 7 (E-Mail) $DocumentCHC.Workbook.Worksheets["CHC"].Column(7).width = 44.0 # Row 2 $DocumentCHC.Workbook.Worksheets["CHC"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $DocumentCHC.Save() $DocumentCHC.Dispose() # Palmer Write-Verbose -Message "Documenting Palmer Station" $DocumentPAL = $PAL | Select-Object Name, Title, "E-Mail" | Sort-Object Name | Export-Excel -Path $Path -TableName "PAL" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium19 -Title "Palmer IT Phone List - Updated $Date" -TitleBold -TitleSize 20 -WorksheetName "PAL" -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $DocumentPAL.Workbook.Worksheets["PAL"].Row(1).Merged = $true $DocumentPAL.Workbook.Worksheets["PAL"].Row(1).Height = 25.0 # Colunm 1 (Name) $DocumentPAL.Workbook.Worksheets["PAL"].Column(1).style.font.bold = $true $DocumentPAL.Workbook.Worksheets["PAL"].Column(1).Width = 23.0 # Colunm 2 (Title) $DocumentPAL.Workbook.Worksheets["PAL"].Column(2).width = 46.0 $DocumentPAL.Workbook.Worksheets["PAL"].Column(2).Style.HorizontalAlignment = "Left" # Colunm 3 (Email) $DocumentPAL.Workbook.Worksheets["PAL"].Column(3).width = 46.0 $DocumentPAL.Workbook.Worksheets["PAL"].Column(3).Style.HorizontalAlignment = "Left" # Row 2 $DocumentPAL.Workbook.Worksheets["PAL"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $DocumentPAL.Save() $DocumentPAL.Dispose() # South Pole Write-Verbose -Message "Documenting South Pole Station" $DocumentSPO = $SPO | Select-Object Name, "Work Phone", "Dorm Phone", Title, "E-Mail" | Sort-Object Name | Export-Excel -Path $Path -TableName "SPO" -AutoSize -ClearSheet -FreezePane 3 -TableStyle Medium18 -Title "South Pole IT Phone List - Updated $Date" -TitleBold -TitleSize 20 -WorksheetName "SPO" -ErrorAction SilentlyContinue -PassThru # Format the sheet # Title Row 1 $DocumentSPO.Workbook.Worksheets["SPO"].Row(1).Merged = $true $DocumentSPO.Workbook.Worksheets["SPO"].Row(1).Height = 25.0 # Colunm 1 (Name) $DocumentSPO.Workbook.Worksheets["SPO"].Column(1).style.font.bold = $true $DocumentSPO.Workbook.Worksheets["SPO"].Column(1).Width = 23.0 # Colunm 2 (Work Phone) $DocumentSPO.Workbook.Worksheets["SPO"].Column(2).width = 13.0 $DocumentSPO.Workbook.Worksheets["SPO"].Column(2).Style.HorizontalAlignment = "Center" # Colunm 3 (Dorm Phone) $DocumentSPO.Workbook.Worksheets["SPO"].Column(3).width = 13.0 $DocumentSPO.Workbook.Worksheets["SPO"].Column(3).Style.HorizontalAlignment = "Center" # Colunm 4 (Title) $DocumentSPO.Workbook.Worksheets["SPO"].Column(4).width = 46.0 $DocumentSPO.Workbook.Worksheets["SPO"].Column(4).Style.HorizontalAlignment = "Left" # Colunm 5 (Email) $DocumentSPO.Workbook.Worksheets["SPO"].Column(5).width = 46.0 $DocumentSPO.Workbook.Worksheets["SPO"].Column(5).Style.HorizontalAlignment = "Left" # Row 2 $DocumentSPO.Workbook.Worksheets["SPO"].Row(2).Style.HorizontalAlignment = "Left" # Save formatting $DocumentSPO.Save() $DocumentSPO.Dispose() # Open spredsheet when done if ($Show) { Write-Verbose -Message "Opening $Path" Invoke-Item $Path } } } <# .EXTERNALHELP MCMItPhoneLists.psm1-Help.xml #> function Get-McmItPhoneListDesktopReference { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateScript({ if ($_ -notmatch "(\.xlsx)") { throw "The file extension must be .xlsx" } return $true })] [System.IO.FileInfo] $Path, [switch] $Show ) 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" } PROCESS { # McMurdo Write-Verbose -Message "Gathering information" $MCM = Get-ADUser -Filter "(extensionAttribute10 -like '*') -AND (department -like 'IT*')" -Properties * | ForEach-Object { New-Object psobject -Property @{ Name = $_.Name "Work Phone" = $_.facsimileTelephoneNumber Pager = $_.pager "Dorm Phone" = $_.extensionAttribute12 Dorm = $_.extensionAttribute10 Room = $_.extensionAttribute11 Title = $_.title.ToUpper() } } } END { Write-Verbose -Message "Creating and formating to $Path" $DocumentMCM = $MCM | Select-Object Name, "Work Phone", Pager, "Dorm Phone", Dorm, Room, Title | Sort-Object Name | Export-Excel -Path $Path -TableName "MCM" -ClearSheet -AutoSize -FreezePane 3 -TableStyle Medium16 -Title "McMurdo IT Phone List (to be printed and kept at your desk) - Updated $Date" -TitleBold -TitleSize 20 -WorksheetName "MCM" -PassThru # Format the sheet # Title Row 1 $DocumentMCM.Workbook.Worksheets["MCM"].Row(1).Merged = $true $DocumentMCM.Workbook.Worksheets["MCM"].Row(1).Height = 25.0 # Colunm 1 (Name) $DocumentMCM.Workbook.Worksheets["MCM"].Column(1).style.font.bold = $true $DocumentMCM.Workbook.Worksheets["MCM"].Column(1).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(1).Style.Font.Size = 10 # Colunm 2 (Work Phone) $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).Style.HorizontalAlignment = "Center" $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).Style.WrapText = $true $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).Style.Font.Size = 10 $DocumentMCM.Workbook.Worksheets["MCM"].Column(2).Width = 6.0 # Colunm 3 (Pager) $DocumentMCM.Workbook.Worksheets["MCM"].Column(3).Style.HorizontalAlignment = "Center" $DocumentMCM.Workbook.Worksheets["MCM"].Column(3).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(3).Style.Font.Size = 10 $DocumentMCM.Workbook.Worksheets["MCM"].Column(3).Width = 6.0 # Colunm 4 (Dorm Phone) $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).Style.HorizontalAlignment = "Center" $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).Style.WrapText = $true $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).Style.Font.Size = 10 $DocumentMCM.Workbook.Worksheets["MCM"].Column(4).Width = 6.0 # Colunm 5 (Dorm) $DocumentMCM.Workbook.Worksheets["MCM"].Column(5).Style.HorizontalAlignment = "Center" $DocumentMCM.Workbook.Worksheets["MCM"].Column(5).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(5).Style.Font.Size = 10 $DocumentMCM.Workbook.Worksheets["MCM"].Column(5).Width = 6.0 # Colunm 6 (Room) $DocumentMCM.Workbook.Worksheets["MCM"].Column(6).Style.HorizontalAlignment = "Center" $DocumentMCM.Workbook.Worksheets["MCM"].Column(6).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(6).Style.Font.Size = 10 # Colunm 6 (Title) $DocumentMCM.Workbook.Worksheets["MCM"].Column(7).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Column(7).Style.Font.Size = 10 # Row 2 $DocumentMCM.Workbook.Worksheets["MCM"].Row(2).Style.HorizontalAlignment = "Center" $DocumentMCM.Workbook.Worksheets["MCM"].Row(2).Style.VerticalAlignment = "Top" $DocumentMCM.Workbook.Worksheets["MCM"].Row(2).Style.WrapText = $true # Save formatting $DocumentMCM.Save() $DocumentMCM.Dispose() # Open spredsheet when done if ($Show) { Write-Verbose -Message "Opening $Path" Invoke-Item $Path } } } |