Updated Function: Get-SQLInstance

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.

image

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

image

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

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell and tagged , , , . Bookmark the permalink.

2 Responses to Updated Function: Get-SQLInstance

  1. Andre Kamman says:

    I’ve got multiple WMINamespaces which made the WMI queries fail.
    I’ve changed line 192 to:
    $Namespace = (Get-WMIObject @WMIParams | Sort -Descending | Select * -First 1).Name
    This makes it only select Computermanagement12 (I have 10 as well)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s