Published on October 28, 2021.
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.
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.
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.
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.
Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.
I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.
I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.
Copyright © 2025 Tim Dietrich.