Table 'savroles' not accessible in data analyzer

[Volker]

Hello,

we have an SQL query which is running fine in analytics and now we try to rephrase this query, which was provided by the Saviynt PPS team during project implementation.

If we just copy the query from working report definition to data analyzer, the following error is the result:

The failure is triggered by "JOIN savroles usr2" (full query below)

Can anyone tell me wat we do wrong or why this table is not accessible in data analyzer?

For reference the full query I will provide the complete query:

SELECT DISTINCT 
    u1.USERNAME,
    u1.FIRSTNAME,
    u1.LASTNAME,
    u1.EMAIL,
    CASE
        WHEN u1.STATUSKEY=1 THEN 'Active'
        WHEN u1.STATUSKEY=0 THEN 'Inactive'
    END AS UserStatus,
    u4.NAME,
    u5.ENDPOINTNAME,
    u6.ENTITLEMENTNAME,
    u7.ENTITLEMENT_VALUE,
    'Deprovision Access' AS Default_Action_For_Analytics,   
    u7.ENTITLEMENT_VALUEKEY AS entvaluekey,
    u4.ACCOUNTKEY AS acctKey,
    u4.NAME AS accName,
    u1.USERKEY AS userKey
FROM 
    users u1
JOIN 
    user_accounts u3 
    ON u1.USERKEY=u3.USERKEY
JOIN 
    accounts u4 
    ON u3.ACCOUNTKEY=u4.ACCOUNTKEY
JOIN 
    endpoints u5 
    ON u4.ENDPOINTKEY=u5.ENDPOINTKEY
JOIN 
    entitlement_types u6 
    ON u5.ENDPOINTKEY=u6.ENDPOINTKEY
JOIN 
    entitlement_values u7 
    ON u6.ENTITLEMENTTYPEKEY=u7.ENTITLEMENTTYPEKEY
WHERE 
    u1.userkey in (
        SELECT DISTINCT 
            usr1.USERKEY
        FROM 
            user_savroles usr1
        JOIN 
            savroles usr2 
            ON usr1.ROLEKEY=usr2.ROLEKEY
        WHERE 
                usr2.ROLENAME='ROLE_MANAGER'
            AND usr1.userkey not in (
                SELECT DISTINCT 
                    MANAGER
                FROM 
                    users
                WHERE 
                    manager IS NOT NULL
                )
    )
    AND u5.ENDPOINTNAME='Saviynt4Saviynt'
    AND u6.ENTITLEMENTNAME='SAVRoles'
    AND u7.ENTITLEMENT_VALUE='ROLE_MANAGER'
    AND u1.statuskey=1
    AND u7.status=1
    AND u1.username not in(
        'admin',
        'SaviyntSupportAgent1',
        'SaviyntSupportAgent2',
        'SaviyntSupportAgent3'
    )
;

[AmitM · Accepted solution]

HI @Volker ,

1) Yes savroles table is not exposed in dataanalyzer. You won't find it in list of tables on left side. Just not that it throws error when you query. There are other tables also not exposed for security reasons and also some columns in some tables also not exposed.

2) You can use below to test your query :

SELECT DISTINCT
u1.USERNAME,
u1.FIRSTNAME,
u1.LASTNAME,
u1.EMAIL,
CASE
WHEN u1.STATUSKEY=1 THEN 'Active'
WHEN u1.STATUSKEY=0 THEN 'Inactive'
END AS UserStatus,
u4.NAME,
u5.ENDPOINTNAME,
u6.ENTITLEMENTNAME,
u7.ENTITLEMENT_VALUE,
'Deprovision Access' AS Default_Action_For_Analytics,
u7.ENTITLEMENT_VALUEKEY AS entvaluekey,
u4.ACCOUNTKEY AS acctKey,
u4.NAME AS accName,
u1.USERKEY AS userKey
FROM
users u1
JOIN
user_accounts u3
ON u1.USERKEY=u3.USERKEY
JOIN
accounts u4
ON u3.ACCOUNTKEY=u4.ACCOUNTKEY
JOIN
endpoints u5
ON u4.ENDPOINTKEY=u5.ENDPOINTKEY
JOIN
entitlement_types u6
ON u5.ENDPOINTKEY=u6.ENDPOINTKEY
JOIN
entitlement_values u7
ON u6.ENTITLEMENTTYPEKEY=u7.ENTITLEMENTTYPEKEY
WHERE
u1.userkey in (
SELECT DISTINCT
usr1.USERKEY
FROM
user_savroles usr1

WHERE
usr1.ROLEKEY=9
AND usr1.userkey not in (
SELECT DISTINCT
MANAGER
FROM
users
WHERE
manager IS NOT NULL
)
)
AND u5.ENDPOINTNAME='Saviynt4Saviynt'
AND u6.ENTITLEMENTNAME='SAVRoles'
AND u7.ENTITLEMENT_VALUE='ROLE_MANAGER'
AND u1.statuskey=1
AND u7.status=1
AND u1.username not in(
'admin',
'SaviyntSupportAgent1',
'SaviyntSupportAgent2',
'SaviyntSupportAgent3'
)
;

savrole table was only used to use sav role name instead of key. You actually don't need that table if directly using rolekey. But it is good to use names rather keys for readability so when you are done with testing and save it to analytics, bring back that section of script.

Hope it helps !!

Br - Amit

If helped , Please ACCEPT SOLUTION and also give Kudos.

[Volker]

@AmitM thanks for your detailed explanation. I have some more questions

- is there a complete database schema reference document public available?

- How did you determine that rolekey '9' is the key for SAV role 'ROLE_MANAGER'

Thanks in advance

Edit:

Have tried in the meantime to use the way over a new analytics, but as I do not know the rows and there names, I am not able to go further.  A db schema documentation would help me a lot.

[AmitM]

HI @Volker , This is Saviynt doc - https://docs.saviyntcloud.com/bundle/EIC-Database-Schema-Reference/page/Content/Database-Schema-Refe...

But this might not sometime (edge cases) gives us required information. It needs some updates.

To find rolekey , go to Admin --> Savroles --> CLick on your Savrole and find key in url, see screen shot below. For role_admin_readonly role , rolekey is 24

On the error or warning message on update date in your latest post - as I said earlier in my post some of the columns are also blocked for confidentiality reasons.

Thanks, Amit

[rushikeshvartak]

• Navigate to Intelligence 
• Create new Analytics
• Using SQL Query
• Provide any name / category/ Risk
• Add Query
• click on Preview to validate query

Saviynt does not include all tables in data analyzer due to confidential information. you can submit idea ticket to include SAVRoles table in data analyzer

[dgandhi]

Since the table is not exposed, you can go to analytics and provided updated query and click on Preview (instead of creating it). Once the data is validated in preview mode, you can create the analytics with new query.