Restricting the "Whom to Request" using a date on a SAV Role

[tmschiller]

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')"}]

[Darshanjain]

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

[tmschiller]

Unfortunately, your logic doesn't work as the expression does not result in the number of days between dates. See below.

[Darshanjain]

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

[tmschiller]

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)

[Darshanjain · Accepted solution]

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

[tmschiller · Accepted solution]

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')"}]

[Darshanjain]

Yes it was an example of how you can give you date condition not exact query to be precise.

Thanks

Darshan