Saviynt Account assignment is not working

[Diwakar]

We are getting below error while trying to assign Saviynt account to users in provisioning tasks. Please help to fix the issue.

Error:SAV - Error while creating account - 851063-No such property: systemusername for class: com.saviynt.ecm.identitywarehouse.domain.Users Possible solutions: systemUserName SAV - Error while creating account - 851063-No such property: systemusername for class: com.saviynt.ecm.identitywarehouse.domain.Users Possible solutions: systemUserName

Create Account Json:

[DaanishJawed]

Hi @Diwakar ,

systemUserName is case sensitve. Refer the below query and accordingly make changes in your JSON.

{
"CreateAccountQry" : "create user '${user?.systemUserName}' IDENTIFIED BY \'${randomPassword}\';"
}

 Thanks

[Diwakar]

@DaanishJawed Thanks for the suggestion, I modified the systemUsername and then re-ran the task however now we are getting this error Error while creating account - a8908**- Exception occured in Create Account Query - PROCEDURE sav_fnd_user_pkg.createuser does not exist SAV

Please suggest the default JSON for Create account or suggest a way to assign Saviynt Account to all users by default.

Thanks,

Diwakar.

[NM]

Hi @Diwakar , you need to first define a store procedure in database then only you can use it in json

[Diwakar]

@DaanishJawed @NM Any other way to assign Saviynt account (used for SaviyntforSaviynt connection) to all users automatically, Through Saviynt Account import job also its not assigning!

[naveenss]

@Diwakar you can use REST connector and setup Sav4Sav

[NM]

@Diwakar which connector are you using?

[Diwakar]

@naveenss Saviynt4Saviynt is already setup as DB connector.

@NM Its DB connector long back, we have been using it for Saviynt. So please suggest within this connector only.

Thanks,

Diwakar.

[NM · Accepted solution]

Hi @Diwakar , try this

{"createAccountQry":["Insert into accounts(accountID,name,endpointkey,orphan,status,systemid) VALUES ('$user.username','${user.username}',${task.endpoint.id},0,'1',${task.endpoint.securitysystemkey.id});"]}

[Diwakar]

@NM Thanks a lot that query worked, and task got completed. Really appreciate your help.

However, it remains stuck in Manually Provisioned. I ran the Saviynt account import job as well, but status is not changing to Active. Can you please help to check why the account status is not changing?

Thanks,

Diwakar.

[rushikeshvartak]

• Validate your account XML and status threshold configuration to check why status is not updated.

[NM]

Hi @Diwakar , did you write accountxml to import accounts??

[Diwakar]

@rushikeshvartak @NM yes, its already there. Attaching the same. Please suggest.

[rushikeshvartak]

Configuration are correct. Did you ran account recon ?

[NM]

@Diwakar , worth checking did it create a new account? What is your account name while creating account? Is it user's username 

[Diwakar]

@rushikeshvartak I ran all this job but still not changing the status to Active.

@NM Yes it created a new account, Account name is username only see the output.

Please suggest.

[rushikeshvartak]

• Share status threshold config and logs when you ran job

[Diwakar]

Status threshold is empty, we have not provided! Please suggest if it required?

Thanks,

Diwakar.

[NM]

See the endpoint name is different for which endpoint are you trying to create the account ?? If saviynt pass saviynt endpoint key

[Diwakar]

@NM Endpoint name is correct only check below. But Saviynt endpoint key is showing 3 in url. Hope that is not causing the issue! as in create account JSON query u put 1, I believe!!!

[rushikeshvartak]

Yes that is issue

Please update JSON as per your configuration

{"createAccountQry":["Insert into accounts(accountID,name,created_on,creator,endpointkey,orphan,status,systemid,updatedate) VALUES ('','${user.username}',utc_timestamp(),'System Generated',${task.endpoint.id},0,'1',${task.endpoint.securitysystemkey.id},utc_timestamp());"]}

[rushikeshvartak]

• Update create Account JSON as mentioned below
• {"createAccountQry":["Insert into accounts(accountID,name,created_on,creator,endpointkey,orphan,status,systemid,updatedate) VALUES ('','${user.username}',utc_timestamp(),'System Generated',${task.endpoint.id},0,'1',${task.endpoint.securitysystemkey.id},utc_timestamp());"]}

STATUS_THRESHOLD_CONFIG can be blank.

create new request and try

@Diwakar 

[Diwakar]

I overlooked your first response. I will make the changes and update.

******Please ignore this****

Sure, Do we need to use the Saviynt endpoint key as '3' instead {task.endpoint.id},0,'1' here ?

[Diwakar]

@NM @rushikeshvartak Made the changes as suggested in create Account JSON and currently using this {"createAccountQry":["Insert into accounts(accountID,name,created_on,creator,endpointkey,orphan,status,systemid,updatedate) VALUES ('','${user.username}',utc_timestamp(),'System Generated',${task.endpoint.id},0,'3',${task.endpoint.securitysystemkey.id},utc_timestamp());"]}

