This has been the week from hell. Our software update points have been having issues syncing for quite a while and we finally had to rip them out and reinstall them. We changed the configuration and use a shared database and content store for all the SUPs in a site. We also learned that we were grossly underpowered on a variety of our site systems, so I decided to add memory and processor information to the list-sitesystems.ps1 script. I cleaned up the script a little bit and have updated bitbucket (you can get to it from the main menu above). I’ll put the script in here, but I’m going to rely on the comments to help explain the script. We’ll see the output below.
|
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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 |
<# .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 heald 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 BackGroundColor This is the color that is filled on the odd row cells. It defaults to 15849925. .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="Background color, defaults to 15849925")] [int32]$BackGroundColor=15849925, [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' ) #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}"" date=""{1}"" component=""{5}"" context="""" type=""{4}"" thread=""{3}"">" -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' 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 '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 'Creating Excel SpreadSheet' $objExcel = New-Object -ComObject Excel.Application $objExcel.Visible = $true $objWorkBook = $objExcel.Workbooks.Add() $objWorkSheet = $objWorkBook.Worksheets.Item(1) 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 'Filling out spreadhseet header' $objExcel.cells.item(1,1) = 'Server Name' #Text $objExcel.cells.item(1,2) = 'Site Code' #Text 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' } else #Othewise, put up the role name. { $objExcel.cells.item(1,$i+3) = $Roles[$i] } } $objExcel.cells.item(1,$Roles.count+4) = 'Processor Name' $objExcel.cells.item(1,$Roles.count+5) = 'Processor Speed' $objExcel.cells.item(1,$Roles.count+6) = 'Number of Procs' $objExcel.cells.item(1,$Roles.count+7) = 'Memory' for ($i=1;$i -le $Roles.count+7;$i++) { $objExcel.cells.item(1,$i).Font.Size = 14 $objExcel.cells.item(1,$i).Font.Bold = $true if ($i -gt 1) {$objExcel.cells.item(1,$i).Orientation = 90} $objExcel.cells.item(1,$i).Interior.Color=$BackGroundColor } $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 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 } } } New-LogEntry 'Getting Processor Info' $objProccessor = Get-WmiObject -ComputerName $Name -Namespace rootcimv2 -Class Win32_Processor $objExcel.cells.item($y,$Roles.count+4) = $objProccessor.Name $objExcel.cells.item($y,$Roles.Count+5) = "{0:N0}" -f $objProccessor.MaxClockSpeed New-LogEntry 'Figuring out how many Procs' [int]$intTotalCores = 0 foreach($CPU in $objProccessor) { $intTotalCores += $CPU.NumberOfLogicalProcessors } $objExcel.cells.item($y,$Roles.count+6) = $intTotalCores New-LogEntry 'Getting Memory' $objMemory = Get-WmiObject -ComputerName $Name -Namespace rootcimv2 -Class Win32_PhysicalMemory [int64]$intTotalMemory = 0 New-LogEntry 'Calculating Memory' foreach($Bank in $objMemory) { $intTotalMemory += "{0:N0}" -f $Bank.Capacity } $objExcel.cells.item($y,$Roles.count+7) = $intTotalMemory if (($y % 2) -eq 1) #Check if this is an odd numbered row { #If it is, set the fill color. New-LogEntry 'Odd row, filling color' for($i=1;$i -lt $roles.Count + 8;$i++) { $objExcel.cells.item($y,$i).Interior.Color=$BackGroundColor } } $y++ } 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 '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 we ran the script, here is what we get (some names have been changed to protect the innocent):
You may notice, the DMZ machines didn’t pull in the memory or processor info, obviously, this is a firewall issue. Also, I still need to put more error detection into the script so to better handle these issues, but until then, enjoy!
If you have any questions, please feel free to comment. Thanks!