Rendering SQL Reporting Reports with PowerShell

Article
05/16/2010

A couple of weeks ago I was playing around with the new PowerShell v2.0 cmdlet New-WebServiceProxy cmdlet and I found that you can also use this cmdlet for Rendering SQL Reports. Let’s start with some info on the New-WebServiceProxy cmdlet.

NAME New-WebServiceProxy

SYNOPSIS Creates a Web service proxy object that lets you use and manage the Web service in Windows PowerShell.

SYNTAX New-WebServiceProxy [-URI] <Uri> [[-Class] <string>] [[-Namespace] <string>] [<CommonParameters>] New-WebServiceProxy [-URI] <Uri> [[-Class] <string>] [[-Namespace] <string>] [-Credential <PSCredential>] [<CommonParameters>] New-WebServiceProxy [-URI] <Uri> [[-Class] <string>] [[-Namespace] <string>] [-UseDefaultCredential] [<CommonParameters>]

DESCRIPTION The New-WebServiceProxy cmdlet lets you use a Web service in Windows PowerShell. The cmdlet connects to a Web service and creates a Web service proxy object in Windows PowerShell. You can use the proxy object to manage the Web service.

A Web service is an XML-based program that exchanges data over a network, particularly over the Internet. The Microsoft .NET Framework provides Web service proxy objects that represent the Web service as a .NET Framework object.

Jeremy Pavleck has also blogged about using this Cmdlet together with OpsMgr in his blogarticle How to monitor Everything. Check it out for more options of using this Cmdlet.

So how can we use this Cmdlet with our OpsMgr Reports?

Let me give an example. I’ve tried in the past to find the number of OpsMgr licenses using PowerShell. Till now I failed badly. But let’s try to get the OpsMgr License Report info in PowerShell using the New-WebServiceProxy Cmdlet.

What info do we need to Render the OpsMgr Licenses Report?

SQL Report Server Name
Path to the Report
The Report Parameters

SQL Report Server Name

This is an easy one. This is just the name of your SQL Reporting Server.

Path to the Report

In our case this is the OpsMgr Licenses Report. You can find the Path to the OpsMgr Licenses Report by opening your Favorite Web browser and going to https://reportservername/reports

So the path to the License Report is "/Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.License"

The Report Parameters

The easiest way to find the correct parameters is by running the Report from the OpsMgr Console and Publish the Report. Now you can find the Report under My Reports.

Open the Report from your browser

Here you already see the parameters being used by the License Report, but you can even see the parameters and their Data Type if you go to Properties and then select Parameters.

Ok, now we know everything needed for Rendering the SQL License Report from PowerShell. Because I want to show the result of the Report in the PowerShell shell I want the data returned to be as clear as it can be, so that’s why I render the Report in a CSV format.

Our goal is to create a script that renders the OpsMgr Licenses Report from a PowerShell script and shows the result like this:

This shows that there are 2 standard OpsMgr Licenses needed in my demo environment. Keep in mind that the OpsMgr Licenses Report differentiates between Physical and Virtual Machines.

Let’s get scripting!

I started with this version of the RenderSQLReportFromPosh script

