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 Accounts

Module: Accounts, Endpoints
Description

Get all the orphan accounts of a specific endpoint

Dynamic Input

Endpoint Name

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT a.name,
a.accountid,
a.accountkey,
e.endpointname,
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 AccountStatus,
a.LASTLOGONDATE,
a.CREATED_ON ,
'ORPHAN' AS Flag
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

Query copied!