Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

User Manager Campaign Filter Query (step 1 /Step 2)

William3squivel
New Contributor III
New Contributor III

Our team is looking to filter out users with outstanding certifications (i.e users with no response in their certification) and launch a new campaign that will escalate to their manager to complete. Currently our query is using the cus.certfied is null and searching for null/no responses which works in Step 1 of the User Manger Campaign (employment Verification) . However, if there is a manager for example that completes the first step of selecting a status for their direct report but does not complete the second step (Access approval/revoke) the query will not filter out that user. Is there a query that can look for no response in both step 1 and step 2 of the user Manager campaign and filter out a user that has no response in either step? 

current query : 
username in (select cu.username from campaign cm inner join certification c on cm.id=c.campaignkey inner join certification_user_status cus on c.certkey=cus.certkey inner join users u on c.certifier=u.userkey inner join certification_user cu on cus.cert_userkey=cu.cert_userkey where cus.certified is null and cm.campaign_name='SelfCertify_ Testing')

2 REPLIES 2

sk
All-Star
All-Star

Validate below query

username in (select distinct cu.username from campaign cm inner join certification c on cm.id=c.campaignkey inner join certification_user_status cus on c.certkey=cus.certkey inner join users u on c.certifier=u.userkey inner join certification_user cu on cus.cert_userkey=cu.cert_userkey where cus.certified is null and cm.campaign_name IN ('<Campaign_Name>')
UNION
SELECT distinct u2.username AS username FROM certification c INNER JOIN campaign ca ON c.CAMPAIGNKEY = ca.id INNER JOIN certification_account_entitlement1_status cs ON c.certkey = cs.certkey INNER JOIN certification_account caa ON caa.cert_accountkey = cs.cert_accountkey INNER JOIN user_accounts ua ON ua.accountkey = caa.accountkey INNER JOIN users u2 ON u2.userkey = ua.userkey WHERE cs.CERTIFIED is null and ca.campaign_name IN ('<Campaign_Name>'))

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

SB
Saviynt Employee
Saviynt Employee

Sine the Step2 includes information of Account and Entitlements, you can use the below 2 tables in your query to check for the same.

Table: certification_user_account_status
COLUMN: CERTIFIED
Table: certification_account_entitlement1_status
COLUMN: CERTIFIED


Regards,
Sahil