Skip navigation

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. 

 

 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:

 

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!

Overview

This may or may not work on RSA IGL v7.2 depending on the installation options. For example, in 7.2 we allow installing the application under a different folder than the previously standard /home/oracle.

So I wouldn't recommend using it on 7.2 yet.

Full backups are a crucial measure for RSA Identity Governance & Lifecycle or generally any other enterprise application. Your organization probably has a strict backup policy which would mention aspects such as:

  • Backup frequency
  • Backup contents
  • Backup storage location
  • Backup retention

 

On the other hand, scheduling full backups for RSA Identity Governance & Lifecycle following our best practices could be challenging. You would need to take into consideration:

  • Backing up all important components (Database, Encryption Keys, Keystores?)
  • On highly active environments, it is recommended to schedule the database backup while the application is down to ensure a consistent backup.
  • Removing old backup files so as not to fill up the file system.
  • Notifying the system administrator of any backup failure.

 

RSA Professional Services created the attached bash script backup.sh as an example of backup using our best practices and as a base for any further modifications required to meet your organization's backup policies. The script is split into separate functions to allow easily building extra functionalities on top of the existing ones. Currently the script will:

  1. Stop the Application (and AFX if exists).
  2. Perform a database backup.
  3. Start the Application (and AFX if exists).
  4. Compress the database backup + the database encryption keys + the application server keystores into a full backup file.
  5. Remove both database and full backup files older than a preset threshold.
  6. Perform checks on the database backup status and possibly notify the system administrator upon any failures.
  7. Possibly copy the full backup files to remote share.

 

Usage

 

The script must be run as oracle and is cronjob friendly. The usage is very simple, just make sure it has the correct execute permissions for the oracle user then run it as follows:

> ./backup.sh

 

By default the script creates the full backup files under /home/oracle/fullbackups and is configured to keep only the last 3 backup files. You can easily overwrite either of those value by setting the environment variables FULLBACKUP_DIR and OLD_BACKUPS_TOKEEP first. For example, using the following syntax:

> env OLD_BACKUPS_TOKEEP=10 FULLBACKUP_DIR=/home/oracle/my_full_backup_dir ./backup.sh

 

Restore Procedure

 

  1. Create a temporary restore directory anywhere. For example:
> mkdir /home/oracle/fullbackups_restore

 

  1. Extract the compressed file in that directory. For example:

 

> cd /home/oracle/fullbackups_restore
> tar -xvf /home/oracle/fullbackups/DailyBackup_20190602_120243.tar.gz

 

  1. List all files to make sure they were extracted correctly. Ideally there should be three file extensions:
    1. *.dmp -> Database backups.
    2. *.key -> Encryption keys.
    3. *.keystore -> Keystore files.

 

For example:

 

> cd /home/oracle/fullbackups_restore
> ls -l
   total 1059580
   -rw-r----- 1 oracle oinstall 1084977152 Jun  2  2019 Export_AVDB_avuser_DailyBackup_20190602_120243.dmp
   -rw------- 1 oracle oinstall         26 Mar 18  2019 9K9.key
   -rw------- 1 oracle oinstall         26 Mar 18  2019 Xmk.key
   -rw-r--r-- 1 oracle oinstall       4815 Apr  2  2018 aveksa.keystore
   -rw-r--r-- 1 oracle oinstall       5301 Mar 18  2019 server.keystore

               

  1. Stop AFX if exists, then the application. For example:

 

> afx stop
> acm stop

 

  1. Copy the database backup file to the default Export/Import directory, then restore the database backup.
    The backup file names are in the format Export_<DatabaseID>_<DatabaseUser><Tag>.dmp. You need use the -t <Tag> option to specify the backup file you want to import.

    For example, the tag value from Export_AVDB_avuser_DailyBackup_20190602_120243.dmp is _DailyBackup_20190602_120243 (including the leading _) as shown below:

 

