Published on August 12, 2020.

Here's a related post that you might be interested in. It shows how you can use SuiteQL to analyze login attempts, evaluate role usage, and more.

One of the many interesting tables that NetSuite provides access to via SuiteQL is the SystemNote table. This table is used to maintain a log of changes made to records. In this blog post, I'll explain what's stored in the table, and some of the interesting ways that some of my clients are using it.

Exploring the SystemNote Table

Let's start by querying the table for a few records.

SELECT
	*
FROM
	SystemNote
WHERE
	ROWNUM <= 5

Here's the result.

As you can see, the records represent changes that have been made to records in the instance. Let's take a look at the columns that were returned.

The Role is the role that the user was in, or that a script or other process was using, when the change was made.

The Date is the date on which the change was made.

The NewValue is the value that a column was changed to. Skipping head, you'll see that there's also the OldValue, which is the value that was stored in the column prior to the change.

The Record value is the name of the record, and the RecordID is the internal ID of the record.

The Field is, of course, the name of the field that was changed. Notice that one of the records represents a change to a custom field. It's the one with the Field value that has the "CUSTENTITY_" prefix. So you can use the SystemNote table to get the changes to both standard and custom fields.

The RecordTypeID is a numeric value representing the type of record that the column is associated with. In this case, the value is -9, which indicates that the record is an Entity. Here's a table that lists other record types and their IDs.

The Name field is actually the ID of the Employee that made the change.

The Context is the context that was used to make the change. In this example, all of the changes were made in the "UIF" context, which indicates that the NetSuite user interface was being used. Some of the other Context values that I've seen are RST (for RESTlets), SCH (for scheduled scripts), SLT (for Suitelets), and UES (for User Event Scripts).

I'm still not sure what the Type column represents. If you figure out what it is, please let me know.

Update: I want to thank Tyson Freeze for letting me know that the "Type" field indicates the type of action what was logged. For example, create, set, change, and unset.

Improving the Query

In a previous post, I discussed SuiteQL's Built-In Functions and the DF function in particular. Let's improve the query by using the DF function and some column aliases.

SELECT
	Date,
	RecordTypeID,
	BUILTIN.DF( RecordTypeID ) AS RecordType,
	RecordID,
	Field,
	OldValue,
	NewValue,
	Name As EmployeeID,
	BUILTIN.DF( Name ) AS EmployeeName,
	Role,
	BUILTIN.DF( Role ) AS RoleName,
	Context,
	BUILTIN.DF( Context ) AS ContextName
FROM
	SystemNote
WHERE
	( RecordTypeID = -9 )
	AND ( RecordID = 3118 )
ORDER BY
	Date ASC

I'm using the DF function to map the RecordTypeID, Name, Role, and Context to their names. Also notice that I'm now requesting a specific record by filtering on the RecordTypeID and RecordID.

Here's the response.

I think this makes the data a little easier to work with. You can see who made a change, what column they changed, and the values before and after the change was made.

Auditing With System Notes

There are many different ways that you can use the data in the SystemNote table, and one of the more obvious ways is to use it for auditing.

Suppose that you want to see all changes that a specific user made on a given day or over a date range. The query might look like this.

SELECT
	Date,
	RecordTypeID,
	BUILTIN.DF( RecordTypeID ) AS RecordType,
	RecordID,
	Field,
	OldValue,
	NewValue,
	Name As EmployeeID,
	BUILTIN.DF( Name ) AS EmployeeName,
	Role,
	BUILTIN.DF( Role ) AS RoleName,
	Context,
	BUILTIN.DF( Context ) AS ContextName
FROM
	SystemNote
WHERE
	( Name = 4 )
	AND ( Date BETWEEN To_Date ( '2020-07-01', 'YYYY-MM-DD' ) AND To_Date ( '2020-07-31', 'YYYY-MM-DD' ) )
ORDER BY
	Date DESC

You can also filter on the column (Field) that was changed, all changes made when a specific role and/or context was being used, and so on.

Analyzing Sales Order Statuses

One of my clients is using SuiteQL and the SystemNote table to analyze status changes on their sales orders. Their goal is to determine how long it takes for a sales order to be processed, including the length of time it takes for a transaction to move from one status to the next. Here's a sample query that shows the progression of a specific order.

SELECT
	Date,
	Record AS SONumber,
	RecordID,
	OldValue AS BeginningStatus,
	NewValue AS EndingStatus
FROM
	SystemNote
WHERE
	RecordTypeID = -30
	AND RecordID = 1123
	AND Field = 'TRANDOC.KSTATUS'
ORDER BY
	Date ASC

Notice that I'm filtering for RecordTypeIDs of -30 (which are transactions), and I'm specifying that I want to see changes to the "TRANDOC.KSTATUS" field. (That's the transaction status field.)

Here's the response.

My client is also using the SystemNote table to analyze the number of days that have elapsed between the date that an order was entered into NetSuite and the date that it was billed. Here's a query showing how we're getting that.

SELECT
	Transaction.TranDate AS DateOrdered,
	SystemNote.Date AS DateBilled,
	EXTRACT( DAY FROM ( SystemNote.Date - Transaction.TranDate ) ) AS DaysOpen
FROM
	Transaction
	LEFT OUTER JOIN SystemNote ON
		( SystemNote.RecordID = Transaction.ID )
		AND ( SystemNote.RecordTypeID = -30 )
		AND ( SystemNote.Field = 'TRANDOC.KSTATUS' )
		AND ( SystemNote.NewValue = 'Billed' )
WHERE
	Transaction.ID = 1123

Notice that I'm joining the sales order's Transaction record to the related SystemNote record that represents when the order was billed. This provides access to the TranDate column, which is the date that the order was entered.

We're using similar queries to analyze things such as the average number of days that it takes orders to be approved, how long it's taking for orders to move from one status to another (from Pending Approval to Pending Fulfillment and so on), and so on.

Wrapping Up

In this post, I've described the SystemNote table and some of the interesting ways that I've been putting it to use for my clients. As always, I hope you find this information to be helpful.