Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Query to extract username, azure ad accname, parent entitlements , child entitlements

Saviyntreh
New Contributor III
New Contributor III

Query to extract username, azure ad accname, parent entitlements , child entitlements for n number of users.

3 REPLIES 3

PremMahadikar
Valued Contributor
Valued Contributor

Hi @Saviyntreh ,

Please try the below query

SELECT
	U.USERNAME AS 'USERNAME',
	CASE 
		WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE' 
		WHEN U.STATUSKEY = 0 THEN 'INACTIVE' 
		WHEN U.STATUSKEY = 1 THEN 'ACTIVE' 
		ELSE U.STATUSKEY 
	END 'USER STATUS',
	A.NAME AS 'AZIUREAD ACCOUNT NAME',
	CASE 
		WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE' 
		WHEN A.STATUS = 1 THEN 'ACTIVE' 
		ELSE A.STATUS 
	END 'ACCOUNT STATUS',
	ev.entitlement_value as parentEntitlement,
	ev2.ENTITLEMENT_VALUE as childEntitlement,
	ev3.ENTITLEMENT_VALUE as grandchildEntitlement 
FROM
	ACCOUNTS A LEFT JOIN  USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
	LEFT JOIN USERS U ON U.USERKEY = UA.USERKEY
	LEFT JOIN account_entitlements1 ae ON A.ACCOUNTKEY=AE.ACCOUNTKEY
	LEFT JOIN entitlement_values ev ON ae.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
	LEFT JOIN entitlements2 e2 ON ev.entitlement_valuekey = e2.entitlement_value1key
	LEFT JOIN entitlement_values ev2 ON ev2.entitlement_valuekey = e2.entitlement_value2key
	LEFT JOIN entitlements2 e3 ON e2.entitlement_value2key = e3.entitlement_value1key
	LEFT JOIN entitlement_values ev3 ON ev3.entitlement_valuekey = e3.entitlement_value2key 

 

If this answers your question, consider selecting Accept As Solution and hit Kudos

Saviyntreh
New Contributor III
New Contributor III

would you please include entitlement type, end point name

@Saviyntreh ,

SELECT
	U.USERNAME AS 'USERNAME',
	CASE 
		WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE' 
		WHEN U.STATUSKEY = 0 THEN 'INACTIVE' 
		WHEN U.STATUSKEY = 1 THEN 'ACTIVE' 
		ELSE U.STATUSKEY 
	END 'USER STATUS',
	e.ENDPOINTNAME AS APPLICATION,
	A.NAME AS 'AZUREAD ACCOUNT NAME',
	CASE 
		WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE' 
		WHEN A.STATUS = 1 THEN 'ACTIVE' 
		ELSE A.STATUS 
	END 'ACCOUNT STATUS',
	et.ENTITLEMENTNAME AS ENTITLEMENTTYPE,
	ev.entitlement_value AS parentEntitlement,
	ev2.ENTITLEMENT_VALUE AS childEntitlement,
	ev3.ENTITLEMENT_VALUE AS grandchildEntitlement 
FROM
	ACCOUNTS A LEFT JOIN  USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
	LEFT JOIN USERS U ON U.USERKEY = UA.USERKEY
	LEFT JOIN account_entitlements1 ae ON A.ACCOUNTKEY=AE.ACCOUNTKEY
	LEFT JOIN endpoints e on a.ENDPOINTKEY=e.ENDPOINTKEY
	LEFT JOIN entitlement_values ev ON ae.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
	LEFT JOIN entitlement_types et on ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY
	LEFT JOIN entitlements2 e2 ON ev.entitlement_valuekey = e2.entitlement_value1key
	LEFT JOIN entitlement_values ev2 ON ev2.entitlement_valuekey = e2.entitlement_value2key
	LEFT JOIN entitlements2 e3 ON e2.entitlement_value2key = e3.entitlement_value1key
	LEFT JOIN entitlement_values ev3 ON ev3.entitlement_valuekey = e3.entitlement_value2key 
WHERE
	e.ENDPOINTNAME='APPLICATIONNAME'

 

If this answers your question, consider selecting Accept As Solution and hit Kudos