Error while running the analytics report in Saviynt

[adityachadde]

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'

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

[nimitdave]

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.

[dgandhi]

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