Extended events get description

Useful query to get basic overview of extended events, actions and targets and their description.

SELECT p.name AS [Package-Name], o.object_type, o.name AS [Object-Name],  o.description AS [Object-Descr], p.guid AS [Package-Guid]
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects  AS o ON p.guid = o.package_guid
WHERE o.object_type = 'event'
SELECT p.name AS [Package-Name], o.object_type, o.name AS [Object-Name], o.description AS [Object-Descr], p.guid AS [Package-Guid]
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects  AS o ON p.guid= o.package_guid
WHERE o.object_type ='action'
SELECT p.name AS [Package-Name], o.object_type, o.name AS [Object-Name], o.description AS [Object-Descr], p.guid AS [Package-Guid]
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects  AS o ON p.guid = o.package_guid
WHERE o.object_type = 'target'

Extended events query plan

I need to cover few scenarios related to SQL statements or procedures and their Execution plans and IO statistics:

  • To monitor stored procedures performance running on server
  • To get execution plan from procedures not easily accessible from management studio
    • Procedures called from Service Broker applications
    • Complicated access to execution plan (Huge nested level of SQL statement – cursors)
  • Compare batches in testing labs

I decided to use Extended Events as lightweight solution for such a scenario.

Connect to Reporting Services with PowerShell

In this post I will show you how to make connection to Reporting Web Service Endpoint trough PowerShell. Report Web Service contains following endpoints you can work with:

  • management endpoints
  • execution endpoints
  • SharePoint Endpoints

You can find more info on Microsoft Website https://bit.ly/2LmkNhg. In my post series I will use management endpoint ReportService2005.asmx. There is ReportingService2006.asmx which is designed for developers working in SharePoint mode and ReportingService2010.asmx designed for either SharePoint or native mode.


List Reporting Services respository through PowerShell commands.


There are no extra prerequisites since PowerShell is part of .NET framework installed on most of Windows platforms by default. In case you would like update your PowerShell framework go to MSDN https://bit.ly/2xX3F92

For listing Reporting Services repository, we need two rows of commands put to your PowerShell console.

$proxy = New-WebServiceProxy "http://<yourservername>/Reportserver/ReportService2005.asmx" -Namespace SSRS.ReportingService2005 -UseDefaultCredential
  • The first command uses new-webserviceProxy cmdlet returning Web service proxy object. It allows us to use all proxy methods, SSRS methods in our scenario.
  • In the second command we use to call Web service methods, List Children in our case

After putting these two commands to PowerShell console you will see list of Reporting Services repository items as output in PowerShell console.

You can write easily PowerShell script, to make the solution reusable and configurable like the example bellow.  PowerShell is powerful and fast tool for writing scripts that help you automatize your tasks and you do not need to be an experienced developer. Enjoy

Let’s put basic setup to configuration xml file.

<Credentials UserName="">DefaultCredential</Credentials>

Script with function to get data from configuration file and function to connect to SSRS Web Service.

function SSRS-Get-Config-Data ($ConfigPath){
[xml] $xml|out-null 
try {
 $xml= [xml](Get-Content $ConfigPath)
catch {
  Write-Host "Cannot read file" $Error[0]
[string] $address|out-null
[string] $credentials|out-null
[string] $credentialName |out-null
try {
    $address= (Select-Xml '//Configuration/SSRSSoap/Address' $xml).node.innertext
    $credentials= (Select-Xml '//Configuration/SSRSSoap/Credentials' $xml).node.innertext
    $credentialName=( Select-Xml '//Configuration/SSRSSoap/Credentials' $xml).node.UserName #.node.Attributes[0].Value
catch {
 Write-Host "Wrong file format" $Error[0]
try {
if ($credentials -eq "DefaultCredential" )
 $proxy = New-WebServiceProxy -Uri $address -Namespace  SSRS.ReportingService2005 -UseDefaultCredential;
if ($credentials -eq "Credential" ) {
    $proxy = New-WebServiceProxy -Uri $address -Namespace  SSRS.ReportingService2005 -Credential $credentialName;
Write-Host "Cannot connect to web service" $Error[0]
return ,$proxy;


function ConnectToWebService ($ConfigPath)
$proxy = SSRS-Get-Config-Data $ConfigPath
if ($proxy.GetType().Name -eq "ReportingService2005" ) # check for type
 return $proxy


$scriptDir = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
. $scriptDir\function.ps1

$proxy = ConnectToWebService ".\config.xml"