Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How to do entitlement import from multiple tables in DB connector

Rajesh_IAM
Regular Contributor
Regular Contributor

Hi,

We are integrating new DB application to Saviynt, As a part of this DB app integration, target DB has 4 different tables and now we need to import all these 4 tables data as 4 different entitlement type (like Group1, Group2, Group3,Group4) through entitlement import. However these 4 tables are indepent (means there is no cascade/dependency among 4 tables). Below are the sample table details.

1.SELECT [GroupID],[GroupName]FROM Table1; -- Entitlement Type: Group1
2.SELECT [RegionCode],[RegionName],[CountryCode],[CountryName] FROM Table2; -- Entitlement Type: Group2
3.
SELECT [OrganizationID],[OrganizationName],[Division],[BusinessUnit],[ProductGroup],[SortOrder] FROM Table3; -- Entitlement Type: Group3
4.
SELECT [TagId],[TagEntityType],[TagName] FROM Table4; -- Entitlement Type: Group4

Please review usecase and help me how to design entitlement import JSON to get all these 4 tables data along 4 different entitlement types.

6 REPLIES 6

Raghu
Valued Contributor III
Valued Contributor III

@Rajesh_IAM  try to use 'union' b/w select query and prepare

reference select union sql query's 

https://forums.saviynt.com/t5/identity-governance/union-in-analytics-query/m-p/89666

https://forums.saviynt.com/t5/identity-governance/extract-values-from-multiple-select-from-sql-query...

 

 

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Rajesh_IAM
Regular Contributor
Regular Contributor

Hi @Raghu  I tried with UNION already like below sample, but here blocker for me is "every table has one entitlement name, so I need to import 4 entitlement values from 4 tables".

[SELECT [GroupID] GID, '1' AS status, [GroupName] entitlementvalue, 'TestSS' securitysystem, 'TestEP' endpointname, 'Group1' entitlementtype FROM Table1 UNION SELECT [TagId] TID, [TagEntityType] TType, '1' AS status, [TagName] entitlementvalue, 'TestSS' securitysystem, 'TestEP' endpointname, 'Group2' entitlementtype FROM Table2 UNION SELECT [RegionCode] RC,[RegionName] RN,[CountryCode] CC, '1' AS status, [CountryName] entitlementvalue, 'TestSS' securitysystem, 'TestEP' endpointname, 'Group3' entitlementtype FROM Table3 UNION SELECT [OrganizationID] OID,[OrganizationName] entitlementvalue,[Division] DIV ,[BusinessUnit] BU,[ProductGroup] PG, 'TestSS' securitysystem, 'TestEP' endpointname, 'Group4' entitlementtype FROM Table4 ]

In the entitlement import, how can I map 4 tables entitlement values to entitlement types, entitlement name in JSON.? How can I map rest of the attributes to particular entitlement value customproperty values.?

It will be automatically mapped. You need to use common custom property across 4 tables if some tables does not have value then pass blank

 

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak  I have tried with attached entitlement JSON, but getting error: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."
Attached Entitlement Import JSON.

All queries should have same columns 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Alias and column should be same in all union fix below query entitlement value column


SELECT [GroupID] AS GID, '1' AS status, [GroupName] AS entitlementvalue, 'TestSS' AS securitysystem, 'TestEP' AS endpointname, 'Group1' AS entitlementtype FROM [SVNT_LMS].[CustomAttributeAdminGroup]
UNION
SELECT [TagId] AS GID, '1' AS status,[TagEntityType] AS entitlementvalue, 'TestSS' AS securitysystem, 'TestEP' AS endpointname, 'Group1' AS entitlementtype FROM [SVNT_LMS].[CustomAttributeTag]
UNION
SELECT [RegionCode] AS GID, '1' AS status, [RegionName] AS entitlementvalue, 'TestSS' AS securitysystem, 'TestEP' AS endpointname, 'Group1' AS entitlementtype FROM [SVNT_LMS].[CustomAttributeGeography]
UNION
SELECT [OrganizationID] AS GID, '1' AS status, [OrganizationName] AS entitlementvalue, 'TestSS' AS securitysystem, 'TestEP' AS endpointname, 'Group1' AS entitlementtype FROM [SVNT_LMS].[CustomAttributeOrganization];


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.