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 Customers Email History Endpoints Entitlement Values External Connections Job Control Panel Request Access Role Assignments Roles Rules Rulesets Security Systems User Accounts User Group User Groups Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLESAccounts by License Type
Module: Accounts
Description
Displays total accounts grouped by license type.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT applicensetype, COUNT(*) AS Total_Accounts FROM accounts GROUP BY applicensetype ORDER BY Total_Accounts DESC;
Comments
nan
Accounts with ARS Task
Module: Accounts
Description
Displays accounts associated with an ARS task.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountid, arstaskkey FROM accounts WHERE arstaskkey IS NOT NULL;
Comments
nan
Disabled Accounts by Endpoint
Module: Accounts
Description
Displays disabled accounts grouped by endpoint.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT endpointkey,
COUNT(*) AS Disabled_Accounts
FROM accounts
WHERE status=0
GROUP BY endpointkey
ORDER BY Disabled_Accounts DESC;
Comments
nan
Service Accounts
Module: Accounts
Description
Displays service accounts.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountkey, name, endpointkey
FROM accounts
WHERE accounttype='SERVICE';
Comments
nan
Account Count by Endpoint
Module: Accounts
Description
Displays total accounts available in each endpoint.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT endpointkey,
COUNT(*) AS Total_Accounts
FROM accounts
GROUP BY endpointkey
ORDER BY Total_Accounts DESC;
Comments
nan
Accounts by Type
Module: Accounts
Description
Displays total accounts grouped by account type.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accounttype AS Account_Type,
COUNT(*) AS Total_Accounts
FROM accounts
GROUP BY accounttype
ORDER BY Total_Accounts DESC;
Comments
nan
Accounts by Status
Module: Accounts
Description
Displays total accounts grouped by account status.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
CASE status
WHEN 1 THEN 'Active'
WHEN 0 THEN 'Inactive'
ELSE CONCAT('Unknown (',status,')')
END AS Account_Status,
COUNT(*) AS Total_Accounts
FROM accounts
GROUP BY status
ORDER BY Total_Accounts DESC;
Comments
nan
Accounts Expiring in Next 30 Days
Module: Accounts
Description
Displays accounts whose validity expires within the next 30 days.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountkey, name, endpointkey, validthrough
FROM accounts
WHERE validthrough BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY validthrough;
Comments
nan
Accounts without Last Login
Module: Accounts
Description
Displays accounts where last login date is not available.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountkey, name, endpointkey
FROM accounts
WHERE lastlogondate IS NULL;
Comments
nan
Recently Updated Accounts
Module: Accounts
Description
Displays recently updated accounts.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountkey, name, endpointkey, updatedate
FROM accounts
WHERE updatedate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY updatedate DESC;
Comments
nan
Recently Created Accounts
Module: Accounts
Description
Displays accounts created during the last 30 days.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountkey, name, endpointkey, created_on
FROM accounts
WHERE created_on >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY created_on DESC;
Comments
nan
Orphan Account Counts
Module: Accounts
Description
Get the count of orphan account in a Specific Endpoint
Dynamic Input
Endpoint Name
Execute in
Data Analyzer, Analytics
SQL Query
SELECT count(*)
FROM accounts a
LEFT JOIN user_accounts ua
ON ua.accountkey = a.accountkey
JOIN endpoints e
ON e.endpointkey = a.endpointkey
WHERE ua.userkey IS NULL
AND e.endpointname = 'Active Directory_Pre-created'
AND a.status <> 'SUSPENDED FROM IMPORT SERVICE';
Comments
nan
Details of accounts that are about to expire
Module: Accounts
Description
Get all the accounts that due to expire in the next 14 days
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
u.username AS Owner,
u.FIRSTNAME AS Owners_FirstName,
ao.owneruserkey,
a.accountkey,
a.name AS 'Account name',
a.VALIDTHROUGH,
a.accounttype,
ao.rank AS Owner_Rank,
ao.accountownerkey,
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
FROM accounts a
JOIN accountowners ao
ON a.accountkey = ao.accountkey
JOIN users u
ON ao.owneruserkey = u.userkey
WHERE a.accounttype = 'Service Account'
AND ao.rank = 1
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND DATEDIFF(a.validthrough, CURDATE()) <= 14
UNION
SELECT
u.username AS Owner,
u.FIRSTNAME AS Owners_FirstName,
ao.owneruserkey,
a.accountkey,
a.name AS 'Account name',
a.VALIDTHROUGH,
a.accounttype,
ao.rank AS Owner_Rank,
ao.accountownerkey,
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
FROM accounts a
JOIN accountowners ao
ON a.accountkey = ao.accountkey
JOIN usergroup_users ugu
ON ao.OWNERUSERGROUPKEY = ugu.USER_GROUPKEY
JOIN users u
ON ugu.USERKEY = u.userkey
WHERE a.accounttype = 'Service Account'
AND ao.rank = 1
AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND DATEDIFF(a.validthrough, CURDATE()) <= 14;
Comments
nan
Query copied!