Intelligence:

[Alex_Terry]

Hello, I am using the below SQL to query some data and I'm getting an error when I run it. 

Query:

SELECT curdate() as 'Date', a.endpointkey AS 'Endpoint Key', e.endpointname AS 'Endpoint Name', a.accountkey AS 'Account Key', CASE WHEN a.endpointkey = 3 THEN a.customproperty33 WHEN a.endpointkey = 4 THEN a.name WHEN a.endpointkey = 5 THEN a.description ELSE NULL END as 'User Principal Name', a.privileged as 'Privileged Status', a.status as 'Account Status', u.userkey as 'Saviynt User Key', u.username as 'Employee ID', u.firstname as 'First Name', u.lastname as 'Last Name', u.statuskey as 'User Status', u.email as 'Email Address', u.costcenter as 'Cost Centre', u.customproperty4 as 'Manager Employee ID', u.customproperty3 as 'Manager Name', u2.email as 'Manager Email' 
FROM accounts a 
INNER JOIN user_accounts ua 
    ON ua.accountkey = a.accountkey 
INNER JOIN users u 
    ON u.userkey = ua.userkey 
INNER JOIN endpoints e 
    ON e.endpointkey = a.endpointkey 
INNER JOIN users u2 
    ON u.customproperty4 = u2.username 
WHERE e.status = 1

 Error:

I've waited to see if I'm notified and I'm not. Also when I do a dry run I get the data required.

Can you assist?

[AmitM]

Hi @Alex_Terry , It looks good to me. But can you still try taking down the second users join and use the where condition instead. where u.username=u.customproperty4.

I saw this issue once due to DB performance issues , see if you are also seeing listener timeout error in logs.

If yes, best to raise a freshdesk ticket , it could be some table having unexpected large data.

Thanks,

Amit

Thanks,

Amit

[CR · Accepted solution]

@Alex_Terry  try below query

SELECT curdate() as 'Datas', a.endpointkey AS 'Endpoint Key', e.endpointname AS 'Endpoint Name', a.accountkey AS 'Account Key', CASE WHEN a.endpointkey = 3 THEN a.customproperty33 WHEN a.endpointkey = 4 THEN a.name WHEN a.endpointkey = 5 THEN a.description ELSE NULL END as 'User Principal Name', a.privileged as 'Privileged State', a.status as 'Account Statae', u.userkey as 'Saviynt User Key', u.username as 'Employee ID', u.firstname as 'First Name', u.lastname as 'Last Name', u.statuskey as 'User Statse', u.email as 'Email Address', u.costcenter as 'Cost Centre', u.customproperty4 as 'Manager Employee ID', u.customproperty3 as 'Manager Name', u2.email as 'Manager Email'
FROM accounts a
INNER JOIN user_accounts ua
ON ua.accountkey = a.accountkey
INNER JOIN users u
ON u.userkey = ua.userkey
INNER JOIN endpoints e
ON e.endpointkey = a.endpointkey
INNER JOIN users u2
ON u.customproperty4 = u2.username
WHERE e.status = 1

[Alex_Terry]

@CR that's sorted it, thanks!