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

Analytic Report is not working even query is correct

KME
New Contributor II
New Contributor II

Hello folks,

We have designed the below query to offboarded users those are offboarded/terminated in the last 30 days. the query is working fine and providing result in Data Analyser and even while dry run of the report. but if we are running it by clicking on "run now", it is throwing the below error.

KME_0-1717489088568.png

In logs, it is showing the below error:

KME_1-1717489350992.png

Analytic Query:
select u.username as SAVIYNT_USERNAME, u.employeeid as USEREMPLOYEEID, u.systemusername as USERNETID, u.email as USEREMAIL, DATE(u.enddate) AS USERENDDATE, a.name as sAMaccountname, a.customproperty2 as USERUPN, u.owner as USERMANAGER from users u join user_accounts ua on u.userkey = ua.userkey join accounts a on a.accountkey = ua.accountkey join endpoints ep on ep.endpointkey = a.endpointkey join securitysystems ss on ss.systemkey = ep.securitysystemkey where u.enddate > date_sub(now(), interval 30 day) and u.enddate < date_ADD(now(), interval 1 day) and u.statuskey = 0

Note: Instead of Now(), we tried curdate() also but both worked in Data Analyser but not in Analytic Report

Analytic configuration:

KME_0-1717489561980.png

Kindly provide any ideas on this issue.

 

 

2 REPLIES 2

Raghu
Honored Contributor
Honored Contributor

@KME  try below

 

select u.username as SAVIYNT_USERNAME, u.employeeid as USEREMPLOYEEID, u.systemusername as USERNETID, u.email as USEREMAIL, u.enddate AS USERENDDeATE, a.name as sAMaccountname, a.customproperty2 as USERUPN, u.owner as USERMANAGER from users u join user_accounts ua on u.userkey = ua.userkey join accounts a on a.accountkey = ua.accountkey join endpoints ep on ep.endpointkey = a.endpointkey join securitysystems ss on ss.systemkey = ep.securitysystemkey where u.enddate > date_sub(now(), interval 30 day) and u.enddate < date_ADD(now(), interval 1 day) and u.statuskey = 0


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

KME
New Contributor II
New Contributor II

Thanks @Raghu 

It worked out.