Configuring an SQL Server destination

You can use SQL Server database destinations to store historical results for analysis and integration with third-party systems. You can forward the results from saved questions and other data sources to a database table.

System requirements

  • Microsoft SQL Server 2008 or later, SQL Server Express
  • Database server must be accessible from the Tanium™ Module Server
  • User credentials to an account on the SQL Server that can insert data, create tables (optional), and run stored procedures (optional)

Set up the database

Decide whether to use an existing database or create a new database.

  1. Get the name of the database that you want to use for Connect. When you set up the destination, you can have Connect create a new table for your data or use an existing table.
  2. The user account that Connect uses for the database must have log in permissions to that database and the appropriate permissions to tables and stored procedures (optional) within that database.

    Both standard and Windows authentication are supported. Set up the following privileges: 

    • For each user, create a User Mapping for the database.
    • For each database, assign the CREATE TABLE privilege.
    • For each schema, assign the ALTER and INSERT privileges.
    • (Optional) For stored procedures, assign at least the EXECUTE privilege.
  3. (Optional) Create stored procedures.
    You can create procedures that run before the first database row is inserted or after the last row is inserted. Before you create the connection, these procedures and any associated parameters must exist in your database. The user account for Connect also must have permission to run these procedures.

ClosedSample SQL Script for creating stored procedures

Specify general connection information

  1. On the Connect home page, click Create Connection > Create.
  2. Enter a name and description for your connection.
  3. Enable the connection to run on a schedule.
    Select Enable. You can set up the schedule when you configure the rest of the connection. If the schedule is not enabled, the connection only runs when you manually run it.
  4. (Optional) Set the logging level.
    By default, the logging is set to Information. Set the log level to Trace or Debug if you are debugging the connection. To reduce the amount of logging, you can set the log level to Warning, Error, or Fatal.

Configure the connection source

The connection source determines what data you are sending to the destination. This data is usually information from Tanium, such as a saved question, question log, system status, or event. The settings vary depending on which source you choose.

Configure SQL server destination

Specify information about how to connect to your database, test the database connection, and configure which data is added to the database tables and rows.

  1. In the Destination section, choose SQL Server.
  2. Specify information for about your database, including server name, user ID, and password.
    If you are using Windows authentication, specify the user name in the following format: domain_name\login_name.
  3. To test the connection to the database, click Connect. If the connection to the database is successful, the field for database name is displayed.
  4. Choose the correct database. The field for the schema is displayed. Choose the schema. The field for the table is displayed. Enter a new table name to be created or choose an existing table name.
  5. (Optional) Configure advanced settings. Expand Advanced Settings.
    You can configure timeouts, batch size, and stored procedures.

    Stored procedure fields and parameters are available only if your database already has stored procedures configured.

Configure filters

(Optional) You can use filters to modify the data that you are getting from your connection source before it is sent to the destination.

For more information about the types of filters you can configure, see Reference: Filtering options.

Map database columns

(Optional) You can view and change the mapping of the data from your source to the columns in your database table.

If the source is a saved question, the following columns are also available for writing to the SQL Server destination:

  • Connection Run ID: The ID of the connection run.
  • Band Number: The source row number. When flattened, this number is in each row that is associated with a row number that is received from Tanium.
  • Row Number: The destination row number. This value is a counter for the rows that are output per connection run. The value is unique for each connection run.

You can change the Destination name of each column and Value Type to force the column to be a String, Numeric, or DateTime value. If you choose Numeric for the value, you can specify a default value that is used if the data cannot be coerced into a numeric value. You can specify any negative or positive number. If you choose DateTime for the value, specify the Date/Time format that you want to use for the column.

When you edit a connection with an SQL Server destination, the columns that are in the database are not displayed by default. You can edit the text fields for the columns, but you must provide the User Name and Password and click Connect in the Source and Destination section to view the columns from the database.

Schedule the connection

Connections can run at a highly configurable time interval, such as multiple times per hour, day, week, or month.

Update the schedule: 

  • Use the Generate Cron tab to build a schedule based on some common time intervals. This tab generates a Cron expression.
  • To view or edit the Cron expression directly, click the Edit Cron Expression tab.

Save and verify connection

  1. Click Create Connection > Create. When the connection gets created, your new connection displays in the list on the Connections page.
  2. To view details about when the connection is running, click the name of the connection. On the resulting connection details page, click the Runs tab.
  3. To view individual run logs, click the link in the Status column in the Runs table.

Your connection is listed in the Connections list and data is sent to the SQL server at the interval you specified. You might want to check your database table to verify that the data is being exported correctly.

Last updated: 12/4/2018 3:28 PM | Feedback