Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

How to pass dynamic attribute in Analytic Actionable Report

Ashir
New Contributor
New Contributor

Hi Everyone,

We are trying to pass dynamic attributes in the query. Because we have a entitlements in the system with the systemUserName inside the entitlements.

Example : datacenter.xxxgroupinc.net\abc-s here abc is the systemUsername and Account name.

For every user systemUserName is different. In System we have different entitlements appended with systemUsername.

Example: datacenter.xxxgroupinc.net\abc-s, datacenter.xxxgroupinc.net\kiran-s, datacenter.xxxgroupinc.net\xyz-s

Here is the query.

Please can some of you suggest any idea.

SELECT
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value,
a.accountkey AS acctkey,
a.name AS accname,
u.username,
a.endpointkey,
u.userkey AS userkey,
'provision access' AS default_action_for_analytics
FROM
entitlement_values ev
JOIN
entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN
accounts a ON a.accountkey IN (
SELECT accountkey
FROM accounts
WHERE endpointkey = (
SELECT endpointkey
FROM endpoints
WHERE endpointname = 'Secret Server'
)
)
JOIN
user_accounts ua ON ua.accountkey = a.accountkey
JOIN
users u ON u.userkey = ua.userkey
WHERE
u.username IN (
SELECT u.username
FROM
users u
JOIN
user_accounts ua ON u.userkey = ua.userkey
JOIN
accounts a ON ua.accountkey = a.accountkey
JOIN
account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
JOIN
entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
WHERE
a.status = 1
AND e.endpointname = 'Secret Server'
AND u.statuskey = 1
AND u.username = '411043'
AND ev.entitlement_value NOT IN ('datacenter.radiangroupinc.net\\,'u.systemUserName',-s')
)
AND ev.entitlement_value in ('datacenter.radiangroupinc.net\\,'u.systemUserName',-s')
AND a.status IN ('1', 'Manually Provisioned');

1 REPLY 1

rushikeshvartak
All-Star
All-Star

SELECT
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value,
a.accountkey AS acctkey,
a.name AS accname,
u.username,
a.endpointkey,
u.userkey AS userkey,
'provision access' AS default_action_for_analytics
FROM
entitlement_values ev
JOIN
entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN
accounts a ON a.accountkey IN (
SELECT accountkey
FROM accounts
WHERE endpointkey = (
SELECT endpointkey
FROM endpoints
WHERE endpointname = 'Secret Server'
)
)
JOIN
user_accounts ua ON ua.accountkey = a.accountkey
JOIN
users u ON u.userkey = ua.userkey
WHERE
u.username IN (
SELECT u.username
FROM
users u
JOIN
user_accounts ua ON u.userkey = ua.userkey
JOIN
accounts a ON ua.accountkey = a.accountkey
JOIN
account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
JOIN
entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
WHERE
a.status = 1
AND e.endpointname = 'Secret Server'
AND u.statuskey = 1
AND u.username = '411043'
AND ev.entitlement_value NOT LIKE CONCAT('datacenter.radiangroupinc.net\\', u.systemUserName, '-s')
)
AND ev.entitlement_value LIKE CONCAT('datacenter.radiangroupinc.net\\', u.systemUserName, '-s')
AND a.status IN ('1', 'Manually Provisioned');


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.