Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Saviynt For Saviynt db connection - User groups - Accounts Import XML file content

Rayomand
New Contributor III
New Contributor III

hi,

for user groups in sav for sav connections of DB connection type,  can someone provide the query for 

Accounts Import XML file content

Rayomand_1-1721219572337.png

 

Rayomand_0-1721219545116.png

 

2 REPLIES 2

rushikeshvartak
All-Star
All-Star
<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>

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

pmahalle
All-Star
All-Star

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>

 


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