Analytics config to see Rogue accesses for very high privileged entitlements

[theosveg]

I'm looking to develop a report that shows Rogue accesses for very high privileged entitlements.  Do you have any insights or suggestions on how I could go about creating this report? Thank you 

[PremMahadikar]

Hi @theosveg ,

Use the below query: (Add additional columns or filter more depending on your use-case)

SELECT 
	U.USERNAME, 
	U.FIRSTNAME, 
	U.LASTNAME, 
	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 'ACCOUNTNAME', 
	CASE 
		WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE' 
		WHEN A.STATUS = 1 THEN 'ACTIVE' 
		ELSE A.STATUS 
	END 'ACCOUNT STATUS', 
	ev.ENTITLEMENT_VALUE,
	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',
	CASE
        WHEN ev.PRIVILEGED= 0 THEN 'None'
        WHEN ev.PRIVILEGED= 1 THEN 'Very Low'
        WHEN ev.PRIVILEGED= 2 THEN 'Low'
        WHEN ev.PRIVILEGED= 3 THEN 'Medium'
        WHEN ev.PRIVILEGED= 4 THEN 'High'
        WHEN ev.PRIVILEGED= 5 THEN 'Very High'
    END as 'ENT PRIVILEGED'
FROM 
	USERS U 
	LEFT JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY 
	LEFT JOIN ACCOUNTS A ON A.ACCOUNTKEY = UA.ACCOUNTKEY 
	LEFT JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY 
	LEFT JOIN account_entitlements1 ae1 ON ae1.accountkey = a.accountkey 
	LEFT JOIN Entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
WHERE 
	U.STATUSKEY = 1 
	AND ev.PRIVILEGED=5 

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

[rushikeshvartak]

SELECT * FROM (SELECT CASE WHEN a.ARSTASKKEY IS NULL THEN 'Account is Out of Band' END AS OutofbandStatus, u.username AS 'USER Name', CONCAT(u.firstname, ' ', u.lastname) AS 'Display NAME', u.departmentname as 'DEPARTMENT', a.name AS 'APPLICATION ACCOUNT NAME', CASE WHEN a.status = 1 THEN 'ACTIVE' WHEN a.status = 'Manually Provisioned' THEN 'ACTIVE' WHEN a.status = 0 THEN 'INACTIVE' END AS 'ACCOUNT STATUS', ' ' AS 'ENTITLEMENT NAME', ss.SYSTEMNAME AS 'SECURITY SYSTEM', e.endpointname AS 'APPLICATION', e.endpointKey FROM usersu JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON a.accountkey = ua.accountkey JOIN endpoints e ON e.endpointKey = a.endpointKey JOIN securitysystems ss ON ss.systemkey = e.securitysystemkey WHERE a.arstaskkey IS NULL UNION ALL SELECT CASE WHEN ae.ARSTASKKEY IS NULL THEN 'Entitlement is Out of Band' END AS COMMENTS, u.username AS 'USER Name', CONCAT(u.firstname, ' ', u.lastname) AS 'Display NAME', u.departmentname as 'DEPARTMENT', a.name AS 'APPLICATION ACCOUNT NAME', CASE WHEN a.status = 1 THEN 'ACTIVE' WHEN a.status = 'Manually Provisioned' THEN 'ACTIVE' WHEN a.status = 0 THEN 'INACTIVE' END AS 'ACCOUNT STATUS', ev.entitlement_value AS 'ENTITLEMENT NAME', ss.SYSTEMNAME AS 'SECURITY SYSTEM', e.endpointname AS 'APPLICATION', e.endpointKey FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON a.accountkey = ua.accountkey JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey JOIN endpoints e ON e.endpointKey = a.endpointKey JOIN securitysystems ss ON ss.systemkey = e.securitysystemkey WHERE ae.arstaskkey IS NULL AND ev.PRIVILEGED=5 ) X ORDER BY 'APPLICATION ACCOUNT NAME' , 'APPLICATION'