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

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!