Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/01/2024 12:51 AM
Hi All,
I have requirement to fetch a report to "Export of full campaign to filter approved and rejected access".
Kindly suggest.
07/01/2024 06:09 AM
@sairamya15 check below query and article it will help full
SELECT ce.certkey AS 'Certification ID',
c.campaign_name AS 'campaign name',
CASE c.status
WHEN 1 THEN 'In Progress'
WHEN 2 THEN 'Completed'
WHEN 5 THEN 'In Progress'
WHEN 4 THEN 'Preview'
WHEN 6 THEN 'Discontinued'
WHEN 7 THEN 'Expired'
WHEN 8 THEN 'Launching'
END AS 'Campaign Status',
ce.cert_name AS 'Certification Name',
CASE
WHEN ce.status = 0 THEN 'New'
WHEN ce.status = 1 THEN 'In Progress'
WHEN ce.status = 2 THEN 'Completed'
WHEN ce.status = 3 THEN 'Locked'
WHEN ce.status = 4 THEN 'Expired'
WHEN ce.status = 7 THEN 'Discontinued'
WHEN ce.status = 8 THEN 'Locked and Task Created'
ELSE NULL
END AS 'Certification Status',
u.displayname AS 'Certifier Name',
c.start_date AS 'Campaign Start Date',
c.end_date AS 'Campaign End Date'
FROM campaign c,
certification ce,
users u
WHERE c.start_date >= Date_sub(Curdate(), interval 30 day)
AND c.id = ce.campaignkey
AND ce.certifier = u.userkey
https://forums.saviynt.com/t5/identity-governance/campaign-query-report/m-p/90168
https://forums.saviynt.com/t5/saviynt-knowledge-base/campaign-report-queries/ta-p/50171
https://forums.saviynt.com/t5/identity-governance/um-campaign-data-analytics-report/m-p/54339
07/01/2024 06:16 AM - edited 07/01/2024 06:16 AM
Can you confirm campaign type
and refer below posts
https://forums.saviynt.com/t5/tag/CampaignReports/tg-p
07/02/2024 02:24 AM
Hi @Raghu
Thanks for the response, The query you have provided is giving the progress of the campaign, but we have a requirement to pull report of approved and rejected access from UM campaign.
@rushikeshvartak Thanks for response, It is for user Manager Campaign
07/02/2024 11:57 AM
07/02/2024 12:01 PM
select distinct certification.CERT_NAME, u2.USERNAME as 'Manager', u2.FIRSTNAME as 'Manager_Firstname', u2.LASTNAME as 'Manager_Lastname', certification_user.USERNAME, certification_user.FIRSTNAME, certification_user.LASTNAME, certification_user.EMAIL ,certification_user.STATUSKEY, certification_account.NAME , certification_entitlement_value.ENTITLEMENT_VALUE, ENTITLEMENTNAME,endpoints.ENDPOINTNAME from campaign inner join certification on campaign.id= certification.CAMPAIGNKEY inner join certification_account_entitlement1_status on certification.CERTKEY= certification_account_entitlement1_status.CERTKEY inner join certification_account on certification_account_entitlement1_status.CERT_ACCOUNTKEY= certification_account.CERT_ACCOUNTKEY inner join certification_entitlement_value on certification_account_entitlement1_status.CERT_ENTITLEMENT_VALUEKEY= certification_entitlement_value.CERT_ENTITLEMENT_VALUEKEY inner join entitlement_types on certification_entitlement_value.ENTITLEMENTTYPEKEY= entitlement_types.ENTITLEMENTTYPEKEY inner join endpoints on entitlement_types.ENDPOINTKEY= endpoints.ENDPOINTKEY left join certification_user_account_status on certification_user_account_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY left join certification_user on certification_user.CERT_USERKEY= certification_user_account_status.CERT_USERKEY inner join users u2 on certification.certifier=u2.userkey where campaign.campaign_name = 'RUshi Review' order by certification_user.CREATEDBY desc;
07/04/2024 03:06 AM
Thanks for the reply.
The query you have provided has give CERT_NAME, MANAGER, MANAGER_FIRSTNAME, MANAGER_LASTNAME, USERNAME, FIRSTNAME, LASTNAME, EMAIL, STATUSKEY, NAME, ENTITLEMENT_VALUE, ENTITLEMENTNAME, ENDPOINTNAME
We need to a report to see all the APPROVED access and REVOKED access of application in user manager campaign also status of employee whether he/she 'WORKS FOR ME, DOESNOT WORKS FOR ME, TERMINATED, EXTENDED END DATE'
07/04/2024 08:56 AM - edited 07/04/2024 08:58 AM
You can update query as per your requirement with reference links provided
Refer https://forums.saviynt.com/t5/identity-governance/certification-export-query-needed/m-p/10695#M2612
Table Name : certification_user_status
Column Name : AUDITTRAIL
07/05/2024 09:41 AM
You get this OOTB, just export it from campaign. Is there any limitation in that report?
Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.