6 minutes
From SQL to Excel with PowerShell

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.
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.


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 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.


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!

Comments powered by Talkyard.