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

Counts of Accounts in an Endpoint

Module: Accounts, Endpoint
Description

Get the counts for total, active, inactive, and co-related accounts associated with a specified endpoint.

Dynamic Input

Endpoint Name

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
E.endpointname AS ENDPOINT_NAME,
COUNT(DISTINCT A.accountkey) AS TOTAL_ACCOUNTS_COUNT,
COUNT(DISTINCT CASE
WHEN A.status IN (1, '1', 'Active', 'MANUALLY PROVISIONED')
THEN A.accountkey
END) AS ACTIVE_ACCOUNTS_COUNT,
COUNT(DISTINCT CASE
WHEN A.status IN (2, '2', 'Inactive', 'MANUALLY DEPROVISIONED')
THEN A.accountkey
END) AS INACTIVE_ACCOUNTS_COUNT,
COUNT(DISTINCT CASE
WHEN U.statuskey = 1
AND A.status IN (1, '1', 'Active', 'MANUALLY PROVISIONED')
THEN A.accountkey
END) AS ACTIVE_ACCOUNTS_WITH_ACTIVE_USERS_COUNT
FROM endpoints E
JOIN accounts A
ON A.endpointkey = E.endpointkey
LEFT JOIN user_accounts UA
ON UA.accountkey = A.accountkey
LEFT JOIN users U
ON U.userkey = UA.userkey
WHERE E.endpointname = 'Active Directory_Pre-created'
GROUP BY E.endpointname;
Comments

nan

Accounts Count of all Endpoint

Module: Accounts, Endpoint
Description

Get the count all Accounts for an Endpoint, grouped by status (print status, count(*))

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
e.endpointname AS Endpoint_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 Logical_Status,
COUNT(*) AS Count,
a.status as DB_Status
FROM accounts a
JOIN endpoints e ON a.endpointkey = e.endpointkey
GROUP BY e.endpointname, a.status
ORDER BY e.endpointname;
Comments

nan

Accounts Count of a specific Endpoint

Module: Accounts, Endpoint
Description

Get the count all Accounts of a specific Endpoint, grouped by status (print status, count(*))

Dynamic Input

Endpoint Name

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
e.endpointname AS Endpoint_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 Logical_Status,
COUNT(*) AS Count,
a.status as DB_Status
FROM accounts a
JOIN endpoints e ON a.endpointkey = e.endpointkey
WHERE e.endpointname LIKE '%ADconnection1%'
GROUP BY a.status;
Comments

nan

Query copied!