SQL Query-Required assistance in getting the Endpoint list in a campaign

[VJReddy]

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?

[rushikeshvartak]

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';

[VJReddy]

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.

[VJReddy]

Hi

Can you please suggest the sql query to pull advanced configuration attributes from Campaign

[rushikeshvartak]

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

[rushikeshvartak · Accepted solution]