Trying to get a list of which employees had netsui...
# suiteqlc
Cory Weiner
10/30/2023, 8:18 PMTrying to get a list of which employees had netsuite access, along with the date ranges and type of access (full user vs employee center) — any tips to accomplish that?
j
jen
10/30/2023, 9:00 PMHere’s a search I use. Note that I’m filtering on a particular date, but you can tweak this for your needs.
jen
10/30/2023, 9:00 PM Copy code
SELECT employee.firstname AS firstname,
employee.lastname AS lastname,
employee.id AS employee_id,
role.name AS role_name,
employee.email AS email,
role.id AS role_id
FROM employeeroles
JOIN employee ON employee.id = employeeroles.entity
JOIN role ON role.id = employeeroles.selectedrole
WHERE employee.isinactive = 'F'
AND (employee.releasedate IS NULL OR employee.releasedate > '1-Jan-2023')
AND employee.giveaccess = 'T'
ORDER BY employee.lastname, employee.firstname, role.namejen
10/30/2023, 9:00 PMThen I do this:
jen
10/30/2023, 9:01 PM Copy code
SELECT loginaudit.user AS employee_id,
loginaudit.role AS role_id,
MAX(TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS')) AS last_login
FROM loginaudit
WHERE status = 'Success'
AND detail NOT IN ('RoleSwitchLogout', 'ExplicitLogout')
GROUP BY loginaudit.user, loginaudit.role🙌 1
jen
10/30/2023, 9:01 PMand do some joiny goodness in SS
w
Watz
10/31/2023, 2:26 PMHere's a complete query that gives you all employees (that still exist in the system) that had login access on the given date.
🙌 2
6 Views