000033281 - Chart Query SQL Guidelines in RSA Via Lifecycle and Governance

Document created by RSA Customer Support Employee on Jun 15, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000033281
Applies ToRSA Product Set: Identity Management and Governance
RSA Product/Service Type: Appliance
RSA Version/Condition: 6.9.1, 7.x
 
IssueCharts can be developed for reporting in the UI under Reports > Charts and can be reused in a custom UI dashboard. Data for the chart is gathered with a SQL query, but there are a few issues that need to be kept in mind or normally valid SQL statements will produce an error or no data will display. This article contains guidelines and tips for avoiding problems when developing a Reports Chart Query.
Tasks
  1. When testing under Oracle SQL Developer or another query tool, connect to the database with user ACMDB. The UI reports engine uses ACMDB for the database connection. If you use AVUSER, the tables described in the Public Database Schema Reference Guide will need a prefix added of PV_ (for example, USER becomes PV_USER); but these schema names will not be found when run from the Reports Query tab.
  2. Surround the entire SQL query with parenthesis and embed as a subselect in the form (SELECT * FROM (your_query)). Sometimes just outer parentheses are sufficient but the outer select allows for ORDER BY and other SQL clauses that may cause issues.
Below is an example of your_query:
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


Formatting within the Chart 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
  )
)

Attachments

    Outcomes