and then ran the account import job but status remain stuck in Manually Provisioned. Please suggest next.

[rushikeshvartak]

Share screenshot of account is created under endpointkey =3 from data analyzer and also share output of below query

select distinct u.username as accountname, 'Saviynt' as name, 'Saviynt' as endpointname,u.username as accountcn, 'SAVRole' 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  and u.username='AAAA'

update username

[Diwakar]

@rushikeshvartak Please find the below screenshot of account that is created under endpoint key=3

and I tried to ran the query provided by you in analyzer but its giving error.

Please suggest next.

[rushikeshvartak]

Run query from analytics. Also does account have sav role assigned ? if not add one sav role and validate

[Diwakar]

@rushikeshvartak I ran the provided query in analytics but there we are seeing below error.

Also, one the user is already added to one Sav role but still account status is stuck on Manually provisioned, please suggest and help to fix the issue.

[rushikeshvartak]

select distinct u.username as accountname, 'Saviynt' as name, 'Saviynt' as endpointname,u.username as accountcn, 'SAVRole' as attribute, s.rolename as entitlementvalue,u.username as username, u.statuskey as s, u.updatedate d,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  and u.username='AAAA'

[NM]

@Diwakar use alias for status and updatedate 

Eg select status as st

[Diwakar]

@rushikeshvartak @NM Thanks for the update, I ran the query for one of the user where Saviynt Account is showing manually provisioned however it's not giving me any output. 

Warning! Query returned no records

Now please suggest what could be the issue, please help to fix.

[rushikeshvartak]

Yes because user don't have any sav role assigned 

[rushikeshvartak]

SELECT DISTINCT
u.username AS accountname,
'Saviynt' AS name,
'Saviynt' AS endpointname,
u.username AS accountcn,
'SAVRole' AS attribute,
s.rolename AS entitlementvalue,
u.username AS username,
u.statuskey AS s,
u.updatedate AS d,
u.username AS objectguid
FROM
users u
LEFT JOIN
user_savroles us ON u.userkey = us.userkey
LEFT JOIN
savroles s ON us.rolekey = s.rolekey
WHERE
s.statuskey = 1
AND u.username = 'AAAA';

[Diwakar]

@rushikeshvartak Now after adding sav role to one of the accounts, Saviynt account status changed to Active, but is this works like that? Without any sav role Account status can't be in Active or Inactive state?

[rushikeshvartak]

• Account should list in query. use updated query with left join so without sav roles also account status will be updated

SELECT DISTINCT
u.username AS accountname,
'Saviynt' AS name,
'Saviynt' AS endpointname,
u.username AS accountcn,
'SAVRole' AS attribute,
s.rolename AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate ,
u.username AS objectguid
FROM
users u
LEFT JOIN
user_savroles us ON u.userkey = us.userkey
LEFT JOIN
savroles s ON us.rolekey = s.rolekey
WHERE
s.statuskey = 1
AND u.username = 'AAAA';

[Diwakar]

@rushikeshvartak thanks, but where should we use this suggested new query and how does it will change the account status? as Account status is changing based on the job we ran.

We need to change the Saviynt account status to (Active, Inactive) even when any Sav role is not assigned.

Thanks,

Diwakar.

[rushikeshvartak]

Use updated left join query

[Diwakar]

I am sorry but I didn't get you, where should I use this left join query to make the Saviynt account status changed to Active or Inactive for all users after running the Account import Job? 

[rushikeshvartak]

Update Account XML as below 

<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,
'SAVRole' AS attribute,
s.rolename AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate ,
u.username AS objectguid
FROM
users u
LEFT JOIN
user_savroles us ON u.userkey = us.userkey
LEFT JOIN
savroles s ON us.rolekey = s.rolekey
WHERE
s.statuskey = 1		 ]]>
    </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>

[Diwakar]

Thanks, Rushi for the query. I updated the same in Accounts Import of SaviyntforSaviynt connection and then tested with one of the Saviynt Account where status is Manually Suspended. So, after running the account import job status still not changed to Inactive. Please suggest if any other config changes needed!

[rushikeshvartak]

• I hope its xml and not only SQL Query
• Run SQL Query from analytics and validate if account is coming in result

[Diwakar]

• I hope its xml and not only SQL Query---Yes, its complete XML.
• Run SQL Query from analytics and validate if account is coming in result---When I ran your query in analytics. Its, not giving me the output of that user. As I believe you put s.status key=1.  So, to update the account status of all users irrespective of their status, we have don't have use this filter s.status key=1 right? 
• When I updated the account import JSON with specific user without s.status key then account status changed to Inactive after running Account import JOB.
• So please let me know which query to use to update the account status of all users?

[rushikeshvartak · Accepted solution]

Remove status condition from where condition

[Diwakar]

Thanks a lot, @rushikeshvartak it worked. For Saviynt you are an ocean of knowledge.