Over the last few months I’ve been directed to ensure that all of our SQL Servers in the Development and QA segments have been installed with the proper licensing, namely Developer Edition.    One of the challenges I’ve ran across is how do I verify the SSRS and SSAS features are on the right versions. For SQL Server I can simply perform a multi-server query from our CMS and easily retrieve this information using the various SERVERPROPERTY options, but I’m not sure how to do this for the ancillary services.

Since I have limited experience with those two features of SQL Server I decided to go with Powershell, since it can simply do anything you ask of it.

In this example, I am going to use Powershell to extract these values directly from the registry of the host computer.

$computers = Get-Content 'Server1','Server2','Server3','Server4'
$results = @()
foreach ($computer in $computers) {
    Try {
        $Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $computer)
        $RegKey= $Reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names")
        $features = $RegKey.GetSubKeyNames();
        foreach ($i in $features) 
        {
            ## Get installed Features 
            $key = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\$i"
            $FeatureKey= $Reg.OpenSubKey($key)
            $Value = $FeatureKey.GetValueNames();
            $instances = $FeatureKey.GetValueNames();
            foreach ($instance in $instances) {
                if ($instance -ne '') {
                    $instanceName = $FeatureKey.GetValue($instance)
                    $key = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\$instanceName\\Setup"
                    $instanceKey= $Reg.OpenSubKey($key)
                    $object = New-Object PSObject
                    Add-Member -InputObject $object -MemberType NoteProperty -Name Computer -Value $computer
                    Add-Member -InputObject $object -MemberType NoteProperty -Name Feature -Value $i
                    Add-Member -InputObject $object -MemberType NoteProperty -Name Version -Value $instanceKey.GetValue('Version')
                    Add-Member -InputObject $object -MemberType NoteProperty -Name Edition -Value $instanceKey.GetValue('Edition')
                    $results += $object
                }
            }
        }
    }
    Catch {
        Write-Host "$computer Not Reachable"
    }
}
$results | Out-GridView

Running this against a list of servers defined in the top line will result in this output:

Now the beauty of Powershell is that I could source my list from a list in a text file:

$computers = Get-Content "c:\servers.txt"

Or directly from SQL:

$computers = Invoke-Sqlcmd "select SRSI.name  FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SRSI" -ServerInstance "MyCMSServer"

Or from Active Directory (I know there is an inline way to filter this down, but I’m not sure the syntax right off.

$computers = @() $ou = [ADSI]"<a href="ldap://OU=SQL,OU=Prod,OU=Servers,OU=Resources,OU=CORP,DC=rjf,DC=com">LDAP://OU=SQL,OU=Prod,OU=Servers,DC=smashrock,DC=com</a>"
foreach ($child in $ou.psbase.Children) {     if ($child.ObjectCategory -like '*computer*') {         $computers += $child.Name     } }

In our case, scanning the Active Directory allows us to check the entire AD Server group to ensure we are not missing any rogue SSRS or SSAS servers that haven’t been added to our CMS lists which could throw us into a licensing surprise when Microsoft walks in to do their audit.

Hopefully this will help you.