#requires -version 2.0 ############################################################################### # Render SQL Reports using PowerShell # This script is using the new Posh v 2.0 cmdlet New-WebServiceProxy # FileName: RenderSQLReportFromPosh.v1.000.ps1 # Authors: Stefan Stranger (Microsoft) # Help from: Jin Chen (Microsoft) # Example of Rendering the OpsMgr Licenses Report from the Generic Report Library # # v1.000 – 15/05/2010 - stefstr - initial sstranger's release ############################################################################### #Define Variables #Enter URI to asmx file on Report Server $URI = "https://opsmgrrms.stranger.local//ReportServer//ReportExecution2005.asmx?wsdl" #Enter Report Path $ReportPath = "/Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.License" $format = "csv" $deviceinfo = "" $extention = "" $mimeType = "" $encoding = "UTF-8" $warnings = $null $streamIDs = $null $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential -namespace "ReportExecution2005" $rsExec = new-object ReportExecution2005.ReportExecutionService $rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials #Set ExecutionParameters $execInfo = @($ReportPath, $null) #Load the selected report. $rsExec.GetType().GetMethod("LoadReport").Invoke($rsExec, $execInfo) | out-null #Report Parameters #Depending on the number of Parameters being used in the Report you need to add more Parameters. #Search the rdl file for the correct parameter names. $param1 = new-object ReportExecution2005.ParameterValue $param1.Name = "ManagementGroupId" $param1.Value = "MGT01" $param2 = new-object ReportExecution2005.ParameterValue $param2.Name = "StartDate_BaseType" $param2.Value = "Today" $param3 = new-object ReportExecution2005.ParameterValue $param3.Name = "StartDate_BaseValue" $param3.Value = "04-21-2010" #Use US Date time! $param4 = new-object ReportExecution2005.ParameterValue $param4.Name = "StartDate_OffsetType" $param4.Value = "Day" $param5 = new-object ReportExecution2005.ParameterValue $param5.Name = "StartDate_OffsetValue" $param5.Value = "-1" $param6 = new-object ReportExecution2005.ParameterValue $param6.Name = "EndDate_BaseType" $param6.Value = "Today" $param7 = new-object ReportExecution2005.ParameterValue $param7.Name = "EndDate_BaseValue" $param7.Value = "04-21-2010" $param8 = new-object ReportExecution2005.ParameterValue $param8.Name = "EndDate_OffsetType" $param8.Value = "None" $param9 = new-object ReportExecution2005.ParameterValue $param9.Name = "EndDate_OffsetValue" $param9.Value = "0" $param10 = new-object ReportExecution2005.ParameterValue $param10.Name = "TimeZone" $param10.Value = "C4FFFFFF00000000C4FFFFFF00000A0000000500030000000000000000000300000005000200000000000000|W. Europe Standard Time" $param11 = new-object ReportExecution2005.ParameterValue $param11.Name = "TimeZoneName" $param11.Value = "(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna" $param12 = new-object ReportExecution2005.ParameterValue $param12.Name = "Interactive" $param12.Value = "False" $parameters = [ReportExecution2005.ParameterValue[]] ($param1,$param2,$param3,$param4,$param5,$param6,$param7,$param8,$param9,$param10) #Set ExecutionParameters $ExecParams = $rsExec.SetExecutionParameters($parameters, "en-us"); $render = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs) $Result = [text.encoding]::ascii.getString($render) #Write the Result to the PowerShell shell $Result

Result in PowerShell:

This is looking good! But as you see there is still some info we are not really interested in, like when the report is generated, which I want to remove from the result. And we also want to be able to do some sorting off course.

