Please share your results and ideas below!
- RSA IGL v7.1x or v7.2x
- Identity data collections running, with some new joiners 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 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
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"
to_char(sysdate - (7 * level) +7,'YYYYMMDD') as FullDate,
to_char(sysdate - (7 * level) +7,'WW') as tmpWeek
CONNECT BY LEVEL <= 10
'Joiners' as AppAction,
count(creation_date) as TotalPerWeek
to_char(creation_date,'WW') as tmpWeek
where creation_date > sysdate - 70
group by tmpWeek
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
- 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.