I saw this blog post about the SQL Server Diagnostics add-on to SSMS and API and thought I would write some PowerShell to work with it as all of the examples use other languages.

SQL ServerDignostics API

The Diagnostic Analysis API allows you to upload memory dumps to be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix.

There is also the Recommendations API to view the latest Cumulative Updates (CU) and the underlying hotfixes addressed in the CU which can be filtered by product version or by feature area (e.g. Always On, Backup/Restore, Column Store, etc).

I have written a module to work with this API. It is not complete. It only has one command as of now but I can see lots of possibilities for improvement and further commands to interact with the API fully and enable SQL Server professionals to use PowerShell for this.

Storing the API Key

To use the API you need an API Key. An API Key is a secret token that identifies the application to the API and is used to control access. You can follow the instructions here https://ecsapi.portal.azure-api.net/ to get one for the SQL Server Diagnostics API.

I will need to store the key to use it. I saved my API Key using the Export-CliXML command as described by Jaap Brasser here .

 Get-Credential | Export-CliXml -Path "${env:\userprofile}\SQLDiag.Cred" 

You need to enter a username even though it is not used and then enter the API Key as the password. It is saved in the root of the user profile folder as hopefully user accounts will have access there in most shops.

The commands in the module will look for the API Key in that SQLDiag.Cred file by default but you can also just use the APIKey parameter

Get-SQLDiagRecommendations

The first function in the module is Get-SQLDiagRecommendations. All this function does is connect to the Recommendations API and return an object containing the information about the latest Cumulative Updates.

If you have already saved your API Key as described above you can use

 Get-SQLDiagRecommendations 

If you want to enter the API Key manually you would use

 Get-SQLDiagRecommendations -APIKey XXXXXXXX

Either way it will return a PowerShell object containing all of the information which looks like this.

One of the beauties of PowerShell is that you can pass objects down a pipeline and use them in other commands. Also, your only limit is your imagination.

You want to export to CSV, HTML, Text file?

Email, Import to database, store in Azure storage?

Embed in Word, Excel  on a SharePoint site?

All of this and much, much more is easily achievable with PowerShell.

In the future this command will feed other functions in the module that will display this information in a more useful fashion. I am thinking of commands like

Get-SQLDiagRecommendations |
Get-SQLDiagLatestCU -Version SQL2012

or

Get-SQLDiagRecommendations |
Get-SQLDiagKBArticle -Version SQL2012 -Feature BackupRestore

If you have any ideas please join in on GitHub

JSON

For now though you can use Get-SQLDiagRecommendations to output the results to JSON so that you can examine them or consume them.

If you use VS Code follow the steps here and you can export the results to the current file with

 Get-SQLDiagRecommendations |ConvertTo-Json -Depth 7 |Out-CurrentFile 

Which looks like this

It shows the entire JSON object containing all of the information about all of the latest CU’s for SQL Server 2012 and up and each of the KB Articles. I have minimised several of the nodes to try and show as much as possible for SQL Server 2012 SP3

If you do not use VS Code or you want to export straight to a file then you can

 Get-SQLDiagRecommendations |ConvertTo-Json -Depth 7 |Out-File -Path PATHTOFILE 

Out-GridView

I like Out-GridView so I quickly gathered the Product, Cumulative Update, Feature Type, KB Number and URL and outputted to Out-GridView like this

$recommendations = Get-SQLDiagRecommendations
$KBs = foreach ($recommendation in $recommendations.Recommendations){
    $Product = $recommendation.Product
    $CU = $recommendation.Title
    $CreatedOn = $recommendation.CreatedOn
    foreach ($fix in $recommendation.Content.RelevantFixes){
        $feature = $fix.Title
        foreach ($Kb in $fix.KbArticles){
            [PSCustomObject]@{
                CreatedOn = $CreatedOn
                Product = $Product
                CU = $CU
                Feature = $feature
                KB = $Kb.Rel
                Link = $Kb.href
                }
           }
       }
   }
 $kbs | Ogv 

As you can filter easily in Out-GridView I filtered by 2012 and this is what it looks like

This will enable you to quickly see any information that you require about the Cumulative Updates for SQL 2012, 2014 and 2016

Github

You can find the module on GitHub. There are instructions and a script to install it easily.

Right now it has only got the one function to get the SQL recommendations but I will look at expanding that over the next few days and once it is more complete put it onto the PowerShell Gallery and maybe move it into the SQL Server Community GitHub Organisation  home of https://dbatools.io , https://dbareports.io, Invoke-SQLCmd2 and the SSIS Reporting pack

Contribute

Of course I am happy to have others contribute to this, in fact I encourage it. Please fork and give PR’s and make this a useful module with more commands. There is the Diagnostic Analysis API as well to work with and I am very interested to see how we can make use of that with PowerShell

Tomorrow I have a post explaining the process I used to create the module and how I used Test Driven Development with Pester to write this function.