Splunk provides tools to create custom dashboards and visualizations. By visualizing NetSuite data in Splunk, stakeholders can get a clearer understanding of their operations and make data-driven decisions. Organizations maintain compliance by tracking and analyzing changes and activities within NetSuite so it makes sense to have the data available in Splunk in order to perform analysis and detection on it.
A connection from Splunk DB Connect (Splunk add-on used to query data from databases) to Netsuite can be established using a custom JDBC driver add-on. Check manual page Install database drivers for details on how to setup a new database driver.
1. Contact your Netsuite admin and ask them to provide the JDBC driver for Linux. Place the jar file in folder lib/dbxdrivers of your add-on
.
├── local
│ ├── app.conf
│ └── db_connection_types.conf
└── lib
└── dbxdrivers
└── NQjc.jar
2. Create a db_connection_types.conf file with a custom DB connection
[NetSuite]
displayName = NetSuite
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcUrlFormat = jdbc:ns://<host>:<port>;ServerDataSource=<datasource>;encrypted=1;Cypersuites=TLS_RSA_WITH_AES_CBC_SHA;CustomProperties=(AccountID=<accountid>;RoleID=<roleid>)
jdbcDriverClass = com.netsuite.jdbc.openaccess.OpenAccessDriver
3. Package and install the app on the same host where DB Connect is running
4. Browse to DBConnect - Configurations - Settings - Drivers and confirm the new "NetSuite" driver is installed (green checkmark)
5. Configure an Identity and a Connection making sure you select Connection Type Netsuite
7. In Netsuite, grant permission for the tables. A good reference for the necessary permissions can be found in the Tables & Permissions link below.
6. You're ready to start writing and scheduling your queries.
If you want to use a table column as checkpoint in order to incrementally query new data from the last checkpoint, you will need to use a rising column query. A special format needs to be use when using a rising column of date format
SELECT * from ReplaceWithYourNetSuiteTable
WHERE TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS') > ?
ORDER BY date ASC
Some useful links with details on writing NetSuite DB queries can be found in Tim Dietrich's blog posts. He's a software developer who specializes in Netsuite and the most of his posts are related to this platform. The list of tables provides a good reference of what data can be extracted and where it's stored.
- NetSuite: Working With Dates and Times in SuiteQL Queries
- SuiteQL Tips & Tricks
- NetSuite: SuiteQL Tables & Permissions Reference
You're now ready for dashboarding and building alerts and reports.