Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/17/2024 05:33 AM
hi,
for user groups in sav for sav connections of DB connection type, can someone provide the query for
Accounts Import XML file content
Solved! Go to Solution.
07/17/2024 05:52 AM
<dataMapping>
<before-import>
</before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
SELECT DISTINCT u.username AS accountname,
'Saviynt' AS NAME,
'Saviynt' AS endpointname,
u.username AS accountcn,
'Access Groups' AS attribute,
ug.user_groupname AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.username AS objectguid
FROM users u,
user_groups ug,
usergroup_users uu
WHERE ug.usergroupkey = uu.USER_GROUPKEY
AND uu.userkey = u.userkey
UNION
SELECT DISTINCT u.username AS accountname,
'Saviynt' AS NAME,
'Saviynt' AS endpointname,
u.username AS accountcn,
'Authorization Roles' AS attribute,
s.rolename AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.username AS objectguid
FROM users u,
user_savroles us,
savroles s
WHERE u.userkey = us.userkey
AND us.rolekey = s.rolekey
AND s.statuskey = 1
UNION
SELECT DISTINCT u.username AS accountname,
'Saviynt' AS NAME,
'Saviynt' AS endpointname,
u.username AS accountcn,
'JRM' AS attribute,
j.jrmrulename AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.username AS objectguid
FROM jrmrules j,
users u
WHERE u.userkey = j.owner; ]]>
</sql-query>
<mapper description="Saviynt Account to Entitlement Import" accountnotinfileaction="suspend" deleteaccountentitlement="true" incrementalcolumn="updatedate" dateformat="date" systems="'Saviynt'">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="name" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="attribute" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
<mapfield saviyntproperty="users.username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>
<mapfield saviyntproperty="accounts.accountID" sourceproperty="accountcn" type="character"></mapfield>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="objectguid" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>
07/17/2024 06:34 AM - edited 07/17/2024 06:36 AM
Hi @Rayomand ,
Here is the xml for user groups using Sav for Sav
<dataMapping>
<before-import></before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'UserGroup' as attribute, ug.user_groupname as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_groups ug, usergroup_users ugu where ug.usergroupkey=ugu.USER_GROUPKEY and ugu.userkey=u.userkey;]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="delete" deleteaccountentitlement="true" addOnlyMode="FALSE" ifusernotexists="noaction" incrementalcolumn="updatedate" systems="'Saviynt'">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="name" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="attribute" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
<mapfield saviyntproperty="users.username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>
<mapfield saviyntproperty="accounts.accountID" sourceproperty="accountcn" type="character"></mapfield>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="objectguid" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>