Reports and Queries

This topic provides an overview of the access and use of Privilege Manager Reports and Queries.

Privilege Manager executes SQL queries to produce reports. You can view existing (or canned) queries and generate resolved queries for testing purposes.

You also can run existing and custom reports external to the application using SQL Server Reporting Services, SQL Server Management Services, or a preferred tool.

View Existing Privilege ManagerReports

Reports, accessible via the Privilege Manager left navigation pane, provides a categorized list of these items; in addition, Select Report Options allows you to hide or reveal reports by manipulating the switch associated with each.

You can view these reports by navigating to Admin | Folders and selecting the Reports tab, expanding to view the folder tree.

For example, Application Justification Summary Details Report is accessible via Reports | Resource Reports | Resource List Reports | Application Control | Data Class Reports.

Determine a Report's SQL Query Object

Each Privilege Manager report is a single XML object that references a separate XML object that contains the SQL query. By viewing the report object’s XML, you can determine the SQL query object.

To view the report as an XML object, change the URL from:

[Your_TMS_URL]/PrivilegeManager/#/item/view/9ba09fa5-ea7e-4352-8400-8eb58b8e41f9

to:

[Your_TMS_URL]/PrivilegeManager/#/item/xml/9ba09fa5-ea7e-4352-8400-8eb58b8e41f9

Viewing an XML item helps determine the folder location, as detailed below. Viewing a report as XML also reveals the XML object for the SQL query.

Use your mouse to hover over the GUIDs in the XML, which displays the name of each GUID's object. Within the section for ChildAssociations, there is an Association for the report's Data Source. Hovering over the GUID for the AssociatedItemId reveals the report query name.

In the screenshot below, hovering over the GUID (9a3d82a3-c7be-47cc-aa1c-48acc7964620) identifies that Item as the Application Justification Summary Details Report Query.

Clicking this GUID opens the XML for the query object in another tab on this screen:

The XML object for the query includes the direct SQL query that the application runs. However, viewing the query in Privilege Manager provides more reliable query results.

View a SQL Query in Privilege Manager

You can view the Privilege Manager SQL queries via Admin | Folders; however, it is helpful to know the folder location for specific queries. In the XML object for the query, hover over the GUID associated with the FolderId and select.

This action opens the XML folder that contains the query.

Click FolderId to open the XML for its parent folder and continue until reaching the root folder, which will not have a FolderId attribute. For the SQL queries, the root folder is Queries.

This XML view now displays the full folder location of this query: Queries | Report Queries | Application Control.

Access and Edit a Query from the Folder View

Navigate to Admin | Folders and select the Reports tab. From the View drop-down list box, select Queries. Navigate the folder structure determined above: Queries | Report Queries | Application Control. Select the Application Justification Report Query from the center pane.

View this query object. The Query tab displays the SQL query that the application runs. This is the same query that appears in the XML of the object.

Scroll to the lower section of the page to edit the query XML.

Resolved Query

The Resolved Query tab provides queries you can use directly on the database to return similar results that the application receives when it runs the query in the object – facilitating your ability to run or customize queries in SQL Server Reporting Services.

On the Resolved Query tab, sliding the Show as Anonymous Block switch to the right or Yes position assigns values to the parameters the query uses. From the Parameter Set drop-down list box, select Test to assign the parameters with appropriate values to run this query directly on your database.

Click Copy To Clipboard and then paste the resolved query in SQL Server Reporting Services, SQL Server Management Services, or your preferred tool.

Results

The Results tab provides options to change query information.

You can change the Parameters and enter specific item Ids.