Saviynt Forums

haardik_verma · ‎03/24/2023

Hi,

We are trying to automate the addition of users to a Saviynt UserGroup (ADMIN >> Identity Repository >> User Group) based on a condition.

Requirement: Add user in a X UserGroup if the user has a Y AD Group(entitlement) present in his AD Account.

Use Case: We want to add users in the usergroup because we wanted selective visibilty of some SOD Risks, functions and violations. We have made this X UserGroup as the owner of these selective SOD risks and rulesets.

We tried using Actionable Analytics to add users to usergroup, but it only gives user the entitlement of SaviyntForSaviynt endpoint, but does not actually add the user to the usergroup (tried running all the saviynt import jobs, but still not working)

We then tried using CustomQueryJob to fullfill the above but we are facing issues

We used the below queries in customqueryjob to add user in usergroup with key=8

1)

insert ignore into usergroup_users (userkey,USER_GROUPKEY) values ((select userkey from users where username='testusername'), 8

This worked!. It added that user to the usergroup.

2)

 insert ignore into usergroup_users (userkey,USER_GROUPKEY) values ((select distinct usr.userkey as userkey from users usr join user_accounts ua1 on usr.USERKEY=ua1.USERKEY join accounts a1 on ua1.ACCOUNTKEY=a1.ACCOUNTKEY join entitlement_types et1 on a1.ENDPOINTKEY=et1.ENDPOINTKEY and et1.endpointkey=2 join entitlement_values ev1 on ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY and ENTITLEMENT_VALUE = 'name of the AD Group here' left join account_entitlements1 ae1 on a1.ACCOUNTKEY=ae1.ACCOUNTKEY and ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY where a1.status in (1,'Active','Manually Provisioned') and ae1.ENTITLEMENT_VALUEKEY is not null), 😎

This is our actual requirement; To add users to a usergroup based on if they have an AD Group

This did not work. The customqueryjob did not give any error, so probably not a syntactical error. All this job did was to add a blank line in users tab of usergroup!

3) Also tried making slight changes like removing the alias (as userkey) in the inner query

insert ignore into usergroup_users (userkey,USER_GROUPKEY) values ((select distinct usr.userkey from users usr join user_accounts ua1 on usr.USERKEY=ua1.USERKEY join accounts a1 on ua1.ACCOUNTKEY=a1.ACCOUNTKEY join entitlement_types et1 on a1.ENDPOINTKEY=et1.ENDPOINTKEY and et1.endpointkey=2 join entitlement_values ev1 on ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY and ENTITLEMENT_VALUE = 'name of the AD Group here' left join account_entitlements1 ae1 on a1.ACCOUNTKEY=ae1.ACCOUNTKEY and ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY where a1.status in (1,'Active','Manually Provisioned') and ae1.ENTITLEMENT_VALUEKEY is not null), 😎

This also did not work. Added a blank line in the users tab of usergroup.

4) Also tried removing the 'values' and 'ignore' keyword, because of the accepted solution of @rushikeshvartak in Solved: ADD BULK User to the SAV Role-"CUSTOMQUERYJOB" - Saviynt Forums - 17839

insert into usergroup_users (userkey,USER_GROUPKEY) select usr.userkey,8 from users usr join user_accounts ua1 on usr.USERKEY=ua1.USERKEY join accounts a1 on ua1.ACCOUNTKEY=a1.ACCOUNTKEY join entitlement_types et1 on a1.ENDPOINTKEY=et1.ENDPOINTKEY and et1.endpointkey=2 join entitlement_values ev1 on ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY and ENTITLEMENT_VALUE = 'name of the AD Group here' left join account_entitlements1 ae1 on a1.ACCOUNTKEY=ae1.ACCOUNTKEY and ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY where a1.status in (1,'Active','Manually Provisioned') and ae1.ENTITLEMENT_VALUEKEY is not null

Using this query gave error in the customqueryjob.

Please kindly suggest a way to achieve our requirement.

Thanks

Thanks & Regards,

Haardik Verma

Darshanjain · ‎03/27/2023

HI @haardik_verma

You can use the below query

INSERT IGNORE INTO usergroup_users (userkey,USER_GROUPKEY)
SELECT DISTINCT usr.userkey,2
FROM users usr
JOIN user_accounts ua1 ON usr.USERKEY=ua1.USERKEY
JOIN accounts a1 ON ua1.ACCOUNTKEY=a1.ACCOUNTKEY
JOIN entitlement_types et1 ON a1.ENDPOINTKEY=et1.ENDPOINTKEY AND et1.endpointkey=2
JOIN entitlement_values ev1 ON ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY
LEFT JOIN account_entitlements1 ae1 ON a1.ACCOUNTKEY=ae1.ACCOUNTKEY AND ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY
WHERE a1.status in (1,"Active","Manually Provisioned") and ae1.ENTITLEMENT_VALUEKEY IS NOT NULL;

It may give you warnings but rows will be inserted and users will be added to user_groups. ( you need to add ent_value condition in the above sql ).

Thanks

Darshan

haardik_verma · ‎03/28/2023

Hi @Darshanjain ,

