Skip navigation

RSA IGL Version: V7.1x & V7.2x

Modules: Governance

Product Area: Charts, Single Series

Note: A summary of all RSA IGL recipes can be found here: (TBC)

Time to apply: ~20 minutes

 

Please share your results and ideas below!

 

Summary:

This recipe shows you how to create a very simple chart, displaying the total number of leavers you have had each week, over the previous 10 weeks.

You can use this data to understand trending within your organisation and potentially look into how automation of the leavers process, could save you time/money

 

Blog Contents:

 

Prerequisites:

  • RSA IGL v7.1x or v7.2x
  • Identity data collections running, with some leavers in the environment 
Make sure you thoroughly test any changes in lower environments first before promoting them to Production.

 

Optional Changes to the Chart

If you want to change this chart to display more/less than 10 weeks, you need to update 2 key values in the SQL. 

  1. "CONNECT BY LEVEL <= 10" - the 10 here, is the number of weeks you want to display, so you could change this to be 8 or 15
  2. "where deletion_date > sysdate - 70 " - the 70 here, is the number of total days, depending on how many weeks you have selected.
    • So if you have 8 weeks, you would have 8weeks * 7 days = 56
    • So if you have 12 weeks, you would have 12weeks * 7 = 84

I would strongly recommended you do not have too many weeks, otherwise it will not display very well in the UI. 

 

The SQL to use:

Please test this works first, within your environment

(select * from 
(select
     T1.tmpWeek as "Week",
     CASE WHEN T2.TotalPerWeek IS NULL THEN CAST(('0') AS NUMBER(20)) ELSE CAST(T2.TotalPerWeek AS NUMBER(20)) END AS "# Leavers"
from
     (
     SELECT
          to_char(sysdate - (7 * level) +7,'YYYYMMDD') as FullDate,
          to_char(sysdate - (7 * level) +7,'WW') as tmpWeek
     FROM dual
     CONNECT BY LEVEL <= 10
     ) t1
left join
     (
     select
          'Leavers' as AppAction,
          tmpWeek,
          count(deletion_date) as TotalPerWeek
     from
          (
          select distinct
               ID,
               deletion_date,
               to_char(deletion_date,'WW') as tmpWeek
          from avuser.pv_users
          where deletion_date > sysdate - 70
          )
          group by tmpWeek
     ) t2
ON t2.tmpWeek = t1.tmpWeek
order by FullDate asc)
)

 

The output looks like this in my environment. Showing that there were 41 leavers in week 27

 

Steps to create within the RSA IGL UI

  1. Log into RSA IGL as a user who can create charts. In my example, im using AveksaAdmin
  2. Go to "Reports" / "Charts"
  3. Select "+ Create Chart" button
  4. Under the "General Tab" add the following details:
    • Name
    • Description
    • Type = Single Series Chart
  5. Under the "Query" Tab, copy the SQL from above and hit "Preview", you should see some results.

    If you get an error at this stage, please test your SQL in a Query tool, like "SQL Developer" or "SQL Squirrel" to ensure it works first. 
    If it still doesn't work, please share your SQL and a screen shot of the issue below.
  6. Under the "Columns" Tab, please use the configuration shown in the image below
  7. Under the "Display Attributes" tab, you should select "Style = Line". Please also apply these settings:
    • Under "Title and Axis Names"
      • Caption: "Total Leavers Per Week"
      • Sub Caption: "For the Previous 10 Weeks"
      • X Axis Name: "Week #"
      • Y Axis Name: Total Leavers
    • Under "Functional attributes"
      • Select "Animation" checkbox (ticked)
      • Select "Show Labels" (Ticked)
      • Select "Show Values" (Ticked)

There are MANY other "display attributes" you can play with on this screen, so please update and make changes as you see fit. 

Final Result

 

 

Next Steps

Add this chart to a dashboard, so you can easily see the data.

RSA IGL Version: V7.1x & V7.2x

Modules: Governance

Product Area: Charts, Multi Series

Note: A summary of all RSA IGL recipes can be found here: (TBC)

Time to apply: ~20 minutes

 

Please share your results and ideas below!

 

Summary:

This recipe shows you how to create a very simple multi-series chart, displaying the total number of joiners AND leavers you have had each week, over the previous 8 weeks.

You can use this data to understand trending within your organisation and potentially look into how automation of the joiners and leavers process, could save you time/money

 

