This script will put all the site systems and their roles in an Excel spreadsheet. This is the first script I will be putting in my BitBucket repository which you can get to from the menu above, please let me know if there are any issues with getting this script. OK, enough small talk, let’s look this script. We start with the opening comments and parameters.
|
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 |
<# .Synopsis Creates an Excel spreadsheet listing all your site servers and their roles. .DESCRIPTION This script will create an Excel spreadsheet listing all your site servers and current roles. It will also show the total number of roles held by each server on the last column and the total number of servers holding each role along the last row. .PARAMETER SiteServer This is the SMSProvider for the site you are after .PARAMETER LogLevel This is the minimum logging level you want for the logfile, nothing below this level will be logged. It defaults to 2. The levels are: 1 - Informational 2 - Warning 3 - Error .PARAMETER LogFileDir This is the directory where the log will be created. It defaults to C:Temp .EXAMPLE List-SiteSystems -SiteServer SCCM01 This will list all the site systems from the site server SCCM01 .LINK http://parrisfamily.com .NOTES 9/10/2015 - Created script #> [CmdletBinding(SupportsShouldProcess=$true)] Param( [Parameter(Mandatory=$true,HelpMessage="Site server where the SMS Provider is installed")] [ValidateScript({Test-Connection -ComputerName $_ -Count 1 -Quiet})] [String]$SiteServer, [Parameter(Mandatory=$false,HelpMessage="Sets the Log Level, 1 - Informational, 2 - Warning, 3 - Error")] [Int32]$LogLevel = 2, [Parameter(Mandatory=$false,HelpMessage="Log File Directory")] [String]$LogFileDir = 'C:Temp' ) |
The comments pretty much explain what’s going on for the parameters, so next we have my usual log entry portion.
|
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 |
#Begin New-LogEntry Function Function New-LogEntry { # Writes to the log file Param ( [Parameter(Position=0,Mandatory=$true)] [String] $Entry, [Parameter(Position=1,Mandatory=$false)] [INT32] $type = 1, #If not specified, it's informational [Parameter(Position=2,Mandatory=$false)] [String] $component = $ScriptName ) Write-Verbose $Entry if ($type -ge $Script:LogLevel) { if ($Entry.Length -eq 0) { $Entry = 'N/A' } $TZOffset = ([TimeZoneInfo]::Local).BaseUTcOffset.TotalMinutes $TZOffset = "$(Get-Date -Format "HH:mm:ss.fff")+$(-$TZOffset)" $Entry = "<!--[LOG[{0}]LOG]!--><time=""{2}"" type="" date="" {3}""="" thread="" {4}""="" ""="" context="" {5}""="" component="" {1}""="">" -f $Entry, (Get-Date -Format "MM-dd-yyyy"), $TZOffset, $pid, $type, $component $Entry | Out-File $Script:LogFile -Append -Encoding ascii } } #End New-LogEntry #Build variables for New-LogEntry Function #First, get the script name $ScriptName = $MyInvocation.MyCommand.Name #Next, make sure the LogFileDir has the at the end, if not, put it there if(-not ($LogFileDir -match '\$')){$LogFileDir = "$LogFileDir"} #Now, take that script name and get rid of the .ps1 at the end $LogFile = $ScriptName -replace '(.*).ps1', '$1' #Finally, put the two together to get the log file name. $LogFile = $LogFileDir + $LogFile + '.log' |
This is something I’m doing in all my scripts, it, by default, creates a log file in c:temp and names it the same name as the script, but with a .log extension. This file can be viewed real-time by cmtrace.exe when the script is running. I will usually change the loglevel parameter (from the first section) to 1 while testing and change to 2 when finished. In the case of this script, I got lazy on the error checking and that default will basically turn off logging.
Now for the meat.
This section starts of by logging that we are starting and then imports the Configuration Manager Module. I use the one from Microsoft which I got here. The nice thing about these is they work on 64 bit platforms.
|
1 2 3 4 5 6 |
New-LogEntry 'Starting Script' New-LogEntry "SiteServer - $SiteServer" New-LogEntry "Importing SCCM Module from $($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + 'ConfigurationManager.psd1')" #SCCM Import Module import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + 'ConfigurationManager.psd1') |
Next, we get the SiteCode, use Push-Location to save the current directory on the stack and change to the SiteCode: location so the ConfigurationManager cmdlets will work.
|
1 2 3 4 5 6 7 8 9 |
New-LogEntry 'Getting SiteCode' $SiteCode = $(Get-WmiObject -ComputerName $SiteServer -Namespace 'rootSMS' -Class SMS_ProviderLocation).SiteCode New-LogEntry "Success! It's $SiteCode" #Save the directory we are in to the stack so we can get back there when we are done. Push-Location Set-Location "$($SiteCode):" |
Now we start creating the Excel spreadsheet.
|
1 2 3 4 5 6 |
New-LogEntry 'Creating Excel SpreadSheet' $objExcel = New-Object -ComObject Excel.Application $objExcel.Visible = $true $objWorkBook = $objExcel.Workbooks.Add() $objWorkSheet = $objWorkBook.Worksheets.Item(1) |
Now, we get the roles, make sure to sort them alphabetically and only get unique names.
|
1 2 3 4 |
New-LogEntry 'Getting Site System Roles' $Roles = @() #Intitialize the array $Roles = $(Get-CMSiteRole).RoleName | Sort-Object -Unique #This will get all the unique roles used in the site and sort them |
Now this section first fills out the header line with Server Name and Site Code and formats the text and fill color.
|
1 2 3 4 5 6 7 8 9 10 11 |
New-LogEntry 'Filling out spreadhseet header' $objExcel.cells.item(1,1) = 'Server Name' #Text $objExcel.cells.item(1,1).Font.Size = '14' #Size $objExcel.cells.item(1,1).Font.Bold = $true #Bold $objExcel.cells.item(1,1).Interior.Color=15849925 #Fill Color $objExcel.cells.item(1,2) = 'Site Code' #Text $objExcel.cells.item(1,2).Font.Size = '14' #Size $objExcel.cells.item(1,2).Font.Bold = $true #Bold $objExcel.cells.item(1,2).Orientation = 90 #Rotate text $objExcel.cells.item(1,2).Interior.Color=15849925 #Fill Color |
We finish off the header row with the list of Roles and end it with a column for the total number of roles/server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
New-LogEntry 'Adding the roles to the header' for($i=0;$i -le $Roles.Count;$i++) #Because the roles start at 0, they actually end at Count -1, but we want the last row for totals, so we use -le instead of -lt { if ($i -eq $Roles.Count) #If we are at the last one, put up the totals { $objExcel.cells.item(1,$i+3) = 'Total Roles' $objExcel.cells.item(1,$i+3).Font.Size = 14 $objExcel.cells.item(1,$i+3).Font.Bold = $true $objExcel.cells.item(1,$i+3).Orientation = 90 $objExcel.cells.item(1,$i+3).Interior.Color=15849925 } else #Othewise, put up the role name. { $objExcel.cells.item(1,$i+3) = $Roles[$i] $objExcel.cells.item(1,$i+3).Font.Size = 14 $objExcel.cells.item(1,$i+3).Font.Bold = $true $objExcel.cells.item(1,$i+3).Orientation = 90 $objExcel.cells.item(1,$i+3).Interior.Color=15849925 } } |
Now that we have the header done, let’s start putting data in. We set some variables and gather data.
|
1 2 3 4 5 |
$y = 2 #Starting at row 2 New-LogEntry 'Getting site systems' $SiteSystems = Get-CMSiteSystemServer | Sort-Object -Property NetworkOSPath #Get the info and sort by Site System Name |
Now that we’ve got the data, fill out the sheet. The comments in the code should explain what is going on.
|
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 |
New-LogEntry 'Filling out site systems and their roles' foreach($Row in $SiteSystems) #Get the site system { $Name = $Row.NetworkOSPath -replace "\*(.*)", '$1' #Set the name and remove the leading \ $objExcel.cells.item($y,1) = $Name.ToUpper() #Put the name in the spreadsheet uppercase $objExcel.cells.item($y,2) = $Row.SiteCode #Put in the site code #Get the roles for this site system $CMRoles = Get-CMSiteRole -SiteSystemServerName $Name foreach($Role in $CMRoles) #Now we are going to put an * in each role the system has { for($x=0;$x -le $Roles.Count;$x++) #Again, we use -lt so we can put the formula in for the totals column { if ($x -lt $Roles.count) { if ($role -match $Roles[$x]) { $objExcel.cells.item($y,$x+3) = '*' #Put in the * $objExcel.cells.item($y,$x+3).HorizontalAlignment = -4108 #This will center it } } else { $Formula="=COUNTA(C$($y):$([char](64 + $x+2))$($y))" #Generate the formula, converting the $x to a letter $objExcel.cells.item($y,$x+3).Formula = $Formula #Put in the formula $objExcel.cells.item($y,$x+3).Font.Bold = $true #Bold } } } if (($y % 2) -eq 1) #Check if this is an odd numbered row { #If it is, set the fill color. for($i=1;$i -lt $roles.Count + 4;$i++) { $objExcel.cells.item($y,$i).Interior.Color=15849925 } } $y++ } |
Now there’s just a little bit left to do. We need to put the totals on the bottom row so we know how many servers in each role.
|
1 2 3 4 5 6 7 8 9 10 11 |
New-LogEntry 'Putting in totals' $objExcel.cells.item($y,1) = 'Totals' $objExcel.cells.item($y,1).Font.Bold = $true for($i=3;$i -lt $Roles.Count +3; $i++) { $Formula="=COUNTA($([char](64 + $i))2:$([char](64 + $i))$($y-1))" $objExcel.cells.item($y,$i).Formula = $Formula $objExcel.cells.item($y,$i).Font.Bold = $true } |
The last thing we do is autofit the columns, put the drive back in the location and log that we are done!
|
1 2 3 4 5 6 7 8 |
New-LogEntry 'Formatting sheet to autofit' $Range = $objWorkSheet.UsedRange [void] $Range.EntireColumn.Autofit() #We're done! Time to return to our original directory Pop-Location New-LogEntry 'Finished Script' |
When you have finished the script, you end up with a spreadsheet that looks something like this:
I hope you find this script useful!