Use Case
To determine Active users List not in feed after full user import.
Applicable Version(s)
All
Solution
Below query can be used to identify all the users which are active in SSM but were not part of the feed of the last full user import. This helps us to identify missing users list to take remedial actions on the users missed-
- Terminate users in case users have been terminated in HR for e.g. users that are active more than required, which can cause SOX control failure/s.
- Update missing properties in SSM based on information fetched from the HR.
SELECT
u.DISPLAYNAME,
u.USERNAME,
u.CUSTOMPROPERTY3 AS 'IDENTITYSTATUS',
u.CUSTOMPROPERTY30 AS 'LASTUPDATETIME',
u.STATUSKEY,
u.JOB_ID,
u.CUSTOMPROPERTY22 AS UserEmail
FROM
users u
WHERE
(job_id < (SELECT
ecmimp.jobid
FROM
ecmimportjob ecmimp
WHERE
coments LIKE '%fullorincremental:full]'
AND jobname = 'UserImportJob'
ORDER BY ecmimp.jobstartdate DESC
LIMIT 1)
AND u.statuskey = 1);
- Query runs on users table and finds out if the jobid is less than that of the last full user import or not.
- Last full import job id will be the max of jobid in ecmimportjob table.
NOTE:
- Limitation of the above control is that you cannot run it after an incremental import. The reason being, incremental does not update all records but only the delta changes. Therefore, all other get picked up in the report.
- It is recommended to run it after a full import but before an incremental import is run.
