Published on March 21, 2021.

Last November, I shared a few queries that make use of NetSuite's LoginAudit table. I showed how you can query that table to analyze failed login attempts, determine the most recent login for a user, and determine when a role was last used.

This past week I used the LoginAudit table to help a client with a similar problem. They wanted to determine what users, if any, have access to their account but have not logged in within the past 90 days. In this post, I'll walk you through that query.

We'll start with a query that returns all active employees that have access to the account.

SELECT
	Employee.LastName,
	Employee.FirstName
FROM
	Employee.Employee
WHERE
	( Employee.GiveAccess = 'T' )
	AND ( Employee.IsInactive = 'F' )
ORDER BY
	Employee.LastName,
	Employee.FirstName

We'll also create a query that returns all user's that have ever logged into the account.

SELECT DISTINCT
	LoginAudit.User
FROM
	LoginAudit

Next, we adjust that query so that only users that have logged in within the past 90 days are included. I covered SuiteQL's support for dates, including date arithmetic, in this post.

SELECT DISTINCT
	LoginAudit.User
FROM
	LoginAudit
WHERE
	( LoginAudit.Date >= ( SYSDATE - 90 ) )

All that's left to do is combine the two queries. We'll use the second query as a subquery, along with a NOT IN clause, to filter out the employees that have logged in.

SELECT
	Employee.LastName,
	Employee.FirstName
FROM
	Employee
WHERE
	( Employee.GiveAccess = 'T' )
	AND ( Employee.IsInactive = 'F' )
	AND ( Employee.ID NOT IN 
			(
				SELECT DISTINCT
					LoginAudit.User
				FROM
					LoginAudit
				WHERE
					( LoginAudit.Date >= ( SYSDATE - 90 ) )			
			)
		)	
ORDER BY
	Employee.LastName,
	Employee.FirstName

And there you have it. The query will return a list of all active employees, that have been granted access to the account, but have not logged in within the past 90 days.