Published on December 3, 2020.

In a couple of earlier posts, I've mentioned a project that I worked on for a NetSuite client that was preparing for, and eventually went through, an audit. (See "Using SuiteQL and System Notes for Auditing," "Analyze Logins with SuiteSQL," and "Access Employees, Supervisors, Roles, and Permissions Using SuiteQL.")

One of the many interesting things that they requested was a list of all outbound HTTP requests that were being made from their account. At first, I didn't think I'd be able to help them. But after doing some research, I came across a table named OutboundRequest. Sure enough, it had nearly all of the data that they were requesting.

Here's what the first version of the SuiteQL query looked like.

	
SELECT
	TO_CHAR( OutboundRequest.Time, 'YYYY-MM-DD hh:mi:ss') AS RequestTime,
	OutboundRequest.Key,
	OutboundRequest.RequestID,
	OutboundRequest.Elapsed,
	OutboundRequest.Host,
	OutboundRequest.Port,
	OutboundRequest.URL,
	OutboundRequest.StatusCode,
	OutboundRequest.Error,
	OutboundRequest.RequestContentType,	
	OutboundRequest.RequestContentLength,
	OutboundRequest.ResponseContentType,
	OutboundRequest.ResponseContentLength,
	OutboundRequest.ScriptDeploymentURL,
	OutboundRequest.ScriptID
FROM
	OutboundRequest
WHERE
	OutboundRequest.Time BETWEEN TO_DATE( '2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-12-31', 'YYYY-MM-DD' )
ORDER BY
	OutboundRequest.Time DESC

The query returns several values, and most of them are straightforward. The Key value is a sequential number that was assigned to the outbound request, and the RequestID is a UUID that was assigned to it. The Elapsed value is the request's elapsed time in milliseconds. The StatusCode is the HTTP status code that was returned by the host. ( For a good reference regarding HTTP status codes, check out: https://httpstatuses.com )

Notice that the query includes a ScriptID column. In cases where a request originated from a script, ScriptID will be a numeric value that maps to a record in the Script table. So my next step was to add a few joins to the query to get information about the script. Here's the updated query.

		
SELECT
	TO_CHAR( OutboundRequest.Time, 'YYYY-MM-DD hh:mi:ss') AS RequestTime,
	OutboundRequest.Key,
	OutboundRequest.RequestID,
	OutboundRequest.Elapsed,
	OutboundRequest.Host,
	OutboundRequest.Port,
	OutboundRequest.URL,
	OutboundRequest.StatusCode,
	OutboundRequest.Error,
	OutboundRequest.RequestContentType,	
	OutboundRequest.RequestContentLength,
	OutboundRequest.ResponseContentType,
	OutboundRequest.ResponseContentLength,
	OutboundRequest.ScriptDeploymentURL,
	OutboundRequest.ScriptID,
	Script.Name AS ScriptName,
	Script.ScriptType,
	BUILTIN.DF( Script.Owner ) AS ScriptOwner,	
	MediaItemFolder.Name AS FolderName,	
	File.Name AS FileName,
	File.CreatedDate AS FileCreatedDate,
	File.LastModifiedDate AS FileLastModifiedDate
FROM
	OutboundRequest
	LEFT OUTER JOIN Script ON
		( Script.ScriptID = OutboundRequest.ScriptID )
	LEFT OUTER JOIN File ON
		( File.ID = Script.ScriptFile )
	LEFT OUTER JOIN MediaItemFolder ON
		( MediaItemFolder.ID = File.Folder )
WHERE
	OutboundRequest.Time BETWEEN TO_DATE( '2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-12-31', 'YYYY-MM-DD' )
ORDER BY
	OutboundRequest.Time DESC

I've joined the OutboundRequest table, to the Script table, and from there to the File table, and then to the MediaItemFolder table. The File table is used to store information about files in the File Cabinet. The MediaItemFolder stores information about the folders in the File Cabinet. ( I used those tables in the RESTlet that I shared in "Use SuiteScript and SuiteQL to Work With the File Cabinet." )

With those joins in place, I was able to provide additional data about the script, including the script name, the name of the owner, the SuiteScript file name, the script type, the name of the folder that the SuiteScript file is stored in, as well as the date the file was created and last modified.

I've mentioned before that this project was a lot of fun to work on. It required the use of several tables that I hadn't used before, and OutboundRequest was one of them.

If you need help with SuiteQL, please feel free to contact me. I'm booked up through the end of 2020, but will be providing one-hour support calls starting in January 2021.