Master How to Build a NetSuite User Access Audit Control Search

Last updated on March 7, 2024.

This article provides a step-by-step guide for creating a user access audit control saved search that will help NetSuite administrators and/or other users in a system audit/control capacity to easily monitor who has access to the system, when they last logged in, etc. The saved search control can be run on-demand or scheduled to facilitate periodic audits.

TL;DR

Follow the steps provided in this article to build a user audit control as illustrated above within minutes. This report will help you detect dormant users and allow you to take timely action. All you need is access to a NetSuite account, a little familiarity with NetSuite saved searches, and a few minutes to spare. It does not get much simpler!

Context

Whether it is users who have left the company or changed roles internally, individuals who needed temporary access that never got removed, or simply malicious actors, it is important to have an effective way to monitor who has access to your NetSuite account. This kind of control is not unique to NetSuite; user access control is one of the key elements of any control policy and typically falls under a NetSuite Administrator or Controller.

NetSuite offers some in-built user access control options none of which provides a concise overview of who has access, when last they logged in, how many roles they have, etc. Here’s a quick overview of NetSuite’s main offerings and their limitations.

The good news is that we can create a search that exposes the desired information in one place as you will learn in this article.

Creating the User Access Control Saved Search

We will be creating an Employee search.

Tip: One might be tempted to build a user access control search off the Login Audit Trail search type. However, that’s not such a great idea because it will not capture users who have access but have never logged into the system. An Employee search does not have this limitation.

1. Navigate to Reports > New Search, select Employee as the record type, and click on “Create Saved Search“. There are other ways to get to this page e.g. List > Employees > Employees > Search or starting off the Employee list and clicking the “Search” link at the top right corner. If you are not in the Classic Interface, your navigation paths might be different.
   
2. Give your search a meaningful name and ID.
   
3. Under tab Criteria > Standard:
   • Login Access = true
   • Inactive = false
     • Exclude inactive users that may have Login Access = true as they cannot log in.

We will come back later to add some criteria under tab Criteria > Summary but it will be easier to understand those after we have gone through the Results tab.

Tip: NetSuite does not automatically uncheck the “Give Access” option when you inactivate a user. This can skew your picture of who has access. However, it is comforting to note that inactive users cannot log in and do not contribute to your license count even if the “Give Access” option remains checked.

Search Results

The search result columns are illustrated above. You will notice that this is a summary search and there is a good reason for that: To capture the last time a user logged in, we need to join to the Login Audit Trail table. However, if a user has logged in more than once, there will be multiple entries in the Login Audit table. Thus, to get aggregate information per user, we need to group. Hence, a summary search is required.

1. Employee Name
   • Field: Name
   • Summary Type: Maximum
   • When Ordered By Field: Login Audit Trail : Date
     • This is optional but useful in case multiple users share the same email address. In that case, we want to see the name of the user who most recently logged in.
       
2. Email Address
   • Field: Email
   • Summary Type: Group
     • We want only one row per email address. If multiple users have the same email address, we will expose that via a count column that we describe later.
       
3. Has Admin Role? In my use case, I was interested in highlighting those users who have the Administrator role. From a control perspective, this elevated access is dangerous and should be limited to a small subset of users. Thus, exposing the number of full admins is useful.
   • Field: Formula (Text)
   • Summary Type: Maximum
   • Formula: DECODE({role}, 'Administrator', 'Yes', 'No')
     • DECODE is a compact alternative to a CASE statement. If the role is “Administrator”, we return “Yes”. In case the user has a combination of admin and non-admin roles, the “Maximum” summary type will ensure that we get a “Yes” because “Y” is lexically greater than “N”.
       
4. Most Recent Successful Login
   • Field: Formula (Date/Time)
   • Summary Type: Maximum
   • Formula: DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},'')
     • We return the login timestamp only if the login was successful. The “Maximum” summary logic knows to give priority to rows with a value over those without a value (i.e. unsuccessful login attempts).
       
5. Days Since Last Login
   • Repeat the configuration for “Most Recent Successful Login” above.
   • Function: Age in Days
     
6. Number of Roles: A high number of roles might be an indication of possible issues and trigger a review.
   • Field: Formula (Numeric)
   • Summary Type: Maximum
     • Note: Other aggregate functions like Minimum or Average would work here because the formula below is producing a count.
   • Formula: COUNT(distinct {role})
     • The distinct keyword is important here to avoid counting the same role more than once. That’s a neat feature!
       
