We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Issue while creating an Analytic Report

hsoumare1
New Contributor
New Contributor

Hello,

I need an extract that is pulling Identities which have manager gap between the ID and the account :

  1. i select the username and his accountID and some values of the account (the  manager (DN of the AD account for this identity)
    then
  2. i pick the up the manager of the user and the accountID of the endpoin
    to finish i compare both to get the diff bet 1 & 2 to extract them

below the details of the analytic :

I've created a new analytic with below query :
SELECT DISTINCT
    u.username AS 'Username',
    a.customproperty6 AS 'GAD Manager Value',
    u2.username AS 'ManagerUsername',
    a2.accountid AS 'ManagerAccountID'
FROM
    users u
INNER JOIN
    user_Accounts ua ON u.userkey = ua.userkey
INNER JOIN
    Accounts a ON ua.Accountkey = a.Accountkey
LEFT JOIN
    users u2 ON u.MANAGER = u2.USERNAME
LEFT JOIN
    user_Accounts ua2 ON u2.userkey = ua2.userkey
LEFT JOIN
    Accounts a2 ON ua2.Accountkey = a2.Accountkey
WHERE
    u.statuskey = 1
    AND a.Endpointkey = 2
    AND a2.Endpointkey = 2
    and a.customproperty6  like '%CN=%'
    and a2.accountid  like '%CN=%'
    AND (a.customproperty6 not like a2.accountid OR (a.customproperty6 IS NULL AND a2.accountid IS NULL)) and a2.status=1 and a.status=1

 when i try to run it the system is running for a while then resulting to a timeout gateway:

hsoumare1_0-1701697218265.png

 


Actual behavior : timeout after a certain time 
expected behavior : analytic successfully ran

It seems my report is too huge so the system replying i timeout.

Could you please advice if its a performance issue of the environment our something wrong whith my query.

Regards
Harouna SOUMARE

4 REPLIES 4

rushikeshvartak
All-Star
All-Star

If you put limit in query does query returns expected results. 

Can you rephrase your requirement with example again ?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi Rushikesh,

 

I've already tried the limit but i'm getting a timeout whatever i reach more than LIMIT 100.

there is the requirement :

  • i want to extract the manager gap between saviynt and an AD
  • i want to compare the user manager AD dn with the user AD account Manager value
  • if diff list it

for example :

  • user A with AD account B  
  • the account B have on customproperty2 his manager  (DN of another AD account )
  • if manager marked on account B not like the DN of the manager AD account marked on the identity -> list them

Regards

Harouna SOUMARE

Try below query 

SELECT DISTINCT
    u.username AS 'Username',
    a.customproperty6 AS 'GAD Manager Value',
    u2.username AS 'ManagerUsername',
    a2.accountid AS 'ManagerAccountID'
FROM
    users u
INNER JOIN
    user_Accounts ua ON u.userkey = ua.userkey
INNER JOIN
    Accounts a ON ua.Accountkey = a.Accountkey
LEFT JOIN
    users u2 ON u.owner = u2.USERNAME
LEFT JOIN
    user_Accounts ua2 ON u2.userkey = ua2.userkey
LEFT JOIN
    Accounts a2 ON ua2.Accountkey = a2.Accountkey
WHERE
    u.statuskey = 1
    AND a.Endpointkey = 2
    AND a2.Endpointkey = 2
    and a.customproperty6  like '%CN=%'
    and a2.accountid  like '%CN=%'
    AND (a.customproperty6 not like a2.accountid OR (a.customproperty6 IS NULL AND a2.accountid IS NULL)) and a2.status=1 and a.status=1

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi Rushikesh,

This this query works better, thanks.

Regards

Harouna SOUMARE