Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Advanced query in Technical Rule

tmschiller
New Contributor III
New Contributor III

We are attempting to create a technical rule that should only trigger if the user doesn't already have an entitlement on their AD account, but are running in issues with the query. We have tried creating the query both inline and using joins, but still receive an "Invalid Condition" error when we preview. Below are both queries we attempted. Any help in getting this to work would be greatly appreciated.

a.customproperty14 in ('2','5') and a.employeeclass = 'Internal' and (a.customproperty32 is null or a.customproperty32 != '2147483648') and a.customproperty13 in ('A','P') and a.statuskey=1 and a.firstname is not null and a.lastname is not null and a.username not in (select u.username from users u inner join user_accounts ua on u.userkey = ua.userkey inner join accounts ac on ua.accountkey = ac.accountkey inner join endpoints e on e.endpointkey = ac.endpointkey inner join account_entitlements1 ae on ae.accountkey = ac.accountkey inner join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey inner join entitlement_types et on ev.entitlementtypekey = et.entitlementtypekey where e.endpointname in ('Active Directory') and u.statuskey = 1 and ac.status = 1 and ev.status = 1 and ev.displayname in ('Office365_LicenseRemoved'))

 

a.customproperty14 in ('2','5') and a.employeeclass = 'Internal' and (a.customproperty32 is null or a.customproperty32 != '2147483648') and a.customproperty13 in ('A','P') and a.statuskey=1 and a.firstname is not null and a.lastname is not null and a.username not in (select u.username from users u where u.statuskey = 1 and u.userkey not in (select ua.userkey from user_accounts ua where ua.accountkey in (select ac.accountkey from accounts ac where ac.status = 1 and ac.endpointkey in (select e.endpointkey from endpoints e where e.endpointname in ('Active Directory')) and ac.accountkey in (select ae.accountkey from account_entitlements1 ae where ae.entitlement_valuekey in (select ev.entitlement_valuekey from entitlement_values ev where ev.status = 1 and ev.displayname in ('Office365_LicenseRemoved'))))))

2 REPLIES 2

tmschiller
New Contributor III
New Contributor III

I have also tried the following without any success as well:

a.customproperty14 in ('2','5') and a.employeeclass = 'Internal' and (a.customproperty32 is null or a.customproperty32 != '2147483648') and a.customproperty13 in ('A','P') and a.statuskey=1 and a.firstname is not null and a.lastname is not null and a.username not in (select u.username from users u, user_accounts ua, accounts ac, endpoints e, account_entitlements1 ae, entitlement_values ev, where u.userkey = ua.userkey and ua.accountkey = ac.accountkey and e.endpointkey = ac.endpointkey and ae.accountkey = ac.accountkey and ev.entitlement_valuekey = ae.entitlement_valuekey and e.endpointname in ('Active Directory') and u.statuskey = 1 and ac.status = 1 and ev.status = 1 and ev.displayname in ('Office365_LicenseRemoved'))

Can you share logs 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.