Getting SSRS Details via Powershell

josh smith included in SQL Server Reporting Services

2019-01-09 187 words One minute

Contents

This is one of those posts so I never have to google this again (one hopes). Here is the PS code to pull back a set of details about every SSRS instance installed on a server including the SSRS instance name, & the first URL port it is running on, the service name and the name of the report server database etc.

$servername = 'myserver'
$key = "Software\\Microsoft\\Microsoft SQL Server"
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $server)
$regKey = $reg.OpenSubKey($key)
$keys = $regKey.GetSubKeyNames()

$v = 0
foreach($k in $keys)
{
    if($k -match 'MSRS[\d\d].' -and $k -notcontains '\@'){
        $pv = $k.Substring(4, 2)

        if ($v -le $pv){
            $v = $pv
            $rs = "RS_" + $k.Substring($k.IndexOf('.') + 1)
        }
    }
}

$nspace = "root\Microsoft\SQLServer\ReportServer\$rs\v$v\Admin"
$RSServers = Get-WmiObject -Namespace $nspace -class `
    MSReportServer_ConfigurationSetting -ComputerName $servername `
    -ErrorVariable perror -ErrorAction SilentlyContinue

foreach ($r in $RSServers){
      #https://docs.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/msreportserver-configurationsetting-members?view=sql-server-2017
    $ssrsHost = $r.InstanceName
    $ssrsVers = $r.version
    $ssrsDB = $r.DatabaseName
    $ssrsShare = $r.IsSharePointIntegrated
    $ssrsService = $r.ServiceName
    $vPath = $r.VirtualDirectoryReportServer
    $urls = $r.ListReservedUrls()
    $urls = $urls.UrlString[0]
    $urls = $urls.Replace('+', $servername) + "/$vPath"
    
    # NOW do a thing with this specific SSRS instance details
    $ssrsHost
    $ssrsVers
    $ssrsDB         
    $ssrsShare
    $ssrsService
    $vPath
    $urls
}