Exclude Null Entitlement Records after User Import

[LoneWolf2020]

This query is related to DB connector (SQL Server). There are some active user account records which do not have any entitlements/roles associated with it. When I run the user import query (user-role mapping query), it is adding 'null' as one of the entitlements for the DB application.

I should configure the user import in such a way that the import should not ignore the active user account records with no entitlements, but it should not add a new entitlement called 'null'.

Please suggest a working solution for this. Thank you!

[rushikeshvartak]

Share xml

[LoneWolf2020]

Hi @rushikeshvartak !!
Apologies for the delayed response!

SQL query that returns all user records (including users who do not have any entitlements linked to them (null entitlements):
SELECT
UD.USER_ID "AccountName",
UD.NOTES "Email",
'1' "status",
'ABC' "application",
'ABC' "endpoint",
'ROLE' "entType",
arv.description "entValue"
FROM XYZ ud
LEFT JOIN OPQ urv on urv.USER_ID = ud.USER_ID
LEFT JOIN HIJ arv on arv.Role_No = urv.Role_No
where UD.ACTIVE_FLAG = 'Y' //returns 1589 records

SQL Query that returns only those user records which have entitlements/roles assigned to them:

SELECT
UD.USER_ID "AccountName",
UD.NOTES "Email",
'1' "status",
'ABC' "application",
'ABC' "endpoint",
'ROLE' "entType",
arv.description "entValue"
FROM XYZ ud
LEFT JOIN OPQ urv on urv.USER_ID = ud.USER_ID
LEFT JOIN HIJ arv on arv.Role_No = urv.Role_No
where UD.ACTIVE_FLAG = 'Y' and arv.description is not null //returns 1523 records

If we are considering all user records for the import, then, it is adding null entries under Entitlements.

Please find the xml attached to this comment for reference.

Requirement: It should import all 1589 user records into Saviynt, but it should not add null entitlement records under entitlements

Please share your inputs on this issue/discrepancy!!

Thank you!

[NM]

@LoneWolf2020 do you see null as an entitlement and entitlement type?

[LoneWolf2020]

Yes

[NM]

@LoneWolf2020 then add a case statement 

Case when arv.description not null then arv.description else '' end

[rushikeshvartak · Accepted solution]

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="AccountName">
<![CDATA[
SELECT UD.user_id "AccountName",
       UD.notes   "Email",
       '1'        "status",
       'ABC'      "application",
       'ABC'      "endpoint",
       CASE
         WHEN arv.description IS NOT NULL THEN 'ROLE'
         ELSE ''
       END        AS "entType",
       CASE
         WHEN arv.description IS NOT NULL THEN arv.description
         ELSE ''
       END        AS "entValue"
FROM   xyz ud
       LEFT JOIN opq urv
              ON urv.user_id = ud.user_id
       LEFT JOIN hij arv
              ON arv.role_no = urv.role_no
WHERE  UD.active_flag = 'Y' 
]]>
</sql-query>

<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="suspend" deleteaccountentitlement="true" addOnlyMode="FALSE"  ifusernotexists="noaction" systems="'ABC'">
<mapfield saviyntproperty="accounts.name" sourceproperty="AccountName" type="character"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="application" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entValue" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entType" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="Email" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>

[LoneWolf2020]

This solution worked. Thank you so much @rushikeshvartak !!