so from my 4th option, you are suggesting to just add 'IGNORE' right?

I do not see any other change..

I tried it and it worked. Thankyou

Final working customqueryjob query:

insert ignore into usergroup_users (userkey,USER_GROUPKEY)
select usr.userkey,8
from users usr
join user_accounts ua1 on usr.USERKEY=ua1.USERKEY
join accounts a1 on ua1.ACCOUNTKEY=a1.ACCOUNTKEY
join entitlement_types et1 on a1.ENDPOINTKEY=et1.ENDPOINTKEY and et1.endpointkey=2
join entitlement_values ev1 on ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY
and ENTITLEMENT_VALUE ='name of the AD Group here'
left join account_entitlements1 ae1 on a1.ACCOUNTKEY=ae1.ACCOUNTKEY and ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY
where a1.status in (1,'Active','Manually Provisioned') and ae1.ENTITLEMENT_VALUEKEY is not null

Thanks & Regards,

Haardik Verma

haardik_verma · ‎04/04/2023

Hi @Darshanjain,

As you have understood the requirement here, Can you please suggest another way to achieve this (automating adding users to a Saviynt userGroup based on presence of an AD Group) because customqueryjob is being DEPRECATED

Actually, as mentioned in the original comment, We were already trying to achieve this via analytics before, where the query was

select
u.userkey as userKey
,'Provision Access' as Default_Action_For_Analytics
,ev.ENTITLEMENT_VALUEKEY as entvaluekey
,a.ACCOUNTKEY as acctKey
,a.ACCOUNTID as accName
from users u
inner join user_accounts ua on u.USERKEY=ua.USERKEY
inner join accounts a on ua.ACCOUNTKEY=a.ACCOUNTKEY
inner join endpoints e on e.ENDPOINTKEY=a.ENDPOINTKEY and e.ENDPOINTNAME='Saviynt For Saviynt'
join entitlement_types et on e.ENDPOINTKEY=et.ENDPOINTKEY
join entitlement_values ev on ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and ev.ENTITLEMENT_VALUEKEY='**ent val key of the savforsav entitlement of usergroup**'
left join account_entitlements1 ae on a.ACCOUNTKEY=ae.ACCOUNTKEY and ev.ENTITLEMENT_VALUEKEY=ae.ENTITLEMENT_VALUEKEY
where ae.ENTITLEMENT_VALUEKEY is null and u.userkey in
(
select distinct
usr.userkey as userKey
from users usr
inner join user_accounts ua1 on usr.USERKEY=ua1.USERKEY
inner join accounts a1 on ua1.ACCOUNTKEY=a1.ACCOUNTKEY
inner join endpoints e1 on e1.ENDPOINTKEY=a1.ENDPOINTKEY and e1.ENDPOINTNAME='AD Green'
join entitlement_types et1 on e1.ENDPOINTKEY=et1.ENDPOINTKEY
join entitlement_values ev1 on ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY and ENTITLEMENT_VALUE = '**Name of the AD Green Group**'
left join account_entitlements1 ae1 on a1.ACCOUNTKEY=ae1.ACCOUNTKEY and ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY
where ae1.ENTITLEMENT_VALUEKEY is not null
)

We also tried using associated entitlement map. In that AD Group, we associated the savforsav entitlement of usergroup and when user requested for that ad group, he also got the usergroup entitlement.

The problem here was that the users were getting provisioned with the savforsav entitlement of usergroup, but were not being added in the UI usergroup. We tried running all of the import jobs of savforsav but nothing worked. We raised a freshdesk ticket regarding this savforsav sunc issue but they suggested to use customqueryjob and requested to go to forums for this sync issue.

I have raised another forum ticket for this sync issue (Saviynt for Saviynt sync issue - Saviynt Forums - 30082), meanwhile can you please suggest another way to automate adding users in usergroup? because customqueryjob is being DEPRECATED right.

Thanks & Regards,

Haardik Verma

Darshanjain · ‎04/05/2023

Hi @haardik_verma

Yes, Custom query job is being deprecated but you can still use it for time being.

Also if you are using analytics to create add access task even that should work and user should be added in the usergroup. Can you provide me the ticket number let me have a look into it.

Thanks

Darshan

haardik_verma · ‎04/05/2023

Hi @Darshanjain

