Guidelines for writing Report Chart SQL Queries in RSA Identity Governance & Lifecycle
Originally Published: 2016-06-07
Last Modified: 2022-06-20
Article Number
Applies To
RSA Version/Condition: All
Issue
Resolution
- When testing the SQL query in Oracle SQL Developer or another query tool, connect to the database with user ACMDB. The RSA Identity Governance & Lifecycle report engine uses ACMDB for the database connection. To view the same information in the AVUSER schema, prefix the tables described in the RSA Identity Governance & Lifecycle Public Database Schema Reference with PV_. For example, table USER would be PV_USER.
NOTE: This is also true for Tabular Reports (Reports > Tabular).
- When creating/editing the query in a Report Chart, because the report engine uses the ACMDB schema, to use tables in the AVUSER schema, they need to be both prefixed with PV_ and with the AVUSER username. For example, table USER would become AVUSER.PV_USER in the query.
NOTE: This is also true for Tabular Reports (Reports > Tabular).
- Surround the entire SQL query with parenthesis and embed the query as a sub-select statement in the form (SELECT * FROM (your_query)). Sometimes only the outer parentheses are sufficient but the outer select allows for ORDER BY and other SQL clauses that may cause issues so it is a better practice to embed yoiur query in its own set of parentheses.
EXAMPLE: (connected as user ACMDB)
Query tested in SQL Query tool:
Query tested in SQL Query tool:
SELECT
current_state AS state,
count(1) AS TOTAL_COUNT
FROM
change_request cr
WHERE
cr.request_date_no_time >= trunc(current_date) - 30
GROUP BY
current_state
ORDER BY current_state
Query reformatted for a Report Chart Query (Reports > Charts > {Chart name} > Edit > Query tab)
(SELECT * FROM
(
SELECT
current_state AS state,
count(1) AS TOTAL_COUNT
FROM
change_request cr
WHERE
cr.request_date_no_time >= trunc(current_date) - 30
GROUP BY
current_state
ORDER BY current_state
)
)
Related Articles
How to list RSA SecurID tokens updated and assigned in last X days using custom SQL query 37Number of Views Weblogic agent (identity asserter) does not allow cookie name other than CTSESSION 21Number of Views RSA Governance & Lifecycle Varonis Implementation Blueprint 35Number of Views Configure Automatic Enablement for a Risk-Based Authentication Policy 5Number of Views SQL Query to obtain directly assigned entitlements for a user 37Number of Views
Trending Articles
RSA Authentication Manager 8.9 Patches and Hotfixes Readme RSA MFA Agent 2.5 for Microsoft Windows Installation and Administration Guide RSA-2026-24: RSA Authentication Manager Security Update for Third-Party Component Vulnerabilities IDR SSO - Step 3: Deploy the Identity Router RSA Authentication Manager 8.9 Setup and Configuration Guide
Don't see what you're looking for?