Snowflake

Use the Snowflake integration to query and update your Snowflake database.

Configure Snowflake on Cortex XSOAR

Several parameters are explained in greater detail in the Detailed Instructions section.

1. Navigate to Settings > Integrations > Servers & Services.
2. Search for Snowflake.
3. Click Add instance to create and configure a new integration instance.
   • Name: a textual name for the integration instance.
   • Username
   • Account - See Detailed Description section.
   • Region (only if you are not US West)
   • Authenticator - See Detailed Description section.
   • Default warehouse to use
   • Default database to use
   • Default schema to use
   • Default role to use
   • Use system proxy settings
   • Trust server certificate (insecure)
   • Client ID
   • Client Secret
   • OAuth Token URL
   • OAuth Scope
   • Fetch incidents
   • Fetch query to retrieve new incidents. This field is mandatory when ‘Fetches incidents’ is set to true.
   • First fetch timestamp ( , e.g., 12 hours, 7 days)
   • The name of the field/column that contains the datetime object or timestamp for the data being fetched (case sensitive). This field is mandatory when ‘Fetches incidents’ is set to true.
   • The name of the field/column in the fetched data from which the name for the Cortex XSOAR incident will be assigned (case sensitive)
   • The maximum number of rows to be returned by a fetch
   • Incident type
4. Click Test to validate the URLs, token, and connection.

Detailed Instructions

Additional information for configuring the integration instance.

Integration Parameters

• Account
  The name of the Snowflake account to connect to without the domain name: snowflakecomputing.com. For example, mycompany.snowflakecomputing.com, enter “mycompany”. For more information, see the Snowflake Computing documentation.

• Authenticator
  (Optional) Use this parameter to log in to your Snowflake account using Okta. For the ‘Username’ parameter, enter your ‘<okta_login_name>’. For the ‘Password’ parameter, enter your ‘<okta_password>’. The value entered here should be ‘https://<okta_account_name>.okta.com/’ where all the values between the less than and greater than symbols are replaced with the actual information specific to your Okta account.

• Credentials
  To use Key Pair authentication, follow these instructions:

  1. Follow steps 1-4 in the instructions detailed in the Snowflake Computing documentation.
  2. Follow the instructions under the section titled Configure Cortex XSOAR Credentials at this link.
  3. Use the credentials you configured. Refer to the two images at the bottom of the section titled Configure an External Credentials Vault.

• Authentication via External OAuth
  To configure External OAuth authentication, please consult the following setup guidelines: Snowflake External OAuth Overview.

  When using External OAuth, fill in the OAuth Client ID, OAuth Client Secret, OAuth Token URL, and optionally the OAuth Scope parameters. The Username field should still be set to the Snowflake service user that the IdP token maps to. The Password field can be left empty.

  Prerequisites

  1. In the IdP (e.g., Okta):
     • Create an API Services application (no user redirect, machine-to-machine).
     • Note the Client ID and Client Secret.
     • Create / use a Custom Authorization Server.
     • Add a custom scope named session:role:<SNOWFLAKE_ROLE> (e.g., session:role:ANALYST).
  2. In Snowflake:
     • Create an External OAuth Security Integration that trusts the IdP's issuer & JWKS URL and maps the JWT sub claim to a Snowflake user.
     • Create / use a service user of TYPE = SERVICE.

Commands

You can execute these commands from the Cortex XSOAR CLI, as part of an automation, or in a playbook. After you successfully execute a command, a DBot message appears in the War Room with the command details.

1. Run a query in Snowflake: snowflake-query
2. Make a DML change in the database: snowflake-update

1. Run a query in Snowflake

Executes a SELECT query and retrieve the data.

Base Command

snowflake-query

Input

Context Output

Command Example

snowflake-query warehouse=demo_wh database=demo_db schema=public query="select * from test"

Context Example

{
    "Snowflake": {
        "Query": "select * from test",
        "Schema": "public",
        "Result": [
            {
                "TS": "2018-09-11 00:00:00.000000",
                "ID": 1,
                "NAME": "b"
            },
            {
                "TS": "2018-10-12 00:00:00.000000",
                "ID": 2,
                "NAME": "kuku"
            },
            {
                "TS": "2018-10-12 00:00:00.000000",
                "ID": 3,
                "NAME": "kiki"
            },
            {
                "TS": "2018-10-12 00:00:00.000000",
                "ID": 4,
                "NAME": "kaka"
            },
            {
                "TS": "2018-10-12 00:00:00.000000",
                "ID": 5,
                "NAME": "kuku"
            },
            {
                "TS": "2019-03-26 11:14:18.574000",
                "ID": 8,
                "NAME": "blah"
            },
            {
                "TS": "2019-03-26 11:16:16.773000",
                "ID": 8,
                "NAME": "new"
            },
            {
                "TS": "2019-03-26 11:30:42.479000",
                "ID": 9,
                "NAME": "nBw4QhFcGJ"
            },
            {
                "TS": "2019-03-14 00:00:00.000000",
                "ID": 10,
                "NAME": "UPDATing"
            },
            {
                "TS": "2019-03-19 00:00:00.000000",
                "ID": 11,
                "NAME": "TESTING IT OUT again"
            },
            {
                "TS": "2019-03-28 05:32:13.355000",
                "ID": 13,
                "NAME": "New Alert"
            },
            {
                "TS": "2019-03-28 06:09:26.153000",
                "ID": 14,
                "NAME": "SHOULD FETCH THIS NEW"
            },
            {
                "TS": "2019-03-28 08:46:50.311000",
                "ID": 15,
                "NAME": "Perth"
            },
            {
                "TS": "2019-03-28 06:19:06.271000",
                "ID": 16,
                "NAME": "Edinburgh"
            },
            {
                "TS": "2019-03-28 06:19:14.059000",
                "ID": 17,
                "NAME": "York"
            },
            {
                "TS": "2019-03-28 06:20:27.126000",
                "ID": 18,
                "NAME": "Persimmon"
            },
            {
                "TS": "2019-03-28 06:28:31.001000",
                "ID": 19,
                "NAME": "Langdon"
            },
            {
                "TS": "2019-03-28 11:53:41.416000",
                "ID": 20,
                "NAME": "London"
            }
        ],
        "Database": "demo_db"
    }
}

Human Readable Output

select * from test

2. Make a DML change in the database

Makes a DML change in the database.

Base Command

snowflake-update

Input

Context Output

There is no context output for this command.

Command Example

snowflake-update warehouse=demo_wh database=demo_db schema=public db_operation="update test set NAME='Persimmon' where ID=18"

Human Readable Output

Operation executed successfully.