One function of mine that I use quite a bit to get information about SQL servers is Get-SQLInstance not only because it provides most of the information that I am looking for but it also is a great way to scan for all of the SQL servers on my network when combined with an Active Directory query to grab all of the servers on the domain.

This was a completely registry-centric tool that used different parts of the registry to gather a decent amount of information without having to actually connect to the SQL server itself and running some TSQL commands to learn more about the system. Now by looking at WMI, I can grab even more information while still not worrying about making a connection and running TSQL.

WMI, as you may or may not know, is an excellent repository of data that exists in Windows and lets you query for just about anything as long as you know where to look. Of course, using PowerShell means that exploring WMI is as simple as running some Get-WMIObject commands using either –List or (if you already know the class) connecting directly to the class using –Class and seeing what information is sitting there waiting to be discovered!

Some of the information that I need requires me to do a little digging around. I have to look at the Root\Microsoft\SQLServer namespace and then taking that information to then look at and then after I find what I need, I can then dig a little deeper into the namespace to unearth more information about the SQL server.

$WMIParams=@{
    Computername = $Computer
    NameSpace='root\Microsoft\SqlServer'
    Query="SELECT name FROM __NAMESPACE WHERE name LIKE 'ComputerManagement%'"
    Authentication = 'PacketPrivacy'
    ErrorAction = 'Stop'
}

I already know what properties I am looking for, so I am going to build a hash table with empty values that I can then use later on for adding items to it. This way, if there are no values added, it will still have the property names.

$PropertyHash = [ordered]@{
    Computername = $Computer
    Instance = $Null
    SqlServer = $Null
    WmiNamespace = $Null
    SQLSTATES = $Null
    VERSION = $Null
    SPLEVEL = $Null
    CLUSTERED = $Null
    INSTALLPATH = $Null
    DATAPATH = $Null
    LANGUAGE = $Null
    FILEVERSION = $Null
    VSNAME = $Null
    REGROOT = $Null
    SKU = $Null
    SKUNAME = $Null
    INSTANCEID = $Null
    STARTUPPARAMETERS = $Null
    ERRORREPORTING = $Null
    DUMPDIR = $Null
    SQMREPORTING = $Null
    ISWOW64 = $Null
    BackupDirectory = $Null
    AlwaysOnName = $Null
}

Now we can start out with our query to first find out the namespace that I want to connect to and then to start looking at the SQL properties using a different filter. In this case SqlServiceType = 1 means that I am only looking for the  SQL Server service (more info on that here).

$Namespace = (Get-WMIObject @WMIParams).Name
$Filter = "SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType=1" 
$PropertyHash['WMINamespace'] = $Namespace
$WMIParams.NameSpace="root\Microsoft\SqlServer\$Namespace"
$WMIParams.Query=$Filter
$WMIResults = Get-WMIObject @WMIParams

The results of this query provides us with a nice amount of information in both a string and number format.

From here it is a matter of adding each of these to their respected name in the hash table and then displaying the results at the end.

$WMIResults | ForEach {
    $Name = "{0}{1}" -f ($_.PropertyName.SubString(0,1),$_.PropertyName.SubString(1).ToLower())    
    $Data = If ($_.PropertyStrValue) {
        $_.PropertyStrValue
    }
    Else {
        If ($Name -match 'Clustered|ErrorReporting|SqmReporting|IsWow64') {
            [bool]$_.PropertyNumValue
        }
        Else {
            $_.PropertyNumValue
        }        
    }
    $PropertyHash[$Name] = $Data
}

The end result is some nice additions to my function that you can see below.

Get-SQLInstance –Computername vSCOM

Be sure to download the updated function below and if you want to help make this better, be sure to fork my repo and submit a pull request!

Download the updated Get-SQLInstance

https://gallery.technet.microsoft.com/scriptcenter/Get-SQLInstance-9a3245a0

Help contribute to Get-SQLInstance

https://github.com/proxb/PowerShell_Scripts