Role Owner Import XML

[ejeong]

Hello, 

I just implemented role import using saviynt4saviynt role import based on departmeent in Workday.

can we update role owner using same JSON field ? or should I use seperate XML in Role Ower Import? 

This is XML I am using to import Roles.. 

<dataMapping>
<before-import>

</before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="role_name"><![CDATA[select
distinct CONCAT('BR_DEP_',SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty63,':', 3),':', -1)) as 'role_name'
,SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty58,':', 3),':', -1) as 'displayname'
,u1.username as 'RoleOwner'
,CONCAT('This role is the set of people entrusted to use the firms of information systems consistent with the responsibilities accorded by their membership in the supervisory org, ' ,SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty58,':', 3),':', -1),'defined in WD as level 3 value') as 'description'
,4 as roletype
,1 as STATUS
,1 as flagexportedtooia
from users u,users u1
where
u.statuskey = 1
and u.customproperty22 is not null
and SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty63,':', 3),':', -1) is not null
and u.customproperty14 = SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty63,':', 3),':', -1)
and u.customproperty14 = u1.customproperty19
group by SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty63,':', 3),':', -1)]]></sql-query>
<mapper description="Role Import" dateformat="date">
<mapfield saviyntproperty="roletype" sourceproperty="roletype" type="number" ></mapfield>
<mapfield saviyntproperty="status" sourceproperty="STATUS" type="number" ></mapfield>
<mapfield saviyntproperty="role_name" sourceproperty="role_name" type="character" ></mapfield>
<mapfield saviyntproperty="displayname" sourceproperty="displayname" type="character" ></mapfield>
<mapfield saviyntproperty="description" sourceproperty="description" type="character" ></mapfield>
<mapfield saviyntproperty="flagexportedtooia" sourceproperty="flagexportedtooia" type="number" ></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL">

</after-import>
</dataMapping>

,SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty58,':', 3),':', -1) as 'displayname'
,u1.username as 'RoleOwner'

This person should be role owner for any role staring with BR_DEP_xxxx role. 

Could you please help me how to resolve this issue? 

Thanks

[rushikeshvartak]

Please use ROLEOWNERIMPORT field & role owner import job for job control panel.

use attached xml

[ejeong]

Job went successful but no owner still updated..

I changed one attribute mapping about rolekey... but still no luck.

[ejeong]

I changed maapping like below.. but still no luck.. 

<mapfield saviyntproperty="rolekey" sourceproperty="rolekey" type="character"></mapfield>
<mapfield saviyntproperty="userkey" sourceproperty="roleowneruserkey" type="character"></mapfield>
<mapfield saviyntproperty="rank" sourceproperty="rank" type="number"></mapfield>

This is log of job..

[ejeong]

I am thinking.. since it's getting data across mutiple table (Users and Roles).. I think I need to mention table name before column name.. any thought on this?

[ejeong]

I tried below but no luck..

<mapfield saviyntproperty="roles.rolekey" sourceproperty="rolekey" type="character"></mapfield>
<mapfield saviyntproperty="users.userkey" sourceproperty="roleowneruserkey" type="character"></mapfield>
<mapfield saviyntproperty="roles.rank" sourceproperty="rank" type="number"></mapfield>

[rushikeshvartak]

Try this

[ejeong]

it still didn't work.. 

also I tried like below as well 

<mapfield saviyntproperty="role_name" sourceproperty="role_name" type="character"/>
<mapfield saviyntproperty="username" sourceproperty="roleownerusername" type="character"/>
<mapfield saviyntproperty="rank" sourceproperty="rank" type="number"/>

<mapfield saviyntproperty="roles.role_name" sourceproperty="role_name" type="character"/>
<mapfield saviyntproperty="users.username" sourceproperty="roleownerusername" type="character"/>
<mapfield saviyntproperty="roles.rank" sourceproperty="rank" type="number"/>

none of them worked..

I stlll see no error in import.. 

[rushikeshvartak · Accepted solution]

Refer :

https://saviynt.freshdesk.com/support/solutions/articles/43000617705-database-connector-guide#Databa... 

[rushikeshvartak]

Can you attach working xml

[ejeong]

Thanks. yes I saw that in freshdesk but there is one thing I felt strange..

but I am wondering how Role_name can be mapped with rolekey... it's different data.. 

[rushikeshvartak]

Mandatory Mapping columns are rolekey userkey rank

Rolekey should be mapped to target table role name

Userkey should username of role owner

Rank should be number

When you are importing data from target it target application will not know what is saviynt internal userkey & rolekey. Saviynt internally based on rolename & username put rolekey n add role owner to role. 

[ejeong]

understood that part 

but rolekey is number format and role_name is string format. 

I thought rolekey from query should be mapped with rolekey in role owner table. 

[smithamg]

Hi,

I have a similar requirement, we want to add roleowner with rank 26 for certifiable roles using sav for sav import. Designed role_import xml as per FD documents.

But when I run role_ower import job its removing role_owner from all roles and adding nothing.

Cant we use this feature for updating existing roles?

Can you please confirm if this functionality worked for you and please attach working xml.

Thanks,

Smitha

[rushikeshvartak]

Try

deleteentitlementowner="false"

[smithamg]

Tried this but still the same issue.

Also tried 

deleteroleowner="false"