Please share your results and ideas below!
- RSA IGL v7.1x or v7.2x
- Identity data collections running, with some leavers in the environment
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.
- "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
- "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
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"
to_char(sysdate - (7 * level) +7,'YYYYMMDD') as FullDate,
to_char(sysdate - (7 * level) +7,'WW') as tmpWeek
CONNECT BY LEVEL <= 10
'Leavers' as AppAction,
count(deletion_date) as TotalPerWeek
to_char(deletion_date,'WW') as tmpWeek
where deletion_date > sysdate - 70
group by tmpWeek
ON t2.tmpWeek = t1.tmpWeek
order by FullDate asc)
he output looks like this in my environment. Showing that there were 41 leavers in week 27
Steps to create within the RSA IGL UI
- Log into RSA IGL as a user who can create charts. In my example, im using AveksaAdmin
- Go to "Reports" / "Charts"
- Select "+ Create Chart" button
- Under the "General Tab" add the following details:
- 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.
- Under the "Columns" Tab, please use the configuration shown in the image below
- Under the "Display Attributes" tab, you should select "Style = Line". Please also apply these settings:
There are MANY other "display attributes" you can play with on this screen, so please update and make changes as you see fit.
Add this chart to a dashboard, so you can easily see the data.