Need information to fetch report of campaign

[sairamya15]

Hi All,

I have requirement to fetch a report to "Export of full campaign to filter approved and rejected access".

Kindly suggest.

[Raghu]

@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

[rushikeshvartak]

Can you confirm campaign type 

and refer below posts

https://forums.saviynt.com/t5/tag/CampaignReports/tg-p

[sairamya15]

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

[rushikeshvartak]

• What is constraint with OOTB export functionality within campaign export ?

[rushikeshvartak]

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;

[sairamya15]

Hi @rushikeshvartak 

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'

[rushikeshvartak]

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

[dgandhi]

You get this OOTB, just export it from campaign. Is there any limitation in that report?