This is part 4 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.
— Update, I have updated this powershell script with a much easier to read and spits out a CSV that can be opened right in Excel without any manipulation. —
The new version of this script is now here
This script gathers information about the server that SQL Server is running on. This one is unique from the rest, as it connects not to SQL Server at all, but rather to WMI to run queries to capture things like the number of CPUs and Cores (think SQL Server 2012 licensing!), memory configuration, OS, IP’s and other network info. This script also expects a text file with a list of servers, I have a file called PhysicalServers.txt that has a list of hostnames. I redirect the output of this script to a csv file, and then import that data into a excel spreadsheet. This one has only a single worksheet, one row for each server in the text file.
The base of this script came from Jesse Hamrick and his awesome site of Powershell Knowledge. Update 5/20/2016 – it seems the site I originally referenced is compromised now with malware. I have removed the link to the website.
#* Connect to computer
$serverList = "PhysicalServers.txt"
foreach ($server in (Get-Content $serverList))
{
$strComputer = $server
# "SysInfo Information"
$myOutput = $strComputer + ","
$colItems = Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" `
-ComputerName $strComputer
foreach($objItem in $colItems) {
$myOutput += [string]$objItem.Manufacturer + ","
$myOutput += [string]$objItem.Model + ","
$myOutput += [string]$objItem.TotalPhysicalMemory + " bytes,"
}
# "System BIOS Information"
$colItems = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $strComputer
foreach($objItem in $colItems) {
$myOutput += [string]$objItem.Description + ","
$myOutput += [string]$objItem.SMBIOSBIOSVersion + "." + $objItem.SMBIOSMajorVersion + "." + $objItem.SMBIOSMinorVersion + ","
$myOutput += [string]$objItem.SerialNumber + ","
}
# "Operating System Information"
$colItems = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2"`
-Computername $strComputer
foreach($objItem in $colItems) {
$subOSName = $objItem.Name.IndexOf("|") -1
$myOutput += $objItem.Name.Substring(0,$subOSName) + ","
$myOutput += [string]$objItem.OSArchitecture + ","
}
# "Processor Information"
$colItems = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" `
-Computername $strComputer
$count=0
$temp=""
foreach($objItem in $colItems) {
$count++
$temp = $objItem.Name + "," + $count + "," + $objItem.NumberOfCores
}
$myOutput += [string]$temp + ","
# "Memory Information"
$colItems = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" `
-ComputerName $strComputer
$iter=1
foreach($objItem in $colItems) {
$iter++
$myOutput += [string]$objItem.Capacity + ","
$myOutput += [string]$objItem.BankLabel + ","
}
# Fill out the remaining memory slots with blanks (in my environment, the biggest server I have has 12 slots, so that's the max)
while($iter -lt 13)
{
$myOutput += [string]",,"
$iter++
}
# "Network Information"
$colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" `
-ComputerName $strComputer | where{$_.IPEnabled -eq "True"}
foreach($objItem in $colItems) {
$myOutput += [string]$objItem.DHCPEnabled + ","
$myOutput += [string]$objItem.IPAddress + ","
$myOutput += [string]$objItem.IPSubnet + ","
$myOutput += [string]$objItem.DefaultIPGateway + ","
$myOutput += [string]$objItem.MACAddress
}
$myOutput
}
