JDBC connector Entitlement and Account Aggregatiom

[pulatoi (Ibrahim Pulatov)]

Hello guys!
I hope you are having a wonderful Friday!
We are trying to onboard an application using JDBC connector, and I am currently having issues with establishing connection between an account and entitlement. I am able to pull users and pull entitlements, but accounts are not being assigned with entitlements.
We have 2 types of entitlements (lets call it EntitlementTypeA & EntitlementTypeB), so I configured those both types of entitlements and its chemas, in account schema I specifically chose the attribute type for those attributes as entitlementA and entitlementB.
For better understanding there 3 tables, Employee(all identity data is being pulled from here), EntitlementA table(entitlement A is being pulled), EntitlementB table. So after reading the documentations and posts on this forum, I figured I need to use joins to establish connection between an account and entitlements.
So this is the sql query I am using for account aggregation:

SELECT e.*, ent1.*, ent2.* FROM employee e 
LEFT JOIN entitlement1 ent1 ON e.sec_groupid = ent1.secg_id
LEFT JOIN entitlement2 ent2 ON e.approval_id = ent2.appr_id;

But it is still not working, am I doing something wrong? This isw my first JDBC connector in Identityno

[udayputta]

@pulatoi, couple of things here.

Your query ends with “;”. As per the SailPoint doc queries should not end with “;”. can you remove that and try it out once.

Account Query Settings

Can you also do a discover schema and update/correct your account and entitlment schema before you can run the account aggregation.
You are also using a wild card (*) in your query and you mentioned you are getting users and groups. Is it in ISC or outside ISC. Can you remove * and fetch the required columns.

SELECT e.column4 AS UserID,column5 AS PERMISSION FROM Permission p WHERE ...'

[rkhade (Rakesh Khade)]

Hi @pulatoi

If this issue is not yet fixed,

Can you please check, if the attribute mapped as entitlement in Account schema matches the attribute mapped as Entitlement Id in entitlement schema.

Refer below snapshot

Account Schema

Ent Schema

Hope this helps your query

Thank you

[Karthikeyan_U (Karthikeyan Udhayasurian)]

Hi Ibrahim,

Hope you will be having the tables like shown in below,

And your schema should be like below
entitlement1

entitlement2

Account schema

And the Account SQL you need to use is (make sure to have the same name in account and entitlement schema)

SELECT e.id, e.name, e.email, e.status, e.sec_groupid AS secg_id, e.approval_id AS appr_id FROM employee e, entitlement1 ent1, entitlement2 ent2
WHERE e.sec_groupid = ent1.secg_id AND e.approval_id = ent2.appr_id

Then you will see the entitlements are aggregated and attached to respective accounts

Thanks,
Karthi

[pulatoi (Ibrahim Pulatov)]

Thank you guys for your answers, followed every single steps you mentioned. As far as entitlement config, it had the same configs, i think the issues was in * or maybe in ;, I havent tested which one was the mistake, but i had to list every single column by name and removed ; and it worked. May God Bless you all, you are the best!