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 ROLESUsers by Manager
Displays total users grouped by manager.
nan
Data Analyzer, Analytics
SELECT manager, COUNT(*) AS Total_Users FROM users GROUP BY manager ORDER BY Total_Users DESC;
nan
Users by Employee Type
Displays total users grouped by employee type.
nan
Data Analyzer, Analytics
SELECT employeetype, COUNT(*) AS Total_Users FROM users GROUP BY employeetype ORDER BY Total_Users DESC;
nan
Users by Location
Displays total users grouped by location.
nan
Data Analyzer, Analytics
SELECT location, COUNT(*) AS Total_Users FROM users GROUP BY location ORDER BY Total_Users DESC;
nan
Users by Region
Displays total users grouped by region.
nan
Data Analyzer, Analytics
SELECT region, COUNT(*) AS Total_Users FROM users GROUP BY region ORDER BY Total_Users DESC;
nan
Users by Department
Displays total users grouped by department.
nan
Data Analyzer, Analytics
SELECT departmentname, COUNT(*) AS Total_Users FROM users GROUP BY departmentname ORDER BY Total_Users DESC;
nan
Users by Status
Displays total users grouped by user status.
nan
Data Analyzer, Analytics
SELECT
CASE statuskey
WHEN 1 THEN 'Active'
WHEN 0 THEN 'Inactive'
ELSE CONCAT('Unknown (',statuskey,')')
END AS User_Status,
COUNT(*) AS Total_Users
FROM users
GROUP BY statuskey
ORDER BY Total_Users DESC;
nan
Recently Updated Users
Displays users updated during the last 30 days.
nan
Data Analyzer, Analytics
SELECT userkey, username, firstname, lastname, updatedate
FROM users
WHERE updatedate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY updatedate DESC;
nan
Recently Created Users
Displays users created during the last 30 days.
nan
Data Analyzer, Analytics
SELECT userkey, username, firstname, lastname, createdate
FROM users
WHERE createdate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY createdate DESC;
nan
Users with Expired End Date
Displays users whose end date has already passed.
nan
Data Analyzer, Analytics
SELECT userkey, username, firstname, lastname, enddate
FROM users
WHERE enddate IS NOT NULL
AND enddate < CURRENT_DATE();
nan
Users with Future Start Date
Displays users whose start date is in the future.
nan
Data Analyzer, Analytics
SELECT userkey, username, firstname, lastname, startdate
FROM users
WHERE startdate > CURRENT_DATE();
nan
Users without Employee ID
Displays users where employee ID is missing.
nan
Data Analyzer, Analytics
SELECT userkey, username, firstname, lastname
FROM users
WHERE employeeid IS NULL OR TRIM(employeeid) = '';
nan
Users without Email Address
Displays users where email address is not populated.
nan
Data Analyzer, Analytics
SELECT userkey, username, firstname, lastname
FROM users
WHERE email IS NULL OR TRIM(email) = '';
nan
Users created in last two days
Get all users created in the last two days
nan
Data Analyzer, Analytics
select userkey, username,createdate, email FROM users
WHERE createdate >= NOW() - INTERVAL 2 DAY;
nan
Users with more than One Account
Get the list of Users having more than one account
nan
Data Analyzer, Analytics
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,
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 u.userkey IN (
SELECT userkey
FROM user_accounts
GROUP BY userkey
HAVING COUNT(accountkey) > 1
)
ORDER BY u.username;
nan
Users updated since a particular date
Get the list of Users updated since a particular date
Date
Data Analyzer, Analytics
SELECT
userkey,
username,
CASE
WHEN statuskey = 0 THEN 'Inactive'
WHEN statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS User_Status,
CONVERT_TZ(savupdatedate, 'UTC', 'Asia/Kolkata') AS savupdatedate_ist
FROM users
WHERE savupdatedate > '2025-11-21 15:30:00.0'
ORDER BY savupdatedate DESC;
nan
Users created since a particular date
Get the list of Users created since a particular date
Date
Data Analyzer, Analytics
SELECT
userkey,
username,
CASE
WHEN statuskey = 0 THEN 'Inactive'
WHEN statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS User_Status,
CONVERT_TZ(createdate, 'UTC', 'Asia/Kolkata') AS createdate_ist
FROM users
WHERE createdate > '2025-11-21 15:30:00.0'
ORDER BY createdate DESC;
nan
Users with Inactive Managers
Get the list of users whose Manager is Inactive.
nan
Data Analyzer, Analytics
SELECT
U.USERNAME AS USER_NAME,
M.USERNAME AS MANAGER_NAME,
U.STATUSKEY AS USER_STATUS,
M.STATUSKEY AS MANAGER_STATUS
FROM USERS U
JOIN USERS M
ON U.MANAGER = M.USERKEY
WHERE M.STATUSKEY IN (0, '0', 'Inactive');
nan
Users with Managers
Get the list of users with Manager.
nan
Data Analyzer, Analytics
SELECT
u.username AS USER_NAME,
m.username AS MANAGER_NAME
FROM users u
JOIN users m
ON u.manager = m.userkey
WHERE u.manager IS NOT NULL AND m.statuskey IN (1, '1', 'Active');
nan
Users with NO Managers
Get the list of users with no Manager.
nan
Data Analyzer, Analytics
SELECT USERNAME FROM users WHERE MANAGER IS NULL OR MANAGER = '';
nan
Count of Active and Inactive Users
Get the total count of active and inactive users, grouped by statuskey (print statuskey, count(*))
nan
Data Analyzer, Analytics
SELECT
statuskey,
CASE
WHEN statuskey = 0 THEN 'Inactive'
WHEN statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS Status,
COUNT(*) AS Count
FROM users
GROUP BY statuskey;
nan