Click HERE to see how Saviynt Intelligence is transforming the industry. |
03/29/2023 07:24 AM
We need to be able to restrict users of this role from being able to make requests for users whose are not of the employeeclass External and have enddate that isn't more that one year old. The query does work in the analyzer, but does not work when we implement it in the SAV Role (no users are displayed). How can be best implement this? Thanks.
[{ "for": "UpdateUserRequest", "query": "select a from Users a where a.employeeclass = 'External' and a.enddate > date_sub(curdate(), interval 1 year)"}, { "for": "ViewExistingAccess,RequestAccessforOthers, RequestAccessOthersMultiUser", "query": "select a from Users a where a.statuskey = 1 and a.employeeclass in ('Internal','External')"}]
Solved! Go to Solution.
03/29/2023 11:56 PM - edited 03/30/2023 02:31 AM
Hi @tmschiller
Yes, the query was failing as date_sub function doesnt work in HQL. ( Ita HQL limitation ).
Use the query like below
[{"for": "UpdateUserRequest","query": "select a from Users a where a.employeeclass = 'External' and (curdate()-a.enddate) <365"}, { "for": "ViewExistingAccess,RequestAccessforOthers, RequestAccessOthersMultiUser", "query": "select a from Users a where a.statuskey = 1 and a.employeeclass in ('Internal','External')"}]
Thanks
Darshan
03/30/2023 05:08 AM
Unfortunately, your logic doesn't work as the expression does not result in the number of days between dates. See below.
03/30/2023 05:31 AM
Hi @tmschiller
As already above said the tool checks for HQL ( not sql ), in backend it will convert it into days and check. Did you try to put this query in sav role and check, it will give you the correct results. ( for data analyzer check you can check with the old query you were using ).
Thanks
Darshan
03/30/2023 05:35 AM
Yes, I put it in the SAV Role and it DID NOT give me the correct results. The correct number should be 7887, but with the query you provided, I get all the External users (10191)
03/30/2023 10:03 PM
Hi @tmschiller
Yes you are correct, it was not checking that condition. Please use the below json
[{"for": "UpdateUserRequest","query": "select a from Users a where a.employeeclass = 'Internal' and datediff(now(),a.enddate)BETWEEN 0 AND 365"}, { "for": "ViewExistingAccess,RequestAccessforOthers, RequestAccessOthersMultiUser", "query": "select a from Users a where a.statuskey = 1 and a.employeeclass in ('Internal','External')"}]
Thanks
Darshan
04/03/2023 05:15 AM
I had to make a change to your solution as it didn't take into account for external users that have a future enddate. Correct solutions is:
[{"for": "UpdateUserRequest","query": "select a from Users a where a.employeeclass = 'External' and (datediff(now(), a.enddate) BETWEEN 0 AND 365 OR a.enddate >= now())"}, { "for": "ViewExistingAccess,RequestAccessforOthers, RequestAccessOthersMultiUser", "query": "select a from Users a where a.statuskey = 1 and a.employeeclass in ('Internal','External')"}]
04/03/2023 05:21 AM
Yes it was an example of how you can give you date condition not exact query to be precise.
Thanks
Darshan