Blog Contents:

 

Prerequisites:

Make sure you thoroughly test any changes in lower environments first before promoting them to Production.

 

Optional Changes to the Chart

If you want to change this chart to display more/less than 8 weeks, you need to update 3 key values in the SQL. 

  1. "CONNECT BY LEVEL <= 8" - the 8 here, is the number of weeks you want to display, so you could change this to be 5 or 15
  2. "where creation_date > sysdate - 56" - the 56 here, is the number of total days, depending on how many weeks you have selected.
    • So if you have 10 weeks, you would have 10weeks * 7 days = 70
    • So if you have 12 weeks, you would have 12weeks * 7 = 84
  3. "where deletion_date > sysdate - 56" - same as the point above

I would strongly recommended you do not have too many weeks, otherwise it will not display very well in the UI. 

 

The SQL to use:

Please test this works first, within your environment.

(select * from 
(select
     T1.tmpWeek as "Week",
     CASE WHEN T2.TotalPerWeek IS NULL THEN CAST(('0') AS NUMBER(20)) ELSE CAST(T2.TotalPerWeek AS NUMBER(20)) END AS "# Leavers",
     CASE WHEN T3.TotalPerWeek IS NULL THEN CAST(('0') AS NUMBER(20)) ELSE CAST(T3.TotalPerWeek AS NUMBER(20)) END AS "# Joiners"
from
     (
     SELECT
          to_char(sysdate - (7 * level) +7,'YYYYMMDD') as FullDate,
          to_char(sysdate - (7 * level) +7,'WW') as tmpWeek
     FROM dual
     CONNECT BY LEVEL <= 8
     ) t1
left join
     (
     select
          'Leavers' as AppAction,
          tmpWeek,
          count(deletion_date) as TotalPerWeek
     from
          (
          select distinct
               ID,
               deletion_date,
               to_char(deletion_date,'WW') as tmpWeek
          from avuser.pv_users
          where deletion_date > sysdate - 56
          )
          group by tmpWeek
     ) t2
ON t2.tmpWeek = t1.tmpWeek
left join
     (
     select  -- this will tell you how many accounts have been created per week
          'Joiners' as AppAction,
          tmpWeek,
          count(creation_date) as TotalPerWeek
     from
          (
          select distinct
               ID,
               creation_date,
               to_char(creation_date,'WW') as tmpWeek
          from avuser.pv_users
          where creation_date > sysdate - 56 --change this to be the same number of days, as per the total weeks selected
          )
          group by tmpWeek
     ) t3
ON t3.tmpWeek = t1.tmpWeek
order by FullDate asc)
)

 

The output looks like this in my environment. Showing that there were 1651 new joiners in week 23 of this year and 41 leavers in week 27 of the year

 

 

Steps to create within the RSA IGL UI

  1. Log into RSA IGL as a user who can create charts. In my example, im using AveksaAdmin
  2. Go to "Reports" / "Charts"
  3. Select "+ Create Chart" button
  4. Under the "General Tab" add the following details:
    • Name
    • Description
    • Type = Multiple Series Chart
  5. Under the "Query" Tab, copy the SQL from above and hit "Preview", you should see some results.

    If you get an error at this stage, please test your SQL in a Query tool, like "SQL Developer" or "SQL Squirrel" to ensure it works first. 
    If it still doesn't work, please share your SQL and a screen shot of the issue below.
  6. Under the "Columns" Tab, please use the configuration shown in the image below, however you can change this to be whatever you like. I think that using red for leavers and green for joiners, looks good.
  7. Under the "Display Attributes" tab, you should select "Style = MS Line". Please also apply these settings
    • Under "Title and Axis Names"
      • Caption: "Weekly Joiners and Leavers"
      • Sub Caption: "For the Previous 8 Weeks"
      • X Axis Name: "Week #"
      • Y Axis Name: Total
    • Under "Functional attributes"
      • Select "Animation" checkbox (ticked)
      • Select "Show Labels" (Ticked)
      • Select "Show Values" (Ticked)

There are MANY other "display attributes" you can play with on this screen, so please update and make changes as you see fit. 

Final Result

 

Next Steps

Add this chart to a dashboard, so you can easily see the data.

RSA IGL Version: V7.1x & V7.2x

Modules: Governance

Product Area: Charts, Single Series

Note: A summary of all RSA IGL recipes can be found here: (TBC)

