NetSuite is the ERP environment that accounting firms underestimate. It's cloud-based, it has a proper REST API, and Oracle has invested in making data access straightforward compared to on-premise systems like SAP. The extraction problem is not authorization or connectivity — it's knowing which objects and fields correspond to what auditors need, and which API approach gets you the full population rather than a filtered view.
SuiteQL is NetSuite's SQL dialect for the SuiteAnalytics Connect API. It exposes the underlying NetSuite data model directly, without the filter constraints that the standard Saved Search API imposes. For audit purposes, this matters because Saved Searches can be configured by the client to exclude records, and you won't know what's excluded unless you built the search yourself. SuiteQL on the correct tables gives you the unfiltered population.
The NetSuite Data Model for Journal Entries
NetSuite structures financial transactions differently from SAP or Oracle EBS. The primary table for journal entry data is the Transaction table (also referred to as the TRANSACTION object in SuiteQL). Manual journal entries in NetSuite have a transaction type of Journal Entry, referenced as 'Journal' in the type field. Each journal entry has a header record and one or more line items in the TransactionLine table.
The key fields in the Transaction table for audit purposes are: id (the internal transaction ID, unique within the NetSuite account), trandate (the transaction date), postingperiod (links to the fiscal period), approvalstatus (indicates whether the entry was approved), createdby (the user ID of the person who created the entry), and lastmodifiedby (the user ID of the person who last modified it). For multi-subsidiary environments, the subsidiary field identifies which entity the entry belongs to.
The TransactionLine table contains the individual debit and credit lines. Key fields: transaction (foreign key to Transaction.id), linesequencenumber (identifies the position of the line within the entry), account (links to the Account table), debit and credit (the amount fields — one will be null, the other populated), entity (links to a customer, vendor, or employee if the line is associated with a sub-ledger), and memo (the text description for the line).
The Account table gives you the account name and account type. Joining Transaction to TransactionLine to Account gives you the complete journal entry population with account-level detail.
The Core SuiteQL Query
The query structure to pull the complete journal entry population for a given period:
SELECT
t.id AS transaction_id,
t.trandate AS transaction_date,
t.postingperiod AS period_id,
t.approvalstatus AS approval_status,
t.createdby AS created_by_user_id,
t.lastmodifiedby AS last_modified_by_user_id,
t.memo AS header_memo,
tl.linesequencenumber AS line_number,
tl.account AS account_id,
a.acctnumber AS account_number,
a.acctname AS account_name,
a.accttype AS account_type,
tl.debit AS debit_amount,
tl.credit AS credit_amount,
tl.memo AS line_memo
FROM
Transaction t
JOIN TransactionLine tl ON t.id = tl.transaction
JOIN Account a ON tl.account = a.id
WHERE
t.type = 'Journal'
AND t.posting = 'T'
AND t.trandate BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY
t.trandate, t.id, tl.linesequencenumberThe t.posting = 'T' filter is critical. NetSuite distinguishes between posting and non-posting transactions. Non-posting journal entries (used for statistical or memo purposes) should not be included in a substantive journal entry test because they don't affect account balances. This filter ensures you're testing only the entries that hit the general ledger.
For multi-subsidiary environments, add AND t.subsidiary = [subsidiary_id] to restrict to a specific entity, or remove the filter to pull the full multi-entity population. If pulling multi-entity data, include t.subsidiary in the SELECT list so you can segment by entity in your analysis.
Getting the Posting Period to Transaction Date Mapping
One NetSuite-specific consideration: entries can be dated in one calendar period but posted to a different accounting period, particularly during period-end close activities when the prior period is still open. To identify true period-end entries by accounting period rather than calendar date, join to the AccountingPeriod table:
SELECT
t.id,
t.trandate,
ap.startdate AS period_start,
ap.enddate AS period_end,
ap.islocked AS period_locked,
t.createdby
FROM
Transaction t
JOIN AccountingPeriod ap ON t.postingperiod = ap.id
WHERE
t.type = 'Journal'
AND t.posting = 'T'
AND ap.startdate >= '2024-01-01'
AND ap.enddate <= '2024-12-31'The ap.islocked field identifies whether the accounting period was closed (locked) at the time of the query. For period-end testing, filter to entries where t.trandate is within the last three business days of ap.enddate — this gives you the period-end sub-population without relying on calendar month boundaries that may not align with the client's fiscal periods.
Connecting to SuiteQL: API Authentication
SuiteQL is accessed via the NetSuite REST API with OAuth 1.0 authentication. The connection requires five parameters from the client's NetSuite administrator: the account ID (a number that appears in the NetSuite URL), a consumer key and consumer secret (from an Integration record in NetSuite Setup), and a token ID and token secret (from an Access Token associated with a user in that account).
The endpoint for SuiteQL queries is: https://[account-id].suitetalk.api.netsuite.com/services/rest/query/v1/suiteql
The request is a POST with the query in the body as {"q": "[your SuiteQL query]"}. The response is paginated at 1,000 records per request by default, with a hasMore flag and an offset parameter to retrieve subsequent pages. For a 50,000-entry population, you'll need 50 sequential requests. AuditPulsar's NetSuite connector handles pagination automatically.
The user associated with the access token needs the following NetSuite roles to execute the queries: View permission on Transactions, View permission on Accounts, and the SuiteQL permission under Setup permissions. The last one is often missing in access tokens created for non-developer users — if the query returns a 403 Forbidden, that's the first place to check.
Exporting Without the API: Saved Search CSV Export
If the client's IT team can't configure an access token in time, a manual export via a NetSuite Saved Search is the fallback. Saved Searches in NetSuite can target the Transaction record type with filters for Journal type entries, date range, and posting status. The search can be configured to return the same fields as the SuiteQL query above.
The limitation: NetSuite Saved Search exports are capped at 10,000 rows in a single CSV. For larger populations, you'll need to export in date range chunks and combine. Also, Saved Search results can exclude records based on the running user's permissions — if the client's NetSuite administrator exports the search rather than a user with full GL access, you may not be getting the complete population. Verify the total count against the NetSuite financial report for the same period.
For clients using NetSuite OneWorld (the multi-subsidiary version), Saved Searches by default show only the records the user's role is restricted to. An audit user ID needs access to all subsidiaries, or the search must be run once per subsidiary. This is an easy oversight that produces a population that looks complete but is missing several entities' data entirely.
Validation Steps Before Analysis
Before running anomaly detection on a NetSuite extraction, three validation steps are worth performing:
First, verify the transaction count against the NetSuite general ledger report for the same period. Generate a GL detail report in NetSuite filtered to journal entries only, for the same date range. Compare the row count to your extraction. A count mismatch indicates that either the query is missing a filter condition or the NetSuite GL report includes non-journal transactions that you're not accounting for in the comparison.
Second, verify that debits equal credits across the population. A basic integrity check: sum all debit_amount values and sum all credit_amount values. They should be equal. A material imbalance suggests either a data extraction error or missing line items for some transactions — possibly because the query hit a pagination error and didn't retrieve all records.
Third, check for null account types. Any TransactionLine row where the Account table join fails to return an account type indicates either a deleted account or a data integrity issue in the NetSuite instance. These records should be flagged separately for follow-up rather than included in the anomaly scoring, because the scoring models expect account type as an input.
How AuditPulsar Handles the NetSuite Connection
AuditPulsar's NetSuite connector uses the SuiteQL API with the connection parameters described above. During engagement setup, the connector validates the credentials, executes a test query on a single transaction, and confirms that all required fields are accessible before beginning the full extraction. The complete population pull runs in parallel requests with automatic retry on pagination errors, typically completing in 4 to 8 minutes for engagements up to 100,000 journal entries.
The platform maps NetSuite's field names to its internal canonical format automatically. The field mapping is viewable in the platform before extraction begins, allowing the auditor to verify that the correct fields are being used and adjust any custom field mappings for clients that have configured non-standard NetSuite implementations.
For clients who can't provide API credentials, the import template in AuditPulsar is pre-mapped to the column names produced by a NetSuite Saved Search export, making the manual CSV workflow as straightforward as the API connection for smaller populations.