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

Accounts owned by Specific Users

Module: Users, Accounts
Description

Get the list of account owned by Specific Users

Dynamic Input

User Name

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
u.userkey,
u.username,
CASE
WHEN statuskey = 0 THEN 'Inactive'
WHEN statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS User_Status,
a.accountkey,
a.name AS Account_Name,
a.displayname AS Account_Display_Name,
a.accounttype,
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,
a.created_on AS Account_Creation_Date,
a.validfrom,
a.validthrough
FROM users u
JOIN user_accounts ua
ON u.userkey = ua.userkey
JOIN accounts a
ON ua.accountkey = a.accountkey
WHERE u.username = 'john.doe'
ORDER BY u.userkey;
Comments

nan

Accounts owned by Users

Module: Users, Accounts
Description

Get the list of account owned by Users

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
u.userkey,
u.username,
CASE
WHEN statuskey = 0 THEN 'Inactive'
WHEN statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS User_Status,
a.accountkey,
a.name AS Account_Name,
a.displayname AS Account_Display_Name,
a.accounttype,
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,
a.created_on AS Account_Creation_Date,
a.validfrom,
a.validthrough
FROM users u
JOIN user_accounts ua
ON u.userkey = ua.userkey
JOIN accounts a
ON ua.accountkey = a.accountkey
ORDER BY u.userkey;
Comments

nan

Users having missing Account Attribute

Module: Users, Accounts
Description

Get all users who have account in a particular endpoint but their one of the account attribute is empty

Dynamic Input

Endpoint Name, Account Attribute

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
WHERE e.endpointname = 'AWSl100'
AND (
a.customproperty1 IS NULL
OR a.customproperty1 = ''
);
Comments

nan

Query copied!