Executive Summary
As service manager in your company, you want to have your MS SQL component patch with the latest version. In a regular basis you need to know the current version of the MS SQL components on your server. In this blog I will focus on the component Power BI Report Server.
Typically, the following services are in the responsibility of a MS SQL service manager: MS SQL Server, Analysis Services , Integrated Services, Reporting Services (SSRS) and Power BI Reporting Services. The DBA can determine installed version of the software by running the command in Listing 1. The result from that command is comparable with the result presented in the Configuration Manager of the MS SQL component. Except for Power BI and Reporting Services as you can see in the screenshot 1 below.
### Listing 1
Get-SilSoftware
In picture 1 you can notice that there is a MS SQL 2022 in this test environment. The version information for the SQL Server is shown correctly. For Power BI the number in the result in picture 1 from the PowerShell command represent the Build number. The value required by the service manager is the one displayed in the configuration manager user interface (picture 2).
The question is, how to get this value in a reliable automatic way? How to get it in a PowerShell script? That is what I will shared with you in this blog. And it is important to notice that, only “standard” PowerShell modules are used. No addition PowerShell library is needed.
😉 Picture 2 should normally show the current installed version of Power BI Report Server. In my test installation, I did not check why the product version is empty in the configuration manager. I found even this fact good for my test. Because, I want to get the correct version by using a PowerShell script. That is what, I will show in the next step.
How to get the Product Version?
To get the version of our current Power BI Reporting Services, I use the registry key as documented here. This is a link on Reporting Services and as you may know, Power BI Reporting Services is fundamentally a SSRS. The commands are in listing 2.
# Listing 2
$ntsComputerName = ‘NTS_COMPUTER’
$ntsWmiObject = Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace -ComputerName $ntsComputerName |select Name
$ntsName = $ntsWmiObject.Name
$ntsWmiObject = Get-WmiObject -namespace "root\Microsoft\SqlServer\ReportServer\$ntsName" -class __Namespace -ComputerName $ntsComputerName |select Name
$ntsName = $ntsWmiObject.Name
### If you get an error, check the instance name and change it. RS_PBIRS
$ntsWmiObject = Get-WmiObject -namespace "root\Microsoft\SqlServer\ReportServer\RS_PBIRS\$ntsName" -class MSReportServer_Instance -ComputerName $ntsComputerName
$ntsName = $ntsWmiObject.Version
$ntsName
From MS SQL 2017, Name Instance of Reporting Services / Power BI Reporting Services are not possible anymore. But in older version, it was possible to use named instance. Listing 3 will retrieve the version of all named instances on a server.
#### Listing 3
## Replace "Named_Instance" with your instance name
$ntsWmiObject = Get-WmiObject -namespace "root\Microsoft\SqlServer\ReportServer\Named_Instance\$ntsName" -class MSReportServer_Instance -ComputerName $ntsComputerName
$ntsWmiObject.Version
Important Notice (for listing 3)
You should notice that the command in listing 3 retrieves the information for all install instances. Although you query a specific instance, you get the result of all instances. So you should consider removing duplicated entry from the final result in your implementation. The function in listing 4 take a server name as input and retrieves all the installations (also Named Instances) of Power BI/Reporting Services on this server. I will insist on it: remove duplicated value from the returned result if you want to use it.
# Listing 4
function Discover_SSRS{
param( [Parameter(Mandatory = $true)] [String] $ntsComputerName )
$ntsWmiObjects = Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace -ComputerName $ntsComputerName | select Name
$ntsDuplicatedWmiObjects = [System.Collections.ArrayList]::new()
foreach( $ntsWmiO in $ntsWmiObjects){
$ntsName = $ntsWmiO.Name
$ntsWmiObjectNamespaces = Get-WmiObject -namespace "root\Microsoft\SqlServer\ReportServer\$ntsName" -class __Namespace -ComputerName $ntsComputerName | select Name
foreach( $ntsWmiObjectNamespace in $ntsWmiObjectNamespaces){
$ntsNameV = $ntsWmiObjectNamespace.Name
$ntsDuplicatedWmiObject = Get-WmiObject -namespace "root\Microsoft\SqlServer\ReportServer\$ntsName\$ntsNameV" -class MSReportServer_Instance -ComputerName $ntsComputerName
$ntsDuplicatedWmiObjects.Add($ntsDuplicatedWmiObject) > $null
}
}
return $ntsDuplicatedWmiObjects
}
$returnArray = @( Discover_SSRS -ntsComputerName 'NTS_COMPUTER' )
Final words
The post shows the command to get the version of install Power BI or Reporting Services in your environment. I hope this will help you, not to lose track of the version of the installation software on your server.
#beSharing