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');