Time to apply: ~20 minutes

Please share your results and ideas below!

 

Summary:

This recipe shows you how to create a very simple chart, displaying the total number of joiners you have had each week, over the previous 10 weeks.

You can use this data to understand trending within your organisation and potentially look into how automation of the joiners process, could save you time/money

 

Blog Contents:

 

Prerequisites:

  • RSA IGL v7.1x or v7.2x
  • Identity data collections running, with some new joiners in the environment 

 

Make sure you thoroughly test any changes in lower environments first before promoting them to Production.

 

 

Optional Changes to the Chart

If you want to change this chart to display more/less than 10 weeks, you need to update 2 key values in the SQL. 

  1. "CONNECT BY LEVEL <= 10" - the 10 here, is the number of weeks you want to display, so you could change this to be 8 or 15
  2. "where creation_date > sysdate - 70 " - the 70 here, is the number of total days, depending on how many weeks you have selected.
    • So if you have 8 weeks, you would have 8weeks * 7 days = 56
    • So if you have 12 weeks, you would have 12weeks * 7 = 84

I would strongly recommended you do not have too many weeks, otherwise it will not display very well in the UI. 

 

The SQL to use:

Please test this works first, within your environment. 

(select * from 
(select
     T1.tmpWeek as "Week",
     CASE WHEN T2.TotalPerWeek IS NULL THEN CAST(('0') AS NUMBER(20)) ELSE CAST(T2.TotalPerWeek AS NUMBER(20)) END AS "# Joiners"
from
     (
     SELECT
          to_char(sysdate - (7 * level) +7,'YYYYMMDD') as FullDate,
          to_char(sysdate - (7 * level) +7,'WW') as tmpWeek
     FROM dual
     CONNECT BY LEVEL <= 10
     ) t1
left join
     (
     select
          'Joiners' as AppAction,
          tmpWeek,
          count(creation_date) as TotalPerWeek
     from
          (
          select distinct
               ID,
               creation_date,
               to_char(creation_date,'WW') as tmpWeek
          from avuser.pv_users
          where creation_date > sysdate - 70
          )
          group by tmpWeek
     ) t2
ON t2.tmpWeek = t1.tmpWeek
order by FullDate asc)
)

 

The output looks like this in my environment. Showing that there were 1651 new joiners in week 23 of this year and 13 new joiners in week 27 of this year. 

 

 

Steps to create within the RSA IGL UI

  1. Log into RSA IGL as a user who can create charts. In my example, im using AveksaAdmin
  2. Go to "Reports" / "Charts"
  3. Select "+ Create Chart" button
  4. Under the "General Tab" add the following details:
    • Name
    • Description
    • Type = Single Series Chart
  5. Under the "Query" Tab, copy the SQL from above and hit "Preview", you should see some results.

    If you get an error at this stage, please test your SQL in a Query tool, like "SQL Developer" or "SQL Squirrel" to ensure it works first.
    If it still doesn't work, please share your SQL and a screen shot of the issue below.
  6. Under the "Columns" Tab, please use the configuration shown in the image below
  7. Under the "Display Attributes" tab, you should select "Style = Line". Please also apply these settings
    • Under "Title and Axis Names"
      • Caption: "Total New Joiners Per Week"
      • Sub Caption: "For the Previous 10 Weeks"
      • X Axis Name: "Week #"
      • Y Axis Name: Total New Joiners
    • Under "Functional attributes"
      • Select "Animation" checkbox (ticked)
      • Select "Show Labels" (Ticked)
      • Select "Show Values" (Ticked)

There are MANY other "display attributes" you can play with on this screen, so please update and make changes as you see fit. 

Final Result

 

 

Next Steps

Add this chart to a dashboard, so you can easily see the data.

RSA IGL Services 101 blogs, help to explain various areas of RSA Identity Governance and Lifecycle, to ensure you are getting the most out of the product and following recommended practices. We hope to show you lots of great features, tips and tricks that you may not have been aware of!

 

This blog provides a high level index and summary of each workflow node available, taken from v7.1x of RSA IGL. As we dive into more detail of each node, we will provide a link below, to click and get more info. For example, please click "Milestone" in the table below.

If there is a specific node you would like to know more about, please let us know in the comments below

Workflow Node Summary

