Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
03/05/2024
08:47 AM
- last edited on
03/05/2024
11:37 AM
by
Dave
Hi Team,
We required your assistance in getting the List of endpoints involved in a campaign. I am using this Query. It has multiple endpoints
select campaign_name,endpointids from campaign where campaign_id in('ABC')
OutPut: Disconnected apps_UAR -1,45.46,47,48
Here endpoinid column is not giving the direct endpoint keys. It consists the all endpoint keys and separated with semicolon.
Please suggest a query to pull all the endpoints from campaign in readable format
Please let us know, how can we use query to split endpoint keys. Any split function in saviynt?
Solved! Go to Solution.
03/05/2024 07:08 PM
SELECT
campaign_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(endpointids, ',', numbers.n), ',', -1) AS endpointid
FROM
campaign
JOIN
(SELECT 1 + units.i + tens.i * 10 AS n
FROM
(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS tens
) AS numbers
WHERE
campaign_id = 'ABC';
03/05/2024 09:16 PM
Thank You for Your response Rushikesh.
We want to extract what are all the conditions we used in Advanced Configuration of a campaign.
Like User query,account query,Consult query,Entitlement query,accountentitlementquery. Kindly suggest on this.
03/09/2024 12:39 AM
Hi
Can you please suggest the sql query to pull advanced configuration attributes from Campaign
03/10/2024 07:12 PM
select campaign_name ,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONFIGURATION_AS_JSON, '"userQry":"', -1), '"', 1) AS 'Users Query',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONFIGURATION_AS_JSON, '"accountQry":"', -1), '"', 1) AS 'Accounts Query',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONFIGURATION_AS_JSON, '"entQry":"', -1), '"', 1) AS 'Entitlements Query',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONFIGURATION_AS_JSON, '"roleQry":"', -1), '"', 1) AS 'Roles Query',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONFIGURATION_AS_JSON, '"accountEnt1Qry":"', -1), '"', 1) AS 'Account Entitlements1 Query',
SUBSTRING_INDEX(SUBSTRING_INDEX(CONFIGURATION_AS_JSON, '"userQry":"', -1), '"', 1) AS 'Consult/Reassign User Query',
status
from campaign where id=617
03/10/2024 07:12 PM