Hello Team,
We are working on preparing Runtime Analytics to prepare the reportee details.
Query :
'SELECT
u.username AS 'USERNAME',
m.username AS 'MANAGER USERNAME',
CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS 'USER STATUS',
accounts.name AS 'ACCOUNT NAME',
endpoints.ENDPOINTNAME AS 'ENDPOINT NAME',
CASE WHEN accounts.STATUS IS NULL THEN 'NOT AVAILABLE' WHEN accounts.STATUS = 1 THEN 'ACTIVE' ELSE accounts.STATUS END 'ACCOUNT STATUS',
ev.entitlement_value AS 'ENTITLEMENT NAME'
FROM
users u
LEFT JOIN user_accounts ua ON ua.userkey = u.userkey
LEFT JOIN accounts ON accounts.accountkey = ua.accountkey
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN account_entitlements1 ae ON ae.accountkey = accounts.ACCOUNTKEY
LEFT JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
LEFT JOIN users m ON m.userkey = u.manager
WHERE
u.statuskey = 1
AND accounts.STATUS IN (
'1', 'Active', 'Manually Provisioned'
)
AND m.username ='Manish';
Results :
Assistance Required :
If you see above screenshot, we are able to get the details as required.
Anyhow the ask is to club the data for particular endpoint and entitlements in 1 row.
i.e.
In above case, arun is a reportee for manager Manish, he has access to 4-5 endpoints.
For each endpoints he has access to 2-3 entitlements.
Is it possible to print in 1 line to get access details of 1 endpoint and assocaited entitlement access.
Please assist.
SELECT u.username AS 'USERNAME',
m.username AS 'MANAGER USERNAME',
CASE u.statuskey
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
END AS 'USER STATUS',
accounts.NAME AS 'ACCOUNT NAME',
endpoints.endpointname AS 'ENDPOINT NAME',
CASE
WHEN accounts.status IS NULL THEN 'NOT AVAILABLE'
WHEN accounts.status = 1 THEN 'ACTIVE'
ELSE accounts.status
END 'ACCOUNT STATUS',
group_concat(ev.entitlement_value separator ', ') AS 'ENTITLEMENT NAMES'
FROM users u
LEFT JOIN user_accounts ua
ON ua.userkey = u.userkey
LEFT JOIN accounts
ON accounts.accountkey = ua.accountkey
LEFT JOIN endpoints
ON endpoints.endpointkey = accounts.endpointkey
LEFT JOIN account_entitlements1 ae
ON ae.accountkey = accounts.accountkey
LEFT JOIN entitlement_values ev
ON ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et
ON et.entitlementtypekey = ev.entitlementtypekey
LEFT JOIN users m
ON m.userkey = u.manager
WHERE u.statuskey = 1
AND accounts.status IN ('1',
'Active',
'Manually Provisioned')
AND m.username = '84809'
GROUP BY u.username,
m.username,
u.statuskey,
accounts.NAME,
endpoints.endpointname,
accounts.status;
