Modules

Browse queries by module.

All Modules

ARS Requests ARS Tasks Accounts Accounts, Endpoint Accounts, Endpoints Analytics Audit Logs Audit Trail Campaign Certification Connections Connections, Job Control Panel Endpoints Job Control Panel Roles Rules Security Systems User Group Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLES

Users having specific Entitlements

Module: Users, Entitlements
Description

Retrieve all users who possess an account in a specified endpoint and have a specific entitlement assigned to that account.

Dynamic Input

Endpoint Name, Entitlement Value Key

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT u.userkey,
u.username,
u.firstname,
u.lastname,
u.email,
a.accountkey,
a.name AS ACCOUNTNAME,
a.displayname AS DISPLAY_NAME,
CASE
WHEN a.status = 1 THEN 'Active'
WHEN a.status = 2 THEN 'Inactive'
WHEN a.status = 3 THEN 'Decommission Active'
WHEN a.status = 4 THEN 'Decommission Inactive'
WHEN a.status = 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN a.status = 'Manually Suspended' THEN 'Manually Suspended'
WHEN a.status = 'SUSPENDED FROM IMPORT SERVICE' THEN 'Deleted'
ELSE a.status
END AS ACCOUNT_STATUS,
e.endpointname
FROM users u
JOIN user_accounts ua
ON u.userkey = ua.userkey
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints e
ON a.endpointkey = e.endpointkey
JOIN account_entitlements1 ae
ON a.accountkey = ae.accountkey
WHERE e.endpointname = 'TechOne_Staff'
AND ae.entitlement_valuekey = 125031;
Comments

nan

Users having no Entitlements

Module: Users, Entitlements
Description

Get all users who have account in a particular endpoint but they don't have entitlements tagged on that account

Dynamic Input

Endpoint Name

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT u.userkey,
u.username,
u.firstname,
u.lastname,
u.email,
a.accountkey,
a.name AS ACCOUNTNAME,
a.displayname AS DISPLAY_NAME,
CASE
WHEN a.status = 1 THEN 'Active'
WHEN a.status = 2 THEN 'Inactive'
WHEN a.status = 3 THEN 'Decommission Active'
WHEN a.status = 4 THEN 'Decommission Inactive'
WHEN a.status = 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN a.status = 'Manually Suspended' THEN 'Manually Suspended'
WHEN a.status = 'SUSPENDED FROM IMPORT SERVICE' THEN 'Deleted'
ELSE a.status
END AS ACCOUNT_STATUS,
e.endpointname
FROM users u
JOIN user_accounts ua
ON u.userkey = ua.userkey
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints e
ON a.endpointkey = e.endpointkey
LEFT JOIN account_entitlements1 ae
ON a.accountkey = ae.accountkey
WHERE e.endpointname = 'AWSl100'
AND ae.accountkey IS NULL;
Comments

nan

Query copied!