Sharing the freshdesk ticket [#1604603] Saviynt for Saviynt sync issue : Customer Portal (freshdesk.com)

Thanks & Regards,

Haardik Verma

haardik_verma · ‎04/05/2023

We are trying to automate the addition and removal of users in a Saviynt User group.

We have created analytics with the below query to automate addition of users in saviynt user group based on the presence of an AD Group with the user.

select
u.userkey as userKey
,'Provision Access' as Default_Action_For_Analytics
,ev.ENTITLEMENT_VALUEKEY as entvaluekey
,a.ACCOUNTKEY as acctKey
,a.ACCOUNTID as accName
from users u
inner join user_accounts ua on u.USERKEY=ua.USERKEY
inner join accounts a on ua.ACCOUNTKEY=a.ACCOUNTKEY
inner join endpoints e on e.ENDPOINTKEY=a.ENDPOINTKEY and e.ENDPOINTNAME='Saviynt For Saviynt'
join entitlement_types et on e.ENDPOINTKEY=et.ENDPOINTKEY
join entitlement_values ev on ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and ev.ENTITLEMENT_VALUEKEY='**ent val key of the savforsav entitlement of usergroup**'
left join account_entitlements1 ae on a.ACCOUNTKEY=ae.ACCOUNTKEY and ev.ENTITLEMENT_VALUEKEY=ae.ENTITLEMENT_VALUEKEY
where ae.ENTITLEMENT_VALUEKEY is null and u.userkey in
(
select distinct
usr.userkey as userKey
from users usr
inner join user_accounts ua1 on usr.USERKEY=ua1.USERKEY
inner join accounts a1 on ua1.ACCOUNTKEY=a1.ACCOUNTKEY
inner join endpoints e1 on e1.ENDPOINTKEY=a1.ENDPOINTKEY and e1.ENDPOINTNAME='AD Green'
join entitlement_types et1 on e1.ENDPOINTKEY=et1.ENDPOINTKEY
join entitlement_values ev1 on ev1.ENTITLEMENTTYPEKEY=et1.ENTITLEMENTTYPEKEY and ENTITLEMENT_VALUE = '**Name of the AD Green Group**'
left join account_entitlements1 ae1 on a1.ACCOUNTKEY=ae1.ACCOUNTKEY and ev1.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY
where ae1.ENTITLEMENT_VALUEKEY is not null
)

In the analytics query, we are checking if the user has a particular AD Green group , and if present then provision the Saviynt for Saviynt endpoint's entitlement of the user group

So our expectation was simple; if a user has that AD Green group, then user should be added in Saviynt userGroup.

We also tried using associated entitlement map. In that AD Group, we associated the savforsav entitlement of usergroup and when user requested for that ad group, he also got the usergroup entitlement.

The problem here was that the users were getting provisioned with the savforsav entitlement of usergroup, but were not being added in the UI usergroup. We tried running all of the import jobs of savforsav but nothing worked. We raised a freshdesk ticket regarding this savforsav sync issue but they suggested to use customqueryjob and requested to go to forums for this sync issue. Hence posting here.

Use Case: We want to add users in the usergroup because we wanted selective visibilty of some SOD Risks, functions and violations. We have made this X UserGroup as the owner of these selective SOD risks and rulesets.

We have already received suggestions for alternative ways to achieve the requirement (Solved: How to bulk add users in UserGroup based on a cond... - Saviynt Forums - 28764), i.e., using customqueryjobs, which works but customqueryjob is being DEPRECATED. We want to avoid using customqueryjob and analytics as well.

We want to make it simple. We will be using the associated entitlement map method but sync issue there as well.

Please suggest what is going wrong here.

Thanks & Regards,

Haardik Verma

Darshanjain · ‎04/12/2023

Hi @haardik_verma '

As already told on the ticket, sav4sav add access task is not adding the user and we may need to check the logs, that is the actual issue so lets check that in the ticket and then only come back here, we dont want to work on duplicate things again and again.

Hope you understand

Thanks

Darshan

haardik_verma · ‎04/12/2023

Hi @Darshanjain , sorry if that latest comment on this forum seems like a reply/comment, but it actually came here after admin merged 2 tickets on forums.

One was about savforsav sync issue and other was about exploring ways to automate addition of users in saviynt usergroup.

Thanks & Regards,

Haardik Verma

Darshanjain · ‎04/18/2023

Hi @haardik_verma

The issue was resolved after adding the correct json

Current Acct import :

select distinct u.username as accountname, 'Saviynt For Saviynt' as name, 'Saviynt For Saviynt' 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_owners ugo where ug.usergroupkey=ugo.USERGROUPKEY and ugo.USERKEY =u.userkey

correct Acct import :

select distinct u.username as accountname, 'Saviynt For Saviynt' as name, 'Saviynt For Saviynt' 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 u.userkey = ugu.userkey and ugu.user_groupkey = ug.usergroupkey

Current Add access json:

"UserGroup": [
"Insert into usergroup_owners(userkey,usergroupkey,rank,updatedate,updateuser) values(${user.id},(select USERGROUPKEY from user_groups where USER_GROUPNAME='${task.entitlement_valueKey.entitlement_value}'),1,utc_timestamp(),(select userkey from users where username = 'admin'))"

Correct Add access json:

UserGroup":["Insert into usergroup_users(USERKEY, USER_GROUPKEY,UPDATEDATE,UPDATEUSER) VALUES (${user.id},(select USERGROUPKEY from user_groups where USER_GROUPNAME= '${task.entitlement_valueKey.entitlement_value}'),utc_timestamp(),(select userkey from users where username = 'admin'))"

Thanks

Darshan

Darshanjain · ‎04/05/2023

Hi @haardik_verma

As you are saviynt 4saviynt rest connector , Are you the passing the correct Api to provision to add users in user group in add access json. Can you share me the add access json. ( Once analytics is run, it will create a add access task and once you provision this Api will be called )

Thanks

Darshan