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.
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).
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.