7. Number of Users Sharing an Email Address: Again, this should not happen under normal conditions and will flag anomalies like duplicate employee records. Gladly, NetSuite is smart enough to allocate only one license in such a situation because licenses are tied to email addresses, not employee records.
   • Field: Internal ID
   • Summary Type: Count

That’s it! Of course, you can extend the search with additional columns as you deem fit. I hope you’ve learned some saved search tricks from this section. DECODE, When Ordered By Field, COUNT(DISTINCT {field}) are pretty neat.

Limiting Search Results

Our saved search so far exposes all users who have access to the system. However, for control purposes, we often care about outliers e.g. users who have never logged in or have not logged in for a while. We can limit the search to only show those results by adding more criteria as illustrated below:

In our example, we want to limit the results to users who have never logged in or users who have not logged in within the past 90 days.

Navigate to the subtab Criteria > Summary and do the following:

1. Check the “Use Expressions” option as we need an “OR” condition. Note: There is a UI glitch on this page. After saving the criteria, if you navigate back to this page, the option will be unchecked. However, in the background, it still retains the criteria.
   
2. Users Who Have Never Logged In
   • Summary Type: Maximum
   • Field: Formula (Date)
   • Description:
     • Click the “Set Description” icon next to this field to configure the remaining options
     • Formula: DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},'')
     • Formula (DATE): empty
       
     • Note: Be sure to press “OK” to commit the line otherwise the description will not persist! This is another glitch that often gets me whenever I am updating summary criteria or highlighting.
       
   • And/Or: OR
     
3. Users Who Have Not Logged In For the Past X Days
   • Summary Type: Maximum
   • Field: Formula (Date)
   • Description:
     • Formula: DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},'')
     • Formula (Date): On or before 90 days ago
       

Conditional Highlighting (Optional)

It is often helpful in control search design to highlight conditions that require attention. This facilitates the principle of management by exceptions: I want to focus on the rows that might indicate an anomaly. In my case, I care about three things: (1) Users who have not logged in for over 90 days; (2) users who are not part of the target org i.e. external users, and (3) external users with Admin access.

Depending on your use case, your rules might be different. The important thing to remember is that since we created a summary search, we will need to apply our highlight at the summary level under subtab Highlighting > Highlight if... (Summary). For inspiration, here are the steps to create the highlight for stale users.

Highlighting Stale Users

• Condition: The same two criteria we used in the summary criteria in the previous section. The only difference is that we need to select a “Summary Type”.
  
• Set the Image, Text Color, Description, etc. as you deem fit

Scheduled Email Delivery (Optional)

As a final optional step, consider setting up period email alerts via subtab Email. Remember to configure a frequency that is aligned with your search criteria. For example, if you are limiting your results to users who have not logged in during the last quarter, it probably makes sense to configure the email to be sent out quarterly as well.

Creating Your Own Search

Now that you understand how the search works, it’s time to create yours! To facilitate that process, I’ve created sample code that you can run in the browser console to get you there faster per the approach I describe in this article.

Tip: The sample code is ready to go, so you can skip ahead to Step 3.4 of the referenced article. Once the search is created, navigate to the criteria and add the missing elements: Age in Days function for the Days Since Last Login column, When Ordered By for the Name column, etc. You will also need to add any conditional highlighting you need or email configuration.

Before running the code, update the search title and ID (lines 67-68). Also, if you do not want to limit your results to users who have not logged in recently, remove the summary criteria (lines 10 – 15).

