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 environment.
This Document will cover the following scenarios:
- Inactive Accounts in UM Campaign
- Inactive Certifiers - Across Certifications
- Inactive Certifiers on Active Certifications
- Inactive Consulted Certifiers
- Inactive User Accounts part of UM campaign
Solution Description:
Inactive Accounts in UM Campaign
This report displays the Inactive Accounts in UM Campaign
select distinct cm.campaign_name as 'Campaign Name', c.CERT_NAME as 'Certification Name',u.SYSTEMUSERNAME as 'Certifier Username',u.FIRSTNAME as 'Certifier First Name',u.LASTNAME as 'Certifier Last Name',a.ACCOUNTID as 'Account ID',a.NAME as 'Account Name' from certification c,campaign cm, certification_account_entitlement1_status caes,certification_account ca,accounts a,users u where ca.CERT_ACCOUNTKEY=caes.CERT_ACCOUNTKEY and ca.ACCOUNTKEY=a.ACCOUNTKEY and c.CERTKEY=caes.CERTKEY and cm.id=c.CAMPAIGNKEY and c.certifier=u.userkey and a.STATUS in ('Manually Suspended','2') |
Inactive Certifiers - Across Certifications
This report displays the Inactive Certifiers across Certifications
select cm.campaign_name as 'Campaign Name',c.cert_name as 'Certification Name', u.SYSTEMUSERNAME as 'Certifier UserName',u.FIRSTNAME as 'Certifier FirstName', u.LASTNAME as 'Certifier LastName',u.USERKEY as 'Certifier UserKey' from certification c, users u,campaign cm where c.certifier=u.userkey and c.CAMPAIGNKEY=cm.id and u.statuskey = 0; |
Inactive Certifiers on Active Certifications
This report displays the Inactive Certifiers on Active Certifications created in the past 60 days.
select u.SYSTEMUSERNAME as Username,u.firstname as FirstName,u.lastname as Lastname,u.email as Email, CASE WHEN u.statuskey=0 THEN 'Inactive' END as UserStatus from certification c, users u where u.userkey = c.CERTIFIER and u.statuskey = 0 and c.status in (0,1) and c.createdate > DATE_SUB(now(), interval 60 DAY) |
Inactive Consulted Certifiers
This report displays Inactive Consulted Certifiers
select cm.campaign_name as 'Campaign Name',k.CERT_NAME as 'Certification Name',u2.SYSTEMUSERNAME as 'Consultee Username',u2.FIRSTNAME as 'Consultee First Name',u2.LASTNAME as 'Consultee Last Name',u.SYSTEMUSERNAME as 'Consulted Username',u.FIRSTNAME as 'Consulted User First Name', u.LASTNAME as 'Consulted User Last Name' from certification_certifier cc, certification k ,certification_user cu, users u,users u2,campaign cm where k.certkey = cc.certkey and cc.userkey = cu.cert_userkey and u.userkey = cu.userkey and k.CAMPAIGNKEY=cm.id and u2.USERKEY=cc.CERTIFIER and k.TYPE=2 and u2.STATUSKEY=0 |
Inactive User Accounts part of UM campaign
This report displays Inactive User Accounts part of UM Campaign
| select p.campaign_name as 'Campaign Name',k.CERT_NAME as 'Certification Name' ,u.SYSTEMUSERNAME as 'Certifier Username',u.FIRSTNAME as 'Certifier First Name',u.LASTNAME as 'Certifier Last Name',a.ACCOUNTID as 'Account ID',a.name as 'Account Name' from campaign p,certification k,certification_user_account_status cu,certification_account ca, user_accounts ua, accounts a,users u where ua.accountkey = a.accountkey and ca.userkey = ua.userkey and ca.ACCOUNTKEY = ua.ACCOUNTKEY and ca.status not in ('Manually Suspended', '2') and a.status in ('Manually Suspended','2') and cu.CERT_ACCOUNTKEY = ca.CERT_ACCOUNTKEY and k.certkey = cu.CERTKEY and p.id = k.CAMPAIGNKEY and k.CERTIFIER=u.USERKEY |
