Published on November 19, 2020.

I recently had a NetSuite client ask if it is possible to access user login information via SuiteQL. They're in the process of auditing their NetSuite account, and needed a way to run ad hoc queries against the login data.

NetSuite makes that data available via the LoginAudit table. Let's take a look at the table and a few queries that you can use to work with it.

The LoginAudit Table

Let's start with a simple query that shows the columns that are available in the LoginAudit table.

SELECT
	*
FROM
	LoginAudit
WHERE
	( LoginAudit.Date BETWEEN 
		TO_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) 
		AND TO_DATE( '2020-11-07 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) 
	)
ORDER BY
	LoginAudit.Date DESC

Notice that I'm requesting login attempts for a specific date/time range. I'm using the TO_DATE function to easily specify the date/time values.

The query returns the following columns:
• Date: The date and time that the user logged in, or attempted to login.
• Detail: Any details about the login attempt. For example, "RoleSwitchLogout," which indicates that the user logged in by switching from another role.
• EmailAddress: The email address that was used.
• IPAddress: The IP address that was in use.
• oAuthAccessTokenName: If the user logged in via oAuth, then this is the name of the token that they used.
• oAuthAppName: If the user logged in via oAuth, then this is the name of the integration that was used.
• RequestURI: The URL that was used.
• Role: The role that the user logged in with.
• SECChallenge: If the user was presented with security questions (challenges), then this indicates if they provided a correct answer.
• Status: Indicates if the login attempt was successful.
• User: The ID of the user (entity).
• UserAgent: The type and version of the browser or application that was in use.

The Login TimeStamp Value

By default, querying the LoginAudit table will return the Date value in a date format, without the time value. Let's update the query so that the Date is returned in a timestamp format. I'll use the TO_CHAR function to do that.

SELECT
	TO_CHAR( LoginAudit.Date, 'YYYY-MM-DD hh:mi:ss') AS LoginDateTime,
	LoginAudit.Status,
	LoginAudit.User,
	LoginAudit.EmailAddress,
	LoginAudit.Role,
	LoginAudit.UserAgent,
	LoginAudit.IPAddress,
	LoginAudit.oAuthAppName,
	LoginAudit.oAuthAccessTokenName
FROM
	LoginAudit
WHERE
	( LoginAudit.Date BETWEEN 
		TO_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) 
		AND TO_DATE( '2020-11-07 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) 
	)
ORDER BY
	LoginAudit.Date DESC

The User and Role Names

The query returns the numeric IDs of the user (entity) and role that were involved with each login attempt. To get the names of the users and/or roles, we could join to the Entity and Role tables, and that is usually what I'd do. However, if you only need the user and role names, then you can use the BUILTIN.DF function and avoid those additional joins.

SELECT
	TO_CHAR( LoginAudit.Date, 'YYYY-MM-DD hh:mi:ss') AS LoginDateTime,
	LoginAudit.Status,
	LoginAudit.User,
	BUILTIN.DF( LoginAudit.User ) AS Username,
	LoginAudit.EmailAddress,
	LoginAudit.Role,	
	BUILTIN.DF( LoginAudit.Role ) AS Rolename,
	LoginAudit.UserAgent,
	LoginAudit.IPAddress,
	LoginAudit.oAuthAppName,
	LoginAudit.oAuthAccessTokenName
FROM
	LoginAudit
WHERE
	( LoginAudit.Date BETWEEN 
		TO_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) 
		AND TO_DATE( '2020-11-07 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) 
	)
ORDER BY
	LoginAudit.Date DESC

At this point, the query provides a nice result set that can be used to analyze login attempts. Let's take a look at a few more queries that you might find to be helpful.

Failed Login Attempts

To get a list of failed login attempts, simply adjust the query's WHERE clause so that only records where the Status is "Failure."

SELECT
	TO_CHAR( LoginAudit.Date, 'YYYY-MM-DD hh:mi:ss') AS LoginDateTime,
	LoginAudit.Status,
	LoginAudit.User,
	BUILTIN.DF( LoginAudit.User ) AS Username,
	LoginAudit.EmailAddress,
	LoginAudit.Role,	
	BUILTIN.DF( LoginAudit.Role ) AS Rolename,
	LoginAudit.UserAgent,
	LoginAudit.IPAddress,
	LoginAudit.oAuthAppName,
	LoginAudit.oAuthAccessTokenName
FROM
	LoginAudit
WHERE
	( LoginAudit.Date BETWEEN 
		TO_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) 
		AND TO_DATE( '2020-11-30 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) 
	)
	AND ( Status = 'Failure' )
ORDER BY
	LoginAudit.Date DESC

Most Recent Login of a User

You can use the LoginAudit table to determine when a user last logged in.

	
SELECT
	BUILTIN.DF( LoginAudit.User ) AS UserName,
	MAX( LoginAudit.Date )
FROM
	LoginAudit
GROUP BY
	BUILTIN.DF( LoginAudit.User )
ORDER BY
	UserName

Most Recent Use of a Role

Similarly, you can use the LoginAudit table to determine when a Role was last used. This might be helpful if you're reviewing custom roles and need to determine if they are still in use.

SELECT
	BUILTIN.DF( LoginAudit.Role ) AS RoleName,
	MAX( LoginAudit.Date )
FROM
	LoginAudit
GROUP BY
	BUILTIN.DF( LoginAudit.Role )
ORDER BY
	RoleName

Wrapping Up

The LoginAudit table provides us with a convenient way to analyze login attempts, and as I mentioned above, it can also be helpful if you need to determine role usage.

If you're looking for other ways that SuiteQL can be used to help with audit-related analysis, then you might want to check out this post: Using SuiteQL and System Notes for Auditing In it, I show how you can use SuiteQL to query the System Notes table to perform auditing, analysis of transactions, and more.

If you have any questions about the queries that I've shared in this post, or SuiteQL in general, please feel free to contact me.