So in the next example script I added formatting to have a cleaner result after rendering the OpsMgr Licenses Report.

 #requires -version 2.0            ###############################################################################            # Render SQL Reports using PowerShell            # This script is using the new Posh v 2.0 cmdlet New-WebServiceProxy            # FileName: RenderSQLReportFromPosh.v1.001.ps1            # Authors: Stefan Stranger (Microsoft)            # Help from: Jin Chen (Microsoft)            # Example of Rendering the OpsMgr Licenses Report from the Generic Report Library            #             # v1.000 – 15/05/2010 - stefstr - initial sstranger's release            # v1.001 - 16/5/2010  - stefstr - added formatting            ###############################################################################                         #Define Variables            #Enter URI to asmx file on Report Server            $URI  = "https://opsmgrrms.stranger.local//ReportServer//ReportExecution2005.asmx?wsdl"            #Enter Report Path            $ReportPath = "/Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.License"            $format = "csv"            $deviceinfo = ""            $extention = ""            $mimeType = ""            $encoding = "UTF-8"            $warnings = $null            $streamIDs = $null            $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential -namespace "ReportExecution2005"            #Temporay File to store the Result of the CSV rendering. It turned out that formatting the Result in memory was not easy.           $TempFile = "d:\temp\render.txt"                               $rsExec = new-object ReportExecution2005.ReportExecutionService            $rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials                         #Set ExecutionParameters            $execInfo = @($ReportPath, $null)                         #Load the selected report.            $rsExec.GetType().GetMethod("LoadReport").Invoke($rsExec, $execInfo) | out-null                                     #Report Parameters            #Depending on the number of Parameters being used in the Report you need to add more Parameters.            #Search the rdl file for the correct parameter names.            $param1 = new-object ReportExecution2005.ParameterValue            $param1.Name = "ManagementGroupId"            $param1.Value = "MGT01"                         $param2 = new-object ReportExecution2005.ParameterValue            $param2.Name = "StartDate_BaseType"            $param2.Value = "Today"                         $param3 = new-object ReportExecution2005.ParameterValue            $param3.Name = "StartDate_BaseValue"            $param3.Value = "04-21-2010" #Use US Date time!                         $param4 = new-object ReportExecution2005.ParameterValue            $param4.Name = "StartDate_OffsetType"            $param4.Value = "Day"                         $param5 = new-object ReportExecution2005.ParameterValue            $param5.Name = "StartDate_OffsetValue"            $param5.Value = "-1"                         $param6 = new-object ReportExecution2005.ParameterValue            $param6.Name = "EndDate_BaseType"            $param6.Value = "Today"                         $param7 = new-object ReportExecution2005.ParameterValue            $param7.Name = "EndDate_BaseValue"            $param7.Value = "04-21-2010"                         $param8 = new-object ReportExecution2005.ParameterValue            $param8.Name = "EndDate_OffsetType"            $param8.Value = "None"                         $param9 = new-object ReportExecution2005.ParameterValue            $param9.Name = "EndDate_OffsetValue"            $param9.Value = "0"                         $param10 = new-object ReportExecution2005.ParameterValue            $param10.Name = "TimeZone"            $param10.Value = "C4FFFFFF00000000C4FFFFFF00000A0000000500030000000000000000000300000005000200000000000000|W. Europe Standard Time"                         $param11 = new-object ReportExecution2005.ParameterValue            $param11.Name = "TimeZoneName"            $param11.Value = "(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna"                         $param12 = new-object ReportExecution2005.ParameterValue            $param12.Name = "Interactive"            $param12.Value = "False"                         $parameters = [ReportExecution2005.ParameterValue[]] ($param1,$param2,$param3,$param4,$param5,$param6,$param7,$param8,$param9,$param10)                         #Set ExecutionParameters            $ExecParams = $rsExec.SetExecutionParameters($parameters, "en-us");                         $render = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)                         $Result = [text.encoding]::ascii.getString($render)                         #Write $Result to temp file.            #Was not able to remove first 7 lines in memory.            $Result > $TempFile                        #Get Content Temp File            $FinalResult = Get-Content -path $TempFile                        #Remove Temp File            Remove-Item $TempFile                        #Remove first 6 lines from $result. Depends on Report being run.            $Result = @($FinalResult[6..$FinalResult.Length])                                    #Format the results            $header, $data = $Result # we use multiple assignments to separate each of the sections into it's own variable                        #Find out how wide the fields are by splitting the string on comma's            $first, $second,$third = $header.split(",") |                 foreach { $_.length }                $second+=$first                        #Reformat the headers.            $h1,$h2,$h3 = $header.split(",") #Remark: Having the same column names is an issue so change the Name to License                       #Change the first Column Name to License            $h1 = "License"                        $data | foreach {                $v1, $v2, $v3 = $_.split(",")                                New-Object psobject |                    add-member -pass -mem Noteproperty $h1 $v1 |                    add-member -pass -mem Noteproperty $h2 $v2 |                    add-member -pass -mem Noteproperty $h3 $v3            }

Let’s check again if we get the Result we hoped for.

Yabbadabbadoo! Now we can add more info if we want, but that’s up to you. Have fun rendering SQL Reports from PowerShell! Let me know about cool new PowerShell scripts and results rendering OpsMgr Reports.

Here are some sources I used when trying to get PowerShell rendering SQL Reports.

Rendering SQL Reporting Reports with PowerShell

Additional resources