Account_Entitlements1 Invalid SQL Syntax Error in Certification Configuration

[cedric]

Can someone please help me understand why I am getting an "Invalid SQL Syntax Error" message when attempting to use the query below in the Account Entitlements1 Query section of an Entitlement Owner certification campaign?

Account Entitlements1 Query
(ae1.startdate < '2024-03-11 00:00:00') and ae1.accountkey in (select accountkey from accounts where status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB, 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH'))

Below are other queries I am using in the configuration:

Entitlements Query
(ENTITLEMENTTYPEKEY=101 and (ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1) or ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUE1KEY from entitlements2 where ENTITLEMENT_VALUE2KEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1)))) or ((entitlement_value LIKE 'ABCDE') AND ENTITLEMENTTYPEKEY=14 AND (ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1) or ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUE1KEY from entitlements2 where ENTITLEMENT_VALUE2KEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1))))

Consult User Query
select u from Users u where 1=1 and u.username not in ('admin', 'systemadmin', 'SaviyntSupportAgent1', 'saviyntsupportagent2', 'saviyntsupportagent3')

[rushikeshvartak]

Error 

-accounts is not mapped [Select count(*) from com.saviynt.ecm.identitywarehouse.domain.Account_entitlements1 ae1 where (ae1.startdate < '2024-03-11 00:00:00') and ae1.accountkey in (select accountkey from accounts where status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB', 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH'))]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: accounts is not mapped [Select count(*) from com.saviynt.ecm.identitywarehouse.domain.Account_entitlements1 ae1 where (ae1.startdate < '2024-03-11 00:00:00') and ae1.accountkey in (select accountkey from accounts where status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB', 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH'))]

In this corrected query, I added a closing single quote after 'ServiceAccountB', and also added commas between each service account name in the NOT IN list for clarity.

[rushikeshvartak]

Use :

Accounts Query : 

status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB', 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH')

Account Entitlements1 Query

(ae1.startdate < '2024-03-11 00:00:00')

[cedric]

Replying back to your top comment regarding the error - that was a manual error on my part when I was trying to generalize the query to remove the actual service account names. From what i'm understanding, you're saying Accounts is not mapped to AE1 so it's impossible to take the approach using my original query?

Regarding this specific comment - Originally I was using this approach you mentioned but it causes the campaign to take over 25+ minutes to launch because it's cycling through millions of ae1 records for each entitlement I am filtering by (270+ entitlements total). I was trying to optimize the approach by combining the two queries into one and putting it into the ae1 query filter.

[dgandhi]

With specifying accounts query, how many records are included in the campaign.

As if there are millions of records then in that case if it takes 25+ mins to launch then it should be good.

[cedric]

It's millions of lines in the AE1 table, not the certification. The certification we should have just under 4000 accounts.

[rushikeshvartak]

If you have performance issue with Elastic search you can raise support ticket 

[rushikeshvartak]

What is total line items you are getting from data analyzer?