Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
04/03/2024 12:22 PM
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
Solved! Go to Solution.
04/03/2024 07:29 PM
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'
04/04/2024 01:35 AM
@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
04/04/2024 11:56 AM
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
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
04/04/2024 01:26 PM
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
04/04/2024 09:10 PM
06/18/2024 10:29 AM
✅👍Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.
04/04/2024 05:27 AM - edited 04/04/2024 05:35 AM
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