SecurID® Governance & Lifecycle Recipes
Reports, Charts and Dashboards are a useful and powerful feature of RSA Identity Governance and Lifecycle.
However, it would seem that many are not using this to its full potential...so we are here to help change that!
I've created this video and presentation, to help you understand the fundamentals of how this feature of the product works, along with a real example you can apply yourself, in your environment.
For more advanced next steps, please see this content: Dynamic Reports & Charts - What are they and how do you use them?
Please watch the video to learn more and leave you thoughts/comments below!
Hit "like" above if you found this useful!
Zoom Video Link --> RSA Reports, Charts and Dashboards - Overview and Live Demo - Zoom
Other Useful Links:
- Use the public schema guide for your IGL version.
- eg. v7.1.1 found here: RSA Identity Governance and Lifecycle 7.1.1 Public Database Schema Reference
- Another blog on how to find all the "OTB" (Out The Box) reports: https://community.rsa.com/community/products/governance-and-lifecycle/blog/2018/12/06/reporting-on-reports
- Some of the changes made to reports in v7.x of RSA IGL: https://community.rsa.com/community/products/governance-and-lifecycle/blog/2016/09/27/new-feature-reporting
Example of what is created in the video
Useful SQL for finding tables you might need
Replace "xxx" with a lower case search, like "user" or "app" or "role"
select * from user_views
where lower(view_name) like '%xxx%'
order by view_name asc
SQL used in the video and presentation.
Please note that this SQL might not be relevant for your environment, so always test this in Dev before testing in Production. Some of this SQL might produce too much data and so adding a date filter (e.g.. in the last year) might be useful. The below is shared to give you examples of the SQL which was used in the demo and presentation/video.
Active Review Details (Report)
(SELECT
pR.NAME,
pR.END_DATE,
pRS.REVIEWED_ENTS_COUNT,
pRS.TOTAL_ENTS_TO_REVIEW,
pRS.REVIEWED_USERS_COUNT,
pRS.TOTAL_USER_COUNT,
concat(CAST(((pRS.REVIEWED_ENTS_COUNT/pRS.TOTAL_ENTS_TO_REVIEW) *100) AS NUMERIC(10,0)),'%') AS Percent_Complete
FROM AVUSER.PV_REVIEW pR
LEFT JOIN AVUSER.PV_REVIEW_STATUS pRS
ON pR.ID = pRS.REVIEW_ID
WHERE pR.STATE = 'InProcess')
Outstanding Review Items (Report)
(SELECT * FROM
(SELECT R.NAME AS Review_Name,
U.FIRST_NAME,
U.LAST_NAME,
U.DEPARTMENT,
TOTAL_ENTS_TO_REVIEW AS Total_Review_Items,
REVIEWED_ENTS_COUNT,
CAST((REVIEWED_ENTS_COUNT /TOTAL_ENTS_TO_REVIEW) AS NUMERIC(10,2)) AS Input_Pct,
concat(CAST(((REVIEWED_ENTS_COUNT/TOTAL_ENTS_TO_REVIEW) *100) AS NUMERIC(10,0)),'%') AS Percent_Complete
FROM avuser.PV_REVIEW R
JOIN avuser.PV_REVIEW_REVIEWER_SUMMARY REVIEWER_STAT_BY_COMP
ON R.ID = REVIEWER_STAT_BY_COMP.REVIEW_ID
JOIN avuser.PV_USERS U
ON REVIEWER_STAT_BY_COMP.REVIEWER_ID = U.ID
WHERE R.STATE= 'InProcess')
WHERE PERCENT_COMPLETE <> '100%'
ORDER BY TOTAL_REVIEW_ITEMS DESC)
Active Reviews (Chart)
(SELECT
pR.NAME,
pR.END_DATE,
CAST(((pRS.REVIEWED_ENTS_COUNT/pRS.TOTAL_ENTS_TO_REVIEW) *100) AS NUMERIC(10,0)) AS Percent_Complete
FROM AVUSER.PV_REVIEW pR
LEFT JOIN AVUSER.PV_REVIEW_STATUS pRS
ON pR.ID = pRS.REVIEW_ID
WHERE pR.STATE = 'InProcess')
Overall Review Summary (Chart)
(select
state as review_state,
count(*) as total_items
from AVUSER.PV_REVIEW
group by state)
Thanks!
please comment below and hit like if this is helpful!