> cp /home/oracle/fullbackups_restore/Export_AVDB_avuser_DailyBackup_20190602_120243.dmp /home/oracle/AveksaExportImportDir
> avdbimport -t _DailyBackup_20190602_120243

 

  1. Copy the .key files to the security directory and update their permissions to the expected value (rw- --- ---). For example:

 

> cp /home/oracle/fullbackups_restore/*.key /home/oracle/security
> chmod 600 /home/oracle/security/*

 

  1. Copy the .keystore files to the keystore directory. For example:
> cp /home/oracle/fullbackups_restore/*.keystore /home/oracle/keystore

 

  1. Start the application, then AFX if exists. For example:
> acm start
> afx start

 

  1. Once you confirm everything is fine, you can delete the temporary restore directory. For example:
> rm -rf /home/oracle/fullbackups_restore

 

We would really love to hear your feedback and you can always contact RSA Professional Services through your account manager if you are interested in adding more customizations to the backup script.

Jamie Pryer

Reporting on Reports....

Posted by Jamie Pryer Employee Dec 6, 2018

A common question we get asked... "How many reports are there within RSA IGL"

The answer: LOADS!

 

Within RSA IGL we have a LOAD of out the box (OTB) reports included and shipped as standard.

All these reports can be found in the UI, by going to “Reports/Tabular” then “Create Report” button at the top.

From here you can find a lot of OTB reports by using the “type” and “Template” dropdowns.

For example, if you wanted a report on all your orphan accounts, you would select “Account” and then “Orphaned Accounts” - thats it! really simple

 

If you want a simple way to see all the reports you have in the system overall, you can execute the following query, either within something like SQL Developer or create a new a report within the UI itself

 

Main Query:

Select * from V_LIST_REPORTS;

This table tells you all the reports you have in your enviroment, both OTB and any you have also created as well.

 

Steps to create this in the UI are as follows

Note: this takes <5minutes to complete

  1. Log in as an admin user or as a user who can create reports
  2. Go to “Reports/Tabular”
  3. Click “Create Report” button
  4. Give you report a title and some details so you know what its for in future.


  5. Click the “Query” tab at the top
  6. Add the following SQL query:
    (select *  from avuser.V_LIST_REPORTS)
    Note: makes sure you wrap you the SQL in parenthesis “(“ and “)”
  7. Click the “Columns” tab
  8. Select only the following columns in the right-hand pane called “Displayed Columns” – everything else should be moved to the right.
    • Name
    • Report Description
    • Report Title
    • Last Modified Date
  9.    Click on “preview” button at the bottom to check its worked.

    These are more nice to have changes, that might make the report look a bit better, in my opinion
  10. Go to the “Grouping and Sorting” tab
  11. Select “Report Type” under Grouping, so that we can see the groups of reports we have created



  12. Click the “style” button at the bottom of the row

  13. Select “slate”

  14. Click "OK" to save and exit your report

 

Thats it, all done!

sap2csv:  Simple and Free java JCo3 utility to output any SAP Table into a csv

Check it out on:  https://sourceforge.net/projects/sap2csv/

Features

  • Accept SAP JCo connection parameters and tablename
  • Outputs tablename.csv
  • Standard Output provides table description

sap2csv

 

See attached quick reference guide for SAP tables.

This document is aimed at users building request forms using the form controls and building block tools that are provided in the application. This content is meant to be used as a reference and complements the material included in the online help and product documentation.

 

Also included are the following examples (as exported XML configuration files) which demonstrate the use of the Javascript control to take specific actions:

  • Invoking custom code based on a change
  • Setting the default value for a Dropdown
  • Manipulating Dates

 

We hope that you will find these examples useful and can adapt/extend them for your specific needs in building Request Forms. More importantly, we encourage you to share some of your commonly-used such implementations as examples by responding to this post and attaching your exported configuration file. With your active participation we hope to develop/grow this example set for the benefit of all members in this RSA Via Lifecycle and Governance user community.