Login
    Contents x
    SQL - Send Assets to Table
    • 28 Jan 2024
    • 4 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    SQL - Send Assets to Table

    • Updated on 28 Jan 2024
    • 4 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article Summary

    SQL - Send Assets to Table (Send to SQL Table) inserts the entities retrieved from the saved query supplied as a trigger (or entities selected in the asset table) to the MSSQL table supplied.
    When used with a saved query as a trigger, only the fields configured in the saved query are inserted into the table supplied.

    See Creating Enforcement Sets to learn more about adding Enforcement Actions to Enforcement Sets.

    General Settings

    • Enforcement Set name (required) - The name of the Enforcement Set. A default value is added by Axonius. You can change the name according to your needs.
    • Add description - Click to add a description of the Enforcement Set. It is recommended to describe what the Enforcement Set does.
    • Run action on assets matching following query (required) - Select an asset category and a query. The Enforcement Action will be run on the assets that match the query parameters.
    • Action name (required) - The name of the Main action. A default value is added by Axonius. You can change the name according to your needs.
    • Configure Dynamic Values - Toggle on to enter a Dynamic Value statement. See Creating Enforcement Action Dynamic Value Statements to learn more about Dynamic Value statement syntax.

    Required Fields

    Click to view Required Fields
    These fields must be configured to run the Enforcement Set.

    1. DB Type (required) - Select the type of database, either Oracle, MSSQL PostgreSQL or MySQL.

    2. SQL Server host (required) - The hostname / domain of the SQL server.

    3. SQL Server port (required) - The port of the SQL server.

    4. User name (required) - The credentials for a user account that has the required permissions to fetch assets.

      NOTE
      • The best practice is to create a dedicated SQL local user for Axonius usage. For details on creating an Axonius user for Microsoft SQL Server, see Creating a Local Read-Only User for Microsoft SQL Server.
      • If you are using a domain user, specify the domain and the user name in the following format: domain\username.

    5. Password (required) - The user's password. The password must not include ";".

    6. SQL Server database name (required) - The database to connect to.

    7. SQL Server table name (required) - The name of the table to write query information to. Axonius runs a 'INSERT INTO [[specified value]] VALUES ([[query values]])' statement.

    8. Chunk size -When working with servers that are limited to certain data size in a request, set the chunk size to which the message is broken up to (default 50).

    Additional Fields

    Click to view Additional Fields

    1. MySQL Character Set (optional, default: empty) - Specify a custom character set (encoding) for connections to MySQL databases. Example values: 'utf8', 'latin1'. When no character set is specified, 'utf8' is used by default.
      Note: This option is only valid when MySQL is selected from the Database Type dropdown.

    2. Truncate table data before update (required, default: False) - Select whether to truncate the table data before update.
      * If enabled, the table data will be truncated and the table will contain only the newly added records.
      * If disabled, the table data will not be truncated and new records will be added to the existing table.

    3. Create table if not exist (required, default: True) - Select whether to create a new SQL table if the supplied table name does not exist.

      • If enabled, a new SQL table will be created if the supplied table name is not found.
      • If disabled, the action will fail, as the supplied table name is not found.

    4. Re-create table each time (required, default: True) - Select to delete the table on the server side and create a new one every time the action is run

    5. Map Axonius fields to SQL Server fields (optional, default: empty) - Use the Field Mapping Wizard to map Axonius fields to SQL Server table columns. In this way you can transfer data found in Axonius into SQL Server. The wizard shows you which fields exist on the Axonius system, allowing you to map them easily.

      NOTE

      For more details about the supported syntax for Axonius fields, see Axonius to External Field Mapping.

    6. Split by field values - Use this option to create a new row for each item in the field. Enter a key (string) with data as stored in the database in the format of a list of dictionaries (str: str) or strings. If it's a list of strings only one column will be added for each In case of dictionary it will add a column for each key“

    • Gateway Name - Select the gateway through which to connect to perform the action.

    For more details about other Enforcement Actions available, see Action Library.


    Was this article helpful?
    What's Next
    Change password!
    Changing your password will log you out immediately. Use the new password to log back in.
    Change profile
    Success!
    First name must have atleast 2 characters. Numbers and special characters are not allowed.
    Last name must have atleast 1 characters. Numbers and special characters are not allowed.
    Enter a valid email
    Enter a valid password
    Your profile has been successfully updated.
    Logout