Script to list the Site Systems and their roles.

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.

<#
.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'
)

.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.

#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'

#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 = "<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.

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')

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.

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):"

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.

New-LogEntry 'Creating Excel SpreadSheet'

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$objWorkBook = $objExcel.Workbooks.Add()
$objWorkSheet = $objWorkBook.Worksheets.Item(1)

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.

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

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.

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

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.

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
    }
}

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.

$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

$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.

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++
}

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.

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
}

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!

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'

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!

Share this:

Like this: