Get Servers from Central Management Server

If you’re not aware, SQL Server Management Studio (SSMS) has a feature called Registered Servers. If it’s not visible press ctrl + Alt + G to bring it up. You can now register servers to a Central Management Server. This can be any server including SQLExpress on your local machine. You can right click on any server to launch a query with a connection to that server just like you would in Object Explorer. The real power comes when you right click on a group and get one query windows with connections to all the servers in that group. The Get-DbaRegisteredServer cmdlet from the dbatools module gives you similar power. Pass the SQL Instance of the CMS server and the name of the group you would like to retrieve.

    
        
$CMSServer = 'SQL28';
$cms = Get-DbaRegisteredServer -SqlInstance $CMSServer -Group 'AllServers'
    

Query All Servers

This is a query I sometimes use to get the basic inventory information from my servers. When I run it from SSMS, I run it against all registered servers in my Central Management Servers list.

    
        
SELECT
SERVERPROPERTY('MachineName') as Machine
, ISNULL(SERVERPROPERTY('InstanceName'),'Default') as Instance
, CASE SERVERPROPERTY('ProductMajorVersion')
	WHEN '12' THEN 'SQL Server 2014'
	WHEN '13' THEN 'SQL Server 2016'
	WHEN '14' THEN 'SQL Server 2017'
	WHEN '15' THEN 'SQL Server 2019'
  END as [SQL Version]
, SERVERPROPERTY('ProductVersion') as ProductVersion
, SERVERPROPERTY('Edition') as Edition
, SERVERPROPERTY('ProductLevel') as SP
, case  SERVERPROPERTY('ProductUpdateLevel') 
	WHEN 'CU' THEN ''
	ELSE SERVERPROPERTY('ProductUpdateLevel') 
