Error while running the analytics report in Saviynt

adityachadde
New Contributor III
New Contributor III

Hi,

We are trying to run the analytics report in Saviynt and getting an error as below when we are using the below query:

SELECT username,
accounts.customproperty6 AS UPN,
accounts.displayname,
accounts.CREATED_ON AS 'Account Creation Date',
lastlogondate,
users.customproperty45 AS 'Special leave start Date',
users.customproperty46 AS 'Special leave end Date'
FROM accounts,
users,
user_accounts
WHERE users.userkey = user_accounts.userkey
AND user_accounts.accountkey = accounts.accountkey
AND ( users.customproperty45 IS NOT NULL
AND users.customproperty46 IS NOT NULL )
AND endpointkey = '9'

adityachadde_0-1684408247473.png

but when we are replacing the date with d_a_t_e and using the below query then we are able to run the report successfully.

SELECT username,
accounts.customproperty6 AS UPN,
accounts.displayname,
accounts.CREATED_ON AS 'Account Creation Date',
lastlogondate,
users.customproperty45 AS 'Special leave start D_a_t_e',
users.customproperty46 AS 'Special leave end D_a_t_e'
FROM accounts,
users,
user_accounts
WHERE users.userkey = user_accounts.userkey
AND user_accounts.accountkey = accounts.accountkey
AND ( users.customproperty45 IS NOT NULL
AND users.customproperty46 IS NOT NULL )
AND endpointkey = '9'

what could be the issue why we are getting above mentioned error?

Best Regards,

Aditya Chadde

2 REPLIES 2

nimitdave
Saviynt Employee
Saviynt Employee

Thats expected keyword date and anything else ending with date is not allowed as the same is keyword in elastic search so instead of termdate you can use dateoftermination.

Try like below

SELECT username,
accounts.customproperty6 AS UPN,
accounts.displayname,
accounts.CREATED_ON AS 'Account Creation Date',
lastlogondate,
users.customproperty45 AS 'Special Leave Starting Date',
users.customproperty46 AS 'Special leave Ending Date'
FROM accounts,
users,
user_accounts
WHERE users.userkey = user_accounts.userkey
AND user_accounts.accountkey = accounts.accountkey
AND ( users.customproperty45 IS NOT NULL
AND users.customproperty46 IS NOT NULL )
AND endpointkey = '9'

Thanks

Thanks,
Devang Gandhi
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.