Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/22/2024 02:25 AM
Hello experts,
Can we print Certified status , currently it printing json value , so when manager clicks works for me it is displaying as "2" and manager clicks does not works for me it is displaying as "3". Instead of that we need
WHEN CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response'
currently we are using below query
SELECT c.campaign_name AS 'Campaign Name',CASE c.campaign_type WHEN '1' THEN 'ENTITLEMENTOWNER' WHEN '2' THEN 'USERMANAGER' WHEN '3' THEN 'SELF_CERTIFICATION' WHEN '5' THEN 'ROLEOWNER' WHEN '6' THEN 'SERVICEACCOUNT' WHEN '7' THEN 'ORGANIZATION' WHEN '8' THEN 'APPLICATION_OWNER' WHEN '9' THEN 'APPLICATIONOWNER_METADATA' END AS campaign_type,CASE WHEN cus.CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN cus.CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response' END AS 'Certified Status', k.CERT_NAME AS 'Certification Name', u2.SYSTEMUSERNAME AS 'Certifier Username', u2.FIRSTNAME AS 'Certifier First Name', u2.LASTNAME AS 'Certifier Last Name', cu.username,cu.FIRSTNAME AS 'User First Name', cu.LASTNAME AS 'User Last Name' 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
User import json
{
"connection": "userAuth",
"successResponses": {
"statusCode": [
200,
201,
202,
203,
204,
205
]
},
"url": "https://xxx.saviyntcloud.com/ECM/api/v5/fetchRuntimeControlsDataV2?max=500",
"httpMethod": "POST",
"httpHeaders": {
"Authorization": "${access_token}"
},
"httpParams":"{\"analyticsname\":\"Campaign\"}",
"httpContentType": "application/json",
"userResponsePath": "results",
"colsToPropsMap": {
"username": "username~#~char",
"customproperty10": "CERTIFIED~#~char"
},
"errorCode": "400",
"errorCodePath": "errorCode"
}
05/22/2024 10:32 PM
Can you elaborate issue. ? Issue in query / json ?
05/23/2024 01:13 AM
The issue is in Query, the certified status is printing as 2 and 3 but we need to print as WHEN CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response' in user customproperty,
if we use this condition in query WHEN CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response' then it is not updating in user cp10.
Is there any other way we can achieve this
05/23/2024 01:54 AM
SELECT
c.campaign_name AS 'Campaign Name',
CASE c.campaign_type
WHEN '1' THEN 'ENTITLEMENTOWNER'
WHEN '2' THEN 'USERMANAGER'
WHEN '3' THEN 'SELF_CERTIFICATION'
WHEN '5' THEN 'ROLEOWNER'
WHEN '6' THEN 'SERVICEACCOUNT'
WHEN '7' THEN 'ORGANIZATION'
WHEN '8' THEN 'APPLICATION_OWNER'
WHEN '9' THEN 'APPLICATIONOWNER_METADATA'
END AS campaign_type,
CASE
WHEN cus.CERTIFIED = 2 THEN 'WORKS FOR ME'
WHEN cus.CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME'
ELSE 'No response'
END AS 'Certified Status',
k.CERT_NAME AS 'Certification Name',
u2.SYSTEMUSERNAME AS 'Certifier Username',
u2.FIRSTNAME AS 'Certifier First Name',
u2.LASTNAME AS 'Certifier Last Name',
cu.username,
cu.FIRSTNAME AS 'User First Name',
cu.LASTNAME AS 'User Last Name'
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;
05/24/2024 02:56 AM
@rushikeshvartak , thanks for the response
Need information on , if manager clicks on works for me in user manager campaign , then in the next step (Approve or Revoke Entitlements), manager clicks on certified or remove (attached the image)
WHen Manager clicks on certified or remove that data we need to get in the query.
Can you help me to get that query
05/26/2024 10:48 AM
did you check below posts :
Campaign Report Queries - Saviynt Forums - 50171
Solved: UM Campaign Data - Analytics Report - Saviynt Forums - 54339
Frequently used Campaign Reports based on Campaign... - Saviynt Forums - 28534
How To - Create Frequently used Campaign/Certifica... - Saviynt Forums - 29145
How to create summary report for Role Owner campai... - Saviynt Forums - 49966
How to download Campaign overall summary report fo... - Saviynt Forums - 29693