END as CU
, RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<', @@VERSION)-5, 4), 4) as [WinVerNum]
, replace(substring(right(@@version,(len(@@version) - CHARINDEX(' on ', @@version) -3)),1,charindex('<',right(@@version,(len(@@version) - CHARINDEX(' on ', @@version) -3))) -2),RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<', @@VERSION)-5, 4), 4),'') as [WinVer]
, CASE 
	WHEN CHARINDEX('Hypervisor',@@VERSION) > 1 THEN 'Yes' ELSE 'No' 
	END AS [Is_VM] ;
    

I put my query into a "here" string.

    
        
$query = @"
select 
SERVERPROPERTY('MachineName') as Machine
, ISNULL(SERVERPROPERTY('InstanceName'),'Default') as Instance
, CASE SERVERPROPERTY('ProductMajorVersion')
	WHEN '12' THEN 'SQL Server 2014'
	WHEN '13' THEN 'SQL Server 2016'
	WHEN '14' THEN 'SQL Server 2017'
	WHEN '15' THEN 'SQL Server 2019'
  END as [SQL Version]
, SERVERPROPERTY('ProductVersion') as ProductVersion
, SERVERPROPERTY('Edition') as Edition
, SERVERPROPERTY('ProductLevel') as SP
, case  SERVERPROPERTY('ProductUpdateLevel') 
	WHEN 'CU' THEN ''
	ELSE SERVERPROPERTY('ProductUpdateLevel') 
END as CU
, RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<', @@VERSION)-5, 4), 4) as [WinVerNum]
, replace(substring(right(@@version,(len(@@version) - CHARINDEX(' on ', @@version) -3)),1,charindex('<',right(@@version,(len(@@version) - CHARINDEX(' on ', @@version) -3))) -2),RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<', @@VERSION)-5, 4), 4),'') as [WinVer]
, CASE 
	WHEN CHARINDEX('Hypervisor',@@VERSION) > 1 THEN 'Yes' ELSE 'No' 
	END AS [Is_VM] ;
 "@;
    
With the PowerShell dbatools I can do the equivalent by passing the $cms object I retrieved from Get-DbaRegisteredServer in the earlier example along with $query to the Invoke-DbaQuery cmdlet. It automatically runs the query agains all the servers in the CMS list object. I save the results to a variable.
    
        
$results = Invoke-DbaQuery -SqlInstance $cms -Query $query
    
I want my server and instance to show up as one column so I create a scriptblock variable to use with Select-Object (a more detailed example in my post Colorize PowerShell Select-Object ). I then pipe to Format-Table to get the following.
 
     
         
$exp = [scriptblock]{$_.Machine + $(if($_.Instance -ne ''){'\' + $_.Instance})}
$results | select @{n='Server';e=$exp},'SQL Version',ProductVersion,Edition,SP,CU,WinVerNum,WinVer | Format-Table 

Server        SQL Version     ProductVersion Edition                                           SP  CU   WinVerNum WinVer
------        -----------     -------------- -------                                           --  --   --------- ------
SQL01         SQL Server 2014 12.0.6329.1    Enterprise Edition (64-bit)                       SP3 CU4   6.3      Windows NT
SQL02         SQL Server 2014 12.0.6329.1    Enterprise Edition (64-bit)                       SP3 CU4   6.3      Windows NT
SQL04         SQL Server 2014 12.0.6329.1    Enterprise Edition (64-bit)                       SP3 CU4   6.3      Windows NT
SQL05         SQL Server 2014 12.0.6329.1    Enterprise Edition (64-bit)                       SP3 CU4   6.3      Windows NT
SQL09         SQL Server 2014 12.0.6329.1    Standard Edition (64-bit)                         SP3 CU4   6.3      Windows NT
SQL10         SQL Server 2016 13.0.5426.0    Enterprise Edition: Core-based Licensing (64-bit) SP2 CU8  10.0      Windows Server 2016 Datacenter
SQL11         SQL Server 2016 13.0.5882.1    Enterprise Edition: Core-based Licensing (64-bit) SP2 CU16 10.0      Windows Server 2016 Standard
SQL12         SQL Server 2016 13.0.5830.85   Enterprise Edition: Core-based Licensing (64-bit) SP2 CU14  6.3      Windows Server 2012 R2 Standard
SQL14         SQL Server 2019 15.0.4123.1    Enterprise Edition: Core-based Licensing (64-bit) RTM CU10 10.0      Windows Server 2019 Standard
SQL15         SQL Server 2016 13.0.5882.1    Enterprise Edition (64-bit)                       SP2 CU16  6.3      Windows Server 2012 R2 Standard
...
< more of same omitted >
...
SQL61\DEV2016 SQL Server 2016 13.0.5830.85   Developer Edition (64-bit)                        SP2 CU14  6.2      Windows Server 2012 Standard
SQL62\RB01    SQL Server 2016 13.0.5830.85   Enterprise Edition: Core-based Licensing (64-bit) SP2 CU14  6.3      Windows Server 2012 R2 Standard
SQL78         SQL Server 2019 15.0.4123.1    Enterprise Edition: Core-based Licensing (64-bit) RTM CU10 10.0      Windows Server 2019 Standard
SQL79         SQL Server 2019 15.0.4123.1    Enterprise Edition: Core-based Licensing (64-bit) RTM CU10 10.0      Windows Server 2019 Standard
 
    

Export to Excel

Now to get it into Excel. Instead of piping to Format-Table we’re going to pipe to the Export-Excel from the ImportExcel module. With no parameters, a temp .xlsx file is created and Excel is launched with your results.

Extra columns passed from object
If you don’t use Select-Object you will end up with extra columns that are normally hidden but are shown in Excel.

    
        
$results | select @{n='Server';e=$exp},'SQL Version',ProductVersion,Edition,SP,CU,WinVerNum,WinVer | Export-Excel 
    
The result looks like this. If you just wanted to get it into Excel to manipulate on your own, this would be all you need. Very useful and it looks pretty good out of the box. With just a little more effort we can get it closer to what we want. One of the biggest advantages of PowerShell is less “pointy clicky”. Make the computer do the work for you.
Plain export to Excel. (click image for full size)
Text
Plain export to Excel.

Add Conditional Text

We are upgrading to SQL 2019 across the estate and where possible upgrading to Windows 2019 Server. If we have a stand alone server running SQL 2016/Windows 2016 we can upgrade SQL in place. Let’s highlight our SQL 2019 Adding some conditional formatting is as easy as creating a conditional text object using New-ConditionalText

    
        
$text = New-ConditionalText '2019'
Export-Excel -ConditionalFormat $text
    

You can see here that without any extra, Export-Excel will highlight ‘2019’ everywhere in the imported range.

Add conditional text rule and sort. (click image for full size)
Text
Add conditional text rule and sort.

Add More Conditions

If we want to limit where the highlighting occurs, we need to add a couple more parameters to New-ConditionalText. To get the SQL 2019 instances highlighted in the default color (Dark Red on Light Pink) and only match in the “SQL Version” column we add the -Range switch and give it “B:B” which tells Excel all of column B. Next we’ll create another text rule with -Range of “H:H”. The -Text, -ConditionalTextColor and -BackgroundColor are ordered parameters so you can use them as the first, second and third options with out naming them.

    
        
$text1 = New-ConditionalText '2019' -Range "B:B"
$text2 = New-ConditionalText '2019' Green Yellow -Range "H:H"
Export-Excel -ConditionalFormat $text1,$text2
    
Here’s the result. I sorted descending on the “SQL Version” column. This makes it nice and easy to see which SQL 2019 servers are still running on Windows 2016.
Multiple conditions and sorted. (click image for full size)
Text
Multiple conditions and sorted.

Final Thoughts

With the handful of cmdlets we’ve used today, you can very quickly generate spreadsheets that you can give to your manager. Managers ❤️ spreadsheets! You can also cut and paste from Excel into email so you just get the formatting. Get a nice looking email instead of a bunch of plain text.

If you want to learn more about Doug Finke’s ImportExcel module, go check his github repo here (ImportExcel).

Thanks for reading!