Use Case
This document will provide the consolidated list of some of the frequently used Campaign Reports. This can be lifted and shifted at any customer environment.
This Document will cover the following scenarios:
- Terminated Users Still Active
- Users with End Date not in sync
- Revoked Access Not Removed
- Open Revoke Tasks for Terminated Users - UM_Step_1
Pre-requisites
N/A
Applicable Version(s)
All
Solution
Terminated Users Still Active:
This report Displays the Terminated Users in UM Step 1 who are still 'Active'
select cm.campaign_name as 'Campaign Name',c.CERT_NAME as 'Certification Name',u.FIRSTNAME as 'Certifier First Name',u.LastNAME as 'Certifier Last Name',u.SYSTEMUSERNAME as 'Certifier Username',cu.FIRSTNAME as 'User First Name',cu.LASTNAME as 'User Last Name',cu.SYSTEMUSERNAME as 'Username',t.TASKKEY as 'Task Key' from certification_user cu, certification_user_status us,certification c,users u, users u2, arstasks t,campaign cm where us.CERTIFIED=4 and us.CERTKEY=t.SOURCEID and t.USERKEY=cu.USERKEY and u2.USERKEY=cu.USERKEY and cu.CERT_USERKEY=us.CERT_USERKEY and t.status=3 and us.CERT_USERKEY=cu.CERT_USERKEY and c.CERTKEY=us.CERTKEY and u.USERKEY=cu.MANAGER and cm.id=c.CAMPAIGNKEY and u2.STATUSKEY=1; |
Users with End Date not in sync:
This report displays the users who have their End dates not in sync with their Extended End date from Step 1 of UM Campaign
select cm.campaign_name as 'Campaign Name', c.CERT_NAME as 'Certification Name', CASE
WHEN c.status = 4 THEN 'Expired'
WHEN c.status in (3,8) THEN 'Locked'
WHEN c.status in (0,1) THEN 'Pending'
WHEN c.status = 7 THEN 'Discontinued'
WHEN c.status = 2 THEN 'Ready to Submit'
WHEN c.status = 6 THEN 'Preview'
WHEN c.status = 10 THEN 'Fully Executed'
END AS 'Certification Status' ,u.SYSTEMUSERNAME as 'Certifier UserName',u.FIRSTNAME as 'Certifier FirstName',u.LASTNAME as 'Certifier LastName' from campaign cm, certification c, users u where cm.id=c.CAMPAIGNKEY and c.CERTIFIER=u.USERKEY and (u.EMAIL is null or ''); |
Revoked Access Not Removed:
This report displays all the revoked access which are in completed or discontinued status
select c.campaign_name as 'Campaign Name',k.CERT_NAME as 'Certification Name' ,u2.SYSTEMUSERNAME as 'Certifier Username',u2.FIRSTNAME as 'Certifier First Name',u2.LASTNAME as 'Certifier Last Name',cu.SYSTEMUSERNAME as 'Username',cu.FIRSTNAME as 'User First Name',cu.LASTNAME as 'User Last Name',cu.EXTENDEDENDDATE as 'Extended End Date',u.ENDDATE as 'User End Date' from campaign c , certification k, certification_user_status cus, certification_user cu,users u,users u2
where c.id =k.campaignkey
and cus.CERT_USERKEY = cu.cert_userkey
and cus.certkey = k.certkey
and u.USERKEY=cu.USERKEY and k.CERTIFIER=u2.userkey and cu.EXTENDEDENDDATE != u.ENDDATE; |
Open Revoke Tasks for Terminated Users-UM_Step_1:
This report displays open revoke tasks for terminated users in User Manager Step 1
select cm.campaign_name as 'Campaign Name',c.CERT_NAME as 'Certification Name',u.FIRSTNAME as 'Certifier FirstName',u.LastNAME as 'Certifier LastName',u.SYSTEMUSERNAME as 'Certifier UserName',cu.FIRSTNAME as 'User FirstName',cu.LASTNAME as 'User LastName',cu.SYSTEMUSERNAME as 'UserName',t.TASKKEY as 'TaskKey' from certification_user cu, certification_user_status us,certification c,users u, arstasks t,campaign cm where us.CERTIFIED=4 and us.CERTKEY=t.SOURCEID and t.USERKEY=cu.USERKEY and cu.CERT_USERKEY=us.CERT_USERKEY and t.status=1 and us.CERT_USERKEY=cu.CERT_USERKEY and c.CERTKEY=us.CERTKEY and u.USERKEY=cu.MANAGER and cm.id=c.CAMPAIGNKEY; |
References