require(['N/search'], function (search) {
   try {

      var searchObj = search.create({
         type: "employee",
         filters: [
            ["access", "is", "T"]
            ,"AND"
            ,["isinactive", "is", "F"]
            ,"AND"
            ,[
               ["max(formuladate: DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},''))", "isempty", ""] 
			   ,"OR" 
			   ,["max(formuladate: DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},''))", "onorafter", "daysago90"]
            ]
         ],
         columns: [
            search.createColumn({
               name: "entityid",
               summary: "MAX",
               sort: search.Sort.ASC,
			   label: "Name"
            }),
            search.createColumn({
               name: "email",
               summary: "GROUP",
               sort: search.Sort.ASC,
			   label: "Email"
            }),
            search.createColumn({
               name: "formulatext",
               summary: "MAX",
               formula: "DECODE({role}, 'Administrator', 'Yes', 'No')",
			   label: "Has Admin Role?"
            }),
            search.createColumn({
               name: "formuladatetime",
               summary: "MAX",
               formula: "DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},'')",
			   label: "Most Recent Successful Login"
            }),
            search.createColumn({
               name: "formuladatetime",
               summary: "MAX",
               formula: "DECODE({loginaudittrail.status},'Success',{loginaudittrail.date},'')",
			   label: "Days Since Last Login"
            }),
            search.createColumn({
               name: "formulanumeric",
               summary: "MAX",
               formula: "COUNT(distinct {role})",
			   label: "# Roles"
            }),
            search.createColumn({
               name: "internalid",
               summary: "COUNT",
			   label: "# Users With This Email Address"
            }),
            search.createColumn({
               name: "giveaccess",
               summary: "GROUP",
			   label: "Login Access"
            })
         ]
      });

      searchObj.id = "customsearch_xxx_ctrl_user_access_audit";
      searchObj.title = "XXX User Access Audit";
      var searchId = searchObj.save();

      console.log('Search recreated successfully. ID: ' + searchId);

   } catch (e) {
      console.error(e.message);
   }
})

Control Considerations

I have written previously about a two-search approach to effectively monitor dormant NetSuite users and roles. While that approach remains relevant and more robust, this article provides a simplified approach that focuses only on dormant users.

This article is sufficient for use cases where we do not care about the specific roles that a user has access to or when they last logged into each role; we focus solely on the fact that the user has access to the system and seek to assess whether that is justified.

Use Case

Given my remark above about control (in)completeness, the obvious question is: In what use cases would this simplified approach be warranted/sufficient? Here’s one: In my current role at Prolecto, I am responsible not just for client operations but also for some internal infrastructure. Prolecto has a couple of NetSuite “test drive” accounts that NetSuite offers to partners for application development, etc. Test drive accounts are effectively playgrounds. Thus, it is typical that our analysts are granted Administrator access so that they are not inhibited by permission restrictions.

While the best practice for production accounts is to limit the number of administrators to the bare minimum, that consideration is not strong for test drive accounts. Thus, I do not quite care to know what roles users have; I simply need to know who has access, when they last logged in, and if they are external (e.g. candidates who were temporarily granted access during their assessment). On the other hand, some test drive accounts are sensitive in that they are used for distributing our Prolecto Labs. Naturally, those accounts require more care as mistakes made there might have a direct client impact and break trust. Again, in such accounts, I need to be able to quickly assess who is in there and whether or not they should be. Finally, test drive accounts have limited seats (often between 10 and 20 full-user licenses). As our number of (top-notch) consultants continues to grow, we hit the user license limit from time to time. In such cases, the simple solution is to withdraw access of the stalest user. Having the last login timestamp per user makes that assessment must easier.

Conclusion

I find the area of audits and controls generally intriguing. At my previous company (publicly traded at the time), I actually enjoyed audit season as weird as that sounds. It’s always great to be one step ahead of your auditors. It builds trust with them quickly and saves you the headache and fuss that will naturally arise on the contrary.

The controls presented in this and the related article are simple yet important ones. Definitely, ones that you can show off to your external or internal auditors with pride.

Are you in need of assistance with developing a NetSuite control strategy or need help assessing the completeness and effectiveness of your controls? User access controls are just a small part of the story. Perhaps it’s time to reach out for a conversation.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.

Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!

NetSuite Insights is proud to partner with Prolecto Resources Inc. – the unrivaled #1 NetSuite Systems Integrator and thought leader in the space! Learn more about how Prolecto can supercharge your NetSuite experience and deliver the best return on your NetSuite investment.

Other Articles You Might Find Interesting

1. Build a Real-Time NetSuite License Utilization Dashboard in 10 Minutes!
2. Understanding the “Run Unrestricted” Saved Search Mode: Part 1
3. How To Audit Changes to Enabled Features in NetSuite
4. Learn to Detect and Block this Journal Entry Backdoor Before Your Auditors Find It!
5. NetSuite Role Permissions Tracker
6. Learn How To Effectively Monitor Dormant NetSuite Users and Roles
7. Learn to Properly Solve the Journal Entry Backdoor/SOD Concern
8. Saved Search Deployment Failed? Try This Before Resorting to Manual Deployment!