Account Entitlement Mappings that got created for an endpoint in last 1 day

[AS5278]

Hi All,

We have a requirement where for an application we want to monitor those users who got access to entitlements outside of Saviynt i.e they did not raise a request in Saviynt but got the entitlement assigned directly in target application. 

Is there any way we can generate a report of such user account and entitlement mapping in Saviynt?.

I though of an approach:

If we get all the account entitlement mappings that got reconciled in Saviynt for today and then look back in Saviynt to see if there is a request associated with that account-entitlement mapping...maybe we can achieve this.

But I am unable to frame the SQL query for this.

Any help is appreciated.

Thanks,

Atul Singh

[rushikeshvartak · Accepted solution]

SELECT *
FROM   (SELECT CASE
                 WHEN a.arstaskkey IS NULL THEN 'Account is Out of Band'
               END                                  AS OutofbandStatus,
               u.username                           AS 'USER Name',
               Concat(u.firstname, ' ', u.lastname) AS 'Display NAME',
               u.departmentname                     AS 'DEPARTMENT',
               a.NAME                               AS
               'APPLICATION ACCOUNT NAME',
               CASE
                 WHEN a.status = 1 THEN 'ACTIVE'
                 WHEN a.status = 'Manually Provisioned' THEN 'ACTIVE'
                 WHEN a.status = 0 THEN 'INACTIVE'
               END                                  AS 'ACCOUNT STATUS',
               ' '                                  AS 'ENTITLEMENT NAME',
               ss.systemname                        AS 'SECURITY SYSTEM',
               e.endpointname                       AS 'APPLICATION',
               e.endpointkey
        FROM   users u
               JOIN user_accounts ua
                 ON u.userkey = ua.userkey
               JOIN accounts a
                 ON a.accountkey = ua.accountkey
               JOIN endpoints e
                 ON e.endpointkey = a.endpointkey
               JOIN securitysystems ss
                 ON ss.systemkey = e.securitysystemkey
        WHERE  a.arstaskkey IS NULL
        UNION ALL
        SELECT CASE
                 WHEN ae.arstaskkey IS NULL THEN 'Entitlement is Out of Band'
               END                                  AS COMMENTS,
               u.username                           AS 'USER Name',
               Concat(u.firstname, ' ', u.lastname) AS 'Display NAME',
               u.departmentname                     AS 'DEPARTMENT',
               a.NAME                               AS
               'APPLICATION ACCOUNT NAME',
               CASE
                 WHEN a.status = 1 THEN 'ACTIVE'
                 WHEN a.status = 'Manually Provisioned' THEN 'ACTIVE'
                 WHEN a.status = 0 THEN 'INACTIVE'
               END                                  AS 'ACCOUNT STATUS',
               ev.entitlement_value                 AS 'ENTITLEMENT NAME',
               ss.systemname                        AS 'SECURITY SYSTEM',
               e.endpointname                       AS 'APPLICATION',
               e.endpointkey
        FROM   users u
               JOIN user_accounts ua
                 ON u.userkey = ua.userkey
               JOIN accounts a
                 ON a.accountkey = ua.accountkey
               JOIN account_entitlements1 ae
                 ON ae.accountkey = a.accountkey
               JOIN entitlement_values ev
                 ON ev.entitlement_valuekey = ae.entitlement_valuekey
               JOIN endpoints e
                 ON e.endpointkey = a.endpointkey
               JOIN securitysystems ss
                 ON ss.systemkey = e.securitysystemkey
        WHERE  ae.arstaskkey IS NULL) X
ORDER  BY 'APPLICATION ACCOUNT NAME',
          'APPLICATION' 

[AS5278]

@rushikeshvartak Thank you for the query. I think this will help.

However, there are two points which I am confused about.

1). How do I skip the analytics from including the already existing accounts, and account-entitlement mapping. I mean those which exist from even before Saviynt came into picture.

Maybe I can associate a dummy taskkey with already existing account-entitlement mapping. But I am not sure how to do this...

2). This analytics report will give me all of the 'Out of Band' access every time it runs. We want to detect the new ones i.e we want to detect the Out of band access on a daily basis. Is this achievable somehow?.

Thanks,

Atul Singh

[rushikeshvartak]

1). How do I skip the analytics from including the already existing accounts, and account-entitlement mapping. I mean those which exist from even before Saviynt came into picture.

Maybe I can associate a dummy taskkey with already existing account-entitlement mapping. But I am not sure how to do this...

 >> use Rebaseline Job which add baseline task ID

Refer https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter02-Identity-Repository/Viewi... 

2). This analytics report will give me all of the 'Out of Band' access every time it runs. We want to detect the new ones i.e we want to detect the Out of band access on a daily basis. Is this achievable somehow?.

--> One time you need to run rebaseline job for application it will consider all access are baseline after that any entitlement for account does not have taskkey will be out of band

[AS5278]

@rushikeshvartak 

Ok, thanks. I got it. Is there any way to skip a specific entitlement type from this out of band access detection?. Can I add dummy taskkey via enhanced query for a specific entitlementtype?. So that I can run this enhanced query job every time before the actual import job is run?.

Thanks,

Atul Singh

[rushikeshvartak]

•  Is there any way to skip a specific entitlement type from this out of band access detection?. - NO
• Can I add dummy taskkey via enhanced query for a specific entitlementtype?. So that I can run this enhanced query job every time before the actual import job is run?. --> Why you want to do custom if product have functionality ? , You can ignore entitlement type using custom OutOf Band Report

[rushikeshvartak]

✅👍Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.

[NM]

Hi @AS5278 , to add dummy taskkey key you can use out of band functionality present under endpoint.

2) maybe you can utilise jobid field in the table and pick the job start time