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

SQL Query to get All User account and entitlements report

sangitaladi
Regular Contributor
Regular Contributor

Hi Team

 

Can someone please help me with getting SQL Query to get All User account and entitlements report.

 

Regards

Sangita ladi

2 REPLIES 2

PremMahadikar
Valued Contributor
Valued Contributor

Hi @sangitaladi ,

 

SELECT 
	U.USERNAME 'USERNAME', 
	U.email 'EMAIL',
	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.DISPLAYNAME AS 'APPLICATION DISPLAY NAME',
	A.NAME AS 'APPLICATION 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 'ACCESS',
	CASE 
		WHEN ev.STATUS IS NULL THEN 'NOT AVAILABLE' 
		WHEN ev.STATUS = 1 THEN 'ACTIVE' 
		WHEN ev.STATUS = 2 then 'Inactive' 
		ELSE ev.STATUS 
	END 'ENTITLEMENT STATUS' 
FROM 
	ACCOUNTS A, 
	USER_ACCOUNTS UA, 
	USERS U, 
	ENDPOINTS E, 
	Entitlement_values ev, 
	account_entitlements1 ae1 	
WHERE 
	A.ACCOUNTKEY = UA.ACCOUNTKEY 
	AND U.USERKEY = UA.USERKEY 
	AND A.ENDPOINTKEY = E.ENDPOINTKEY 
	AND ae1.accountkey = a.accountkey 
	AND ae1.entitlement_valuekey = ev.entitlement_valuekey
	AND U.USERNAME NOT IN ('admin')

 

 

If this helps, please consider selecting Accept As Solution and hit Kudos

rushikeshvartak
All-Star
All-Star

Refer https://forums.saviynt.com/t5/identity-governance/reporting-about-entitlement-and-account-list/m-p/4... 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.