Skip navigation
2019

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!