NetSuite: Use SuiteQL To Determine Roles That Are Not Being Used

I was recently asked by a client's NetSuite administrator to help them identify any active roles that have not being used recently. The admin was attempting to cleanup the NetSuite account's roles, and their goal was to identify and disable roles that aren't being used.

By using SuiteQL and querying the LoginAudit table, identifying roles that haven't been used in awhile is easy to do. Let's take a look at the queries that I developed to help with this request.

Active Roles Not Used In The Last 90 Days

The first query that I developed returns the active roles that haven't been used in the last 90 days. Here's the query.

SELECT
	Role.ID,
	Role.Name,
	Role.IsInactive
FROM 
	Role
WHERE
	( Role.IsInactive = 'F' )
	AND ( ID NOT IN
			(
				SELECT DISTINCT
					LoginAudit.Role
				FROM
					LoginAudit
				WHERE
					( LoginAudit.Date >= ( SYSDATE - 90 ) )
					AND ( LoginAudit.Role IS NOT NULL )
			)
		)
ORDER BY
	Role.Name

The query uses a correlated subquery in the WHERE clause,. The subquery returns the Role IDs that have been used in the last 90 days. The WHERE clause specifies that only Roles whose IDs are not returned by that subquery should be returned.

All Active Roles And The Date Last Used

Another query that I developed for my client returns all active roles, and includes the date that the role was most recently used. Here's the query.

SELECT
	Role.ID,
	Role.Name,
	Role.IsInactive,
	( SELECT MAX( LoginAudit.Date ) FROM LoginAudit WHERE LoginAudit.Role = Role. ID ) AS LastUsed
FROM 
	Role
WHERE
	( Role.IsInactive = 'F' )
ORDER BY
	Role.Name

This query also uses a correlated subquery, but in this case the subquery is in the SELECT clause. So it is executed for each row returned by the query.

Wrapping Up

I hope you find these queries to be helpful. If so, then you might also be interested in the queries that I shared in this post from back in March, which can be used to identify employees that have not logged into NetSuite recently.

Also, the queries shared in this post are available via the Remote Library function of the SuiteQL Query Tool. To locate them, simply search for the keyword "roles."

If you have any questions about these queries, please feel free to contact me.