SCOM – Use SQL Server Profiler to capture SQL queries used by the SCOM console

There have been many scenarios where a customer has asked what query is being used in a report, dashboard, view, etc. in the SCOM console.  This information can be useful for many reasons, but in particular, I find that I often utilize it for reverse engineering reports and queries and for troubleshooting.  There is a fairly simple way to obtain this information using SQL Server Profiler traces…

In this particular example, I am going to run a trace to find the query a console executed report is using…

1.  Begin the SQL Profiler trace:

a.  Locate SQL Server Profiler

b.  Select File –> New Trace

c.  Connect to the Instance where the OperationsManagerDW database resides

d.  Per the screenshot below, to make reading the trace easier you can filter on the account being used to execute reports by entering it in the “LoginName” option of the “Column Filter”.

Tip:  You can find the account you are using to execute reports by logging into the SQL Reporting Configuration tool on  your reporting server and selecting the “Execution Account” option.

profiler

e.  Once you enter your filter values, select “Run” to begin the trace

2.  Run the report from the SCOM console to capture the query being used by the report

3.  Stop the SQL Server Profiler trace and locate the sproc by searching for the reporting user account

In my case, I can see that the report is calling a stored procedure called “Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertReportDataGet” (see below).

ProfilerTrace

Additionally, this same process can be used to capture queries used by regular console views, tasks, dashboards, etc., or anything else executed against the database where the trace is being run.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s