The workflow editor includes processing nodes common to and also specific to request, approval, fulfillment, and escalation workflows. Nodes are the building blocks you use to create and modify workflows. This following table lists nodes that you can use in request, approval, fulfillment, and escalation workflows.

 

Node

Description

Activity

Used to define a activity for a change request.

Approval

Used to define an approval for a change request.

Approvals Phase

Used to allow change request items to be approved as groups at the same level.

Cancel Change Request

Used to generate a milestone to cancel the entire change request processed by the workflow and revert all changes completed in the change request, reject the entire change request processed by the workflow, or put the change request in an error state.

Complete Assigned

Used in an escalation workflow to mark work assigned to a user (through an approval or activity) as completed.

Create Admin Error

Specifies the type of admin error to create for an administrator.

Decision

Evaluates a condition(s) based on a true or false result for outgoing transitions to an action or stop delimiter based on whether or not the condition exists.

Delay

Suspends a workflow temporarily based on date criteria. The date could be a specific date, the change request fulfillment date, a system calculated date relative to current time, the result of a java method that returns a date, or the result of a SQL query resulting in a date.

Form Approval

Used to define an approval for a change request generated from a form.

Form Fulfillment

Used to define a fulfillment for a change request generated from a form.

Fulfillment Handler

Invokes a Java class to fulfill changes in a request.

Fulfillment Phase

Used to allow change request items to be fulfilled as groups at the same level.

Get Remaining SecondsUsed to store how much time remains for a calculated due date, performs some escalation outside of the assigned user’s control, and then updates the due date for the assigned user based on the earlier recorded remaining time.

Java *

Provides an interface to a Java method passing any parameters and returning a true/false result you can incorporate into a workflow.

The Java node, can evaluate conditions and perform actions in a workflow required for an approval and to initiate completion of an activity.

Note that if you use the Java node in a workflow or use the Java tag in workflow forms, you should place custom classes or jars in one of the following directories:

  • aveksa.ear/aveksa.war/WEB-INF/plugins/JavaNode/lib
  • aveksa.ear/aveksa.war/WEB-INF/plugins/JavaNode/classes

The sample Java Node workflow is deployed and references classes in these plugin directories. The source files for the samples are also included in the plugin directory under the src directory.

Job State

Specifies a job state the pauses a workflow: Canceled, Error, or Suspension

Manual Fulfillment

Used to handle a fulfillment manually and not automatically by the system.

Mark Verified

Used to indicate that changes marked as pending verification should be marked as verified.

Milestone

Provides high-level status information about a workflow milestone you want displayed in a change request.

Next Value

Returns the next value for a given job level workflow variable. If no value is returned (the last value was previously retrieved), the node returns false, which can be tested on an outgoing transition. If a valid value is returned, a true return code is provided. This node is typically used to iterate through an array of values to get the next value in the array..

Provisioning Command

Used to complete a provisioning command in a data source for a particular business source.

Reassign

Used to assign an approval or activity to another user.

Reset Password

Used to generate an email notification prompting a user to retrieve a password that has been reset for the user.

REST Web Service *

Invokes a REST call to an endpoint. The responses and results from the calls are stored in the workflow variables based on the configuration in the node. This information can be used in a workflow’s decision logic.

The node supports:

  • GET and POST methods
  • Basic authentication
  • Header parameters.
  • XML and Properties response types
  • Parsing of the response using XPath and RegEx expressions.

Run Report

Generates a report configured for the node in the workflow.

Run Review

Used to generate a user access review associated with the node.

Send Email

Generates email you want from the workflow. It supports the use of workflow variables or runtime workflow information to specify the To/From portions of the email.

Set Value

Creates or updates a job level workflow variable(s) using the value(s) provided. The value can be a literal or use other workflow variables that are evaluated at the time the node is executed

SOAP Web Service *

Invokes a SOAP call to an endpoint. The responses and results from the calls are stored in the workflow variables based on the configuration in the node. This information can be used in a workflow’s decision logic.

The node supports:

  • POST method
  • Basic authentication
  • WS-Security
  • Generic MIME Header
  • SOAP based XML response type
  • Parsing of the response using XPath and RegEx expressions

SQL Execute *

Runs an Insert/Update/Delete SQL command or a stored procedure where no result set is needed. It runs against the system database (AVDB). This node supports variables from the workflow with the SQL.

If you want to use an output parameter from your stored procedure (say, ‘success’ or “failure’ status) as a workflow variable for subsequent processing, you must define the stored procedure as a function and use the following syntax:

select sp_update_db (‘JOE’, ‘SMITH’, status) status from dual.

SQL Select *

To be updated.

Start

Used as the start delimiter for a workflow.

Stop

Used as the stop delimiter for a workflow.

Subprocess

Calls/interjects another workflow as a subprocess of the current workflow. This node is useful in compartmentalizing work items or to improve maintenance or re-use of workflows.

Text Node

Used to enter text into a workflow.

Transition

Used to connect two workflow nodes (processes) unidirectionally with a straight line. Transitions can be conditional or unconditional. A conditional transition occurs only if a particular condition is true. An unconditional transition can occur regardless of whether a condition is true. A transition is visually represented as an arrow.

Undo Changes

Used to generates changes to reverse the requested changes that have been fulfilled.

Wait for Verification

Used to create a database watch for evidence of a change request fulfillment.

 

Note: Not all controls and types are available for every workflow type. Also, nodes with an asterisk symbol ( * ) are designed for advanced application. These nodes should be implemented carefully because poorly defined nodes can negatively impact workflow performance.

Services 101 blogs, help to explain various areas of RSA Identity Governance and Lifecycle, to ensure you are getting the most out of the product and following recommended practices. We hope to show you lots of great features, tips and tricks that you may not have been aware of!

Please reply below with any questions or hit like if this is helpful!

We are starting by looking at workflow nodes and in this blog, specifically the "milestone" node. 

The RSA Services team love to use Milestone Nodes whenever possible and find they are a great addition to any workflow. However they are surprised to find they are not being used enough by our customers to help make things easier and clearer!

 

Thanks to the PS rockstars: Clive Morrish, Ahmed Nofal and Mostafa Helmy for their help on this blog.

Click the images to enlarge if you need!

Product Area: Workflow's

Note: A summary of all workflows is found here: RSA IGL Services 101: Explaining Workflow Nodes - Summary

Workflow Node: Milestones

Time to apply: <10 minutes

Impact: High positive impact for end users, Low risk to workflow process as nothing is being changed to effect the flow.

Summary: Using "Milestone" nodes, provide a great way to help track the route a workflow has taken and give some business friendly information about what's happening, without having to drill into the processing itself. This helps business end users and admin's alike, as the Milestones are captured on the Request Tab to provide an easy to use reference point. 

RSA Field Example: To put it in generic terms, what we really use them for, is to help determine why the CR has ended up where it has, without having to look at the processing workflow. We typically use them after decision nodes or to provide success/failure response. 

As shown in this status image below, to meet a customer requirement we needed to identify requests created as a result of an account being Revoked within an account review and handle them differently. This is the first decision within the workflow and we use the Milestone to confirm this, without this Milestone you'd need to view the processing workflow to confirm the route the request has taken.

 

Then, because it’s a revoke from a review, there's a requirement to create a new CR from the workflow. This milestone not only confirms the new CR has been created but also provides the new CR id. This provides an audit-able trail and helps users with locating the new CR.

 

In its simplest form, the Supervisor Approval workflow could be updated to include Milestone to advise if a supervisor couldn’t be found! Without the milestone, you'd need to dig a little deeper to extract this useful information.

 

 

Usage: All workflows should include milestones where possible, especially ones which are seen by business users, to make their understanding clearer and the process more simple.

General Notes/Benefits

  • Positive business impact to provide added information and details
  • Reduced help desk calls, where business users don't understand whats happened and why
  • Aid with troubleshooting
  • Can be used to provide error handling
  • Can be used to assist with tracking/auditing
  • Can provide dynamic variables from the request
    • As an example, you could have a workflow create an additional CR, a milestone can be used to confirm the new CR has been created successfully and also provide the CR ID, as shown below:

 

Configuration:  

  • We are using v7.1x in the example below, however most versions of the older product also have milestones available. 
  • Milestone nodes are found under the "Modeler Toolbox", about halfway down, as shown in the image below. Just drag and drop them into your workflow.

  • Milestone nodes have a couple of basic properties:
    • General: Name
      • Keep the node name generic and configure the milestone message under the Status options, for the following reasons
      • Variables cannot be used within the Node Name but they can be when using the Status options
      • The status options can be used to control when the milestone is displayed
    • General: Description
      • A simple description of what the milestone is doing, for future reference.

 

  • Status options:
    • Planned (Possible)
    • Completed
      • Planned (Definite) - we recommend not to use this one and to stick with the Possible/Completed

 

To help try and explain these, we have created the following workflow that contains a Milestone for each option.

 

 

The Planned (Possible) message will be displayed even though at this point the workflow has not yet transitioned through the node. This is a way to provide some information about a potential next step in the process, which is upcoming. 

 

 

 

Completed will populate the message only after the workflow has successfully transitioned through/past the node:

  

 

RSA PS Recommendation

Leave both ‘Planned’ options empty and only populate the Completed option to show the business which items have actually happen in the process flow, so as to not cause any confusion.

Final note.

Milestone nodes, also make use of the helpful information "i" button, found at the end of the status details. The "i" button displays details directly from the request. The below image is the first decision in the License Review workflow which checks if the requested entitlement is licensed or not. By clicking on the "i" button it confirms which entitlement it’s referring to, this is really relevant if you have CR containing multiple items (which is a common use case)

Date formats in workflows, typically come in a very long and "un-useful" format, which is: "YYYY-MM-DD HH:MM:SS" eg: 2019-11-05 10:46:54.0

This format is not very useful for business users and so changing it to be something more "readable" is a good idea in general. 

 

Thanks to the help from our resident rock star...Mostafa Helmy, we have the following example that you can use today for your review esclation emails. The below could be applied to any dates in other areas of the product too, however review esclations is a widely used area and a good place to start.

 

The process is as follows, where we want to update the review "end date" value.You could apply this to the "start date" too, if you wanted.

 

  1. First, establish the format of how you want the date to look. e.g.. "29-01-2019" or "Feb-22-19" etc..
  2. To do this, open a query analyser (e.g. SQL Developer) and test with the following SQL to find the date you want to display.
    Note, this example has 2 different options to get you started. Also, see the table below to get more ideas of the date formats that you could use.

    select to_char(sysdate,'MM/DD/YYYY') as new_date from dual;

    select to_char(sysdate,'DAY DD-MON-YY') as new_date from dual;


    Image of the above SQL example

  3. Once you have the correct format that you want to use
    Always start in DEV, NOT prod
  4. Log into RSA IGL as a user who can edit/create workflows
  5. Find the workflow/escalation you want to edit and open it
  6. Add a new "SQL Select" object to the workflow, at the start
    It must be before the "email" or what ever you want this better date format to be
  7. Edit the "SQL Select" object to be the following SQL.
    Note: This SQL will create a new value called "new_end_date" which can then be added into your email.
    Format SQL
    MM-DD-YYYY
    (08/21/2019)

    select to_char(end_date,'MM/DD/YYYY') as new_end_date from pv_review where id='${jobUserData_acm.ReviewId}'

    DD-MM-YYYY
    (21/08/2019)

    select to_char(end_date,'DD/MM/YYYY') as new_end_date from pv_review where id='${jobUserData_acm.ReviewId}'

  8. Set the Variable Type to Job. This must be set so that it can be referenced as a variable within the workflow
  9. Make sure you now hit "SAVE"
    The new value wont show until you have done this!!
  10. Select your email node and "right-click" where you want to add the new value (new_end_date) to add it in. (See image below)
  11. Test this has worked by generating a new email and confirming the date format is correct and as expected.
  12. Migrate to production

 

 

 

 

Other formatting suggestions:

Note:

  • The format, MUST be in the correct CASE
  • See this link for Oracle datetime format models: Format Models 
YEARYear, spelled out
YYYY4-digit year
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY4-digit year based on the ISO standard
QQuarter of year (1, 2, 3, 4; JAN-MAR = 1).
MMMonth (01-12; JAN = 01).
MONAbbreviated name of month.
MONTHName of month, padded with blanks to length of 9 characters.
RMRoman numeral month (I-XII; JAN = I).
WWWeek of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
WWeek of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IWWeek of year (1-52 or 1-53) based on the ISO standard.
DDay of week (1-7).
DAYName of day.
DDDay of month (1-31).
DDDDay of year (1-366).
DYAbbreviated name of day.
JJulian day; the number of days since January 1, 4712 BC.
HHHour of day (1-12).
HH12Hour of day (1-12).
HH24Hour of day (0-23).
MIMinute (0-59).
SSSecond (0-59).
SSSSSSeconds past midnight (0-86399).
FFFractional seconds.

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!

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!