RSA IGL Recipes: Weekly Trending, Multi Series Chart - Joiners & Leavers

[#1]

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:
• Optional Changes to the Chart
• The SQL to use:
• Steps to create within the RSA IGL UI
• Final Result
• Next Steps

Prerequisites:

• RSA IGL v7.1x or v7.2x
• Identity data collections running, with some new joiners and leavers in the environment 
• You may want to test these out first, which are single series charts:
  • https://community.rsa.com/community/products/governance-and-lifecycle/blog/2020/07/02/rsa-igl-recipe... 
  • https://community.rsa.com/community/products/governance-and-lifecycle/blog/2020/07/02/rsa-igl-recipe... 

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.