Powershell script to capture OS level properties (CPU, Cores, NIC, Memory, BIOS, OS)

I have a master post which links to a set of powershell scripts all related to this. The Master post which links to all the scripts.

This script is an updated and streamlined version of this original and ugly script

This version actually dumps out to a CSV via the “Export-CSV” command-let. It has some error handling and will check the first WMI call and skip the rest if the first one can’t connect. It has a friendly output to the screen so the user knows which server is currently being queried.

$serverList = "..\SQL Servers.txt"
#$serverList = "test.txt"
[System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)

foreach ($server in (Get-Content $serverList)) 
{ 
    "Collecting information from $server"
    [System.Collections.Specialized.OrderedDictionary]$si = @{}    # Blank hashtable for each iteration
    $totCores=0

    $si.Server=[string]$server;

    try
    {
        [wmi]$sysInfo = get-wmiobject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $server -ErrorAction Stop
        [wmi]$bios = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $server
        [wmi]$os = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2" -Computername $server
        [array]$procs = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" -Computername $server
        [array]$mem = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" -ComputerName $server
        [array]$nic = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" -ComputerName $server | where{$_.IPEnabled -eq "True"}


        $si.Manufacturer=[string]$sysInfo.Manufacturer;
        $si.Model=[string]$sysInfo.Model;
        $si.TotMem=[string]$sysInfo.TotalPhysicalMemory;
        $si.BiosDesc=[string]$bios.Description;
        $si.BiosVer=[string]$bios.SMBIOSBIOSVersion+"."+$bios.SMBIOSMajorVersion+"."+$bios.SMBIOSMinorVersion;
        $si.BiosSerial=[string]$bios.SerialNumber;
        $si.OSName=[string]$os.Name.Substring(0,$os.Name.IndexOf("|") -1);
        $si.Arch=[string]$os.OSArchitecture;
        $si.Processors=[string]@($procs).count;
        $si.Cores=[string]$procs[0].NumberOfCores;
        $iter=0
        while ($iter -lt 12)   #Parse through each stick of memory (arbitrary 12 Memory stick columns)
        {
            if ($mem[$iter] -ne $null) {
                $si."MemBank$iter" = [string]$mem[$iter].Capacity + "," + [string]$mem[$iter].BankLabel 
            } else {
                $si."MemBank$iter" = ""
            }
            $iter++
        }
        $iter=0
        while ($iter -lt 4)    #Parse through each NIC (arbritrary 4 Network Card columns)
        {
            $si."DHCP$iter"=[string]$nic[$iter].DHCPEnabled;
            $si."IPAddress$iter"=[string]$nic[$iter].IPAddress;
            $si."Subnet$iter"=[string]$nic[$iter].IPSubnet;
            $si."Gateway$iter"=[string]$nic[$iter].DefaultIPGateway;
            $si."Mac$iter"=[string]$nic[$iter].MacAddress;
            $iter++
        }
    }
    catch [Exception]
    {
        "Could not contact $server, skipping to next"
        Continue
    }
    finally
    {
        [void]$sysCollection.Add((New-Object PSObject -Property $si))    #Add the dictionary list to the collection
    }
}

$sysCollection | Export-CSV -path ServerInventory.csv -NoTypeInformation    #Output the collection to a CSV file

T-SQL Tuesday #39 Changing Service Accounts or Passwords with PoSH

T-SQL Tuesday #39 Changing Service Accounts or Passwords with PoSH

T-SQL Tuesday

I’ve done one other of these in the past. This is a good one, and I too love using Powershell to automate management of multiple SQL Servers. Mine is an example of a situation I was in. We had 30 or so SQL Servers that were configured with Domain accounts to run each of the major services – the Engine, SSAS, SSIS, SSRS, and the Agent. However, these accounts were shared across the computers, so an account XYZ\SQLServer-SVC was used to run SQL Server Engine on all the servers. Due to circumstances outside my control we had to change passwords on all accounts.

I was dreading the fact that I would have to take a major outage and race to change the service account password in AD, and then do each server individually. Then I thought, POWERSHELL, you can save me! Using SMO.WMI.ManagedComputer was the trick for me. Here’s the code.

#Load the SqlWmiManagement assembly off of the DLL

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$serviceAccountList = Get-Content "SQL Servers.txt"

foreach ($server in $serviceAccountList)
{
    $SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $server #Suck in the server you want
    $changeService = $SMOWmiserver.Services | where {$_.serviceAccount -eq "XYZ\SQLServer-SVC"} #| select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table
    if ($changeService.name -ne $null) {
        foreach ($service in $changeService)
        {
            "Service " + $service.name + " found on " + $server
		
	    # Change the service account name or password as needed		

            $service.setServiceAccount("ACCOUNT","PASSWORD")
            "Service " + $service.name + " changed on " + $server
            $service = $null
        } 
    } else {
        "Specified account was not found on " + $server
    }
    $changeService = $null
    $SMOWmiserver = $null
}

Right there, $service.setServiceAccount, that’s the magic. The nice thing about this approach is that it will take care of the dependencies. I started with another approach which would change the service account and then restart the service, but had issues with dependent services, like SQL Agent stopping me from stopping SQL Server outright. With the setServiceAccount part of ManagedComputer, it handled it for me.

Oh, and all new Servers, they have unique domain Service Account credentials, we no longer share a service account across computers. With SQL 2012, I plan on testing out Managed Service Accounts.