Generic SQL
Generic SQL integration for the Databases: MySQL, PostgreSQL, Microsoft SQL Server and Oracle.
Default ports#
If the port value is empty, a default port will be selected according to the database type.
- MySQL: 3306
- PostgreSQL: 5432
- Microsoft SQL Server: 1433
- Oracle: 1521
Connection Arguments#
Specify arguments for the configuration of an instance name-value pairs, for example:
Separate pairs using & character, for example:
Connection Pooling#
By default, the integration does not pool database connections. Thus, a connection is created and closed for each command run by the integration. When connection pooling is enabled, each Docker container will maintain a single connection open for time specified in the the Connection Pool Time to Live parameter (default: 600 seconds). After the time to live expires, and upon execution of a new command, the database connection will close and a new connection will be created.
Note: when pooling is enabled, the number of active open database connections will equal the number of active running demisto/genericsql Docker containers.
Bind Variables#
There are two options to use to bind variables:
- Use both bind variable names and values, for example: SELECT * from Table Where ID=:x" bind_variables_names=x bind_variables_values=123
- Use only bind variable values, for example: INSERT into Table(ID, Name) VALUES (%s, %s)" bind_variables_values= "123, Ben”
Configure Generic SQL on Cortex XSOAR#
- Navigate to Settings > Integrations > Servers & Services.
- Search for Generic SQL.
- Click Add instance to create and configure a new integration instance.
- Name: a textual name for the integration instance.
- SQL DB
- Database host
- Port
- Database Name
- Username
- Connection Arguments (ex: arg1=val1&arg2=val2)
- Click Test to validate the URLs, token, and connection.
Commands#
You can execute these commands from the Demisto 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. The two commands are the same, they can get the same arguments and will provide the same outputs.
- query
- sql-command
1. query#
Running a sql query
Required Permissions#
Permissions to the database are needed
Base Command#
query
Input#
| Argument Name | Description | Required |
|---|---|---|
| limit | Number of results you would like to get back | Optional |
| query | The sql query | Required |
| skip | Number of results you would like to skip on | Optional |
| bind_variables_names | e.g: "foo","bar","alpha" | Optional |
| bind_variables_values | e.g: 7,"foo",3 | Optional |
Context Output#
There is no context output for this command.
Command Example#
!query query="select * from TestTable" limit=10 skip=0
Context Example#
Human Readable Output#
Query result:#
ID LastName FirstName 22222 Grace Bob 33333 Jacob Liya 44444 James Chris 55555 Zohar Tamar
Command Example#
!query query="INSERT into TestTable(ID, LastName, FirstName) VALUES (11211, :x , :y)" bind_variables_names=x,y bind_variables_values="test,playbook"
Context Example#
Human Readable Output#
Command executed
Command Example#
!query query="delete from TestTable where ID=11211"
Context Example#
Human Readable Output#
Command executed
2. sql-command#
Running a sql query
Base Command#
sql-command
Input#
| Argument Name | Description | Required |
|---|---|---|
| limit | Number of results you would like to get back | Optional |
| query | The sql query | Required |
| skip | Number of results you would like to skip on | Optional |
| bind_variables_names | e.g: "foo","bar","alpha" | Optional |
| bind_variables_values | e.g: 7,"foo",3 | Optional |
Context Output#
There is no context output for this command.
Command Example#
!sql-command query="select * from TestTable" limit=10 skip=0
Context Example#
Human Readable Output#
Query result:#
ID LastName FirstName 22222 Grace Bob 33333 Jacob Liya 44444 James Chris 55555 Zohar Tamar
Command Example#
!sql-command query="INSERT into TestTable(ID, LastName, FirstName) VALUES (11211, :x , :y)" bind_variables_names=x,y bind_variables_values="test,playbook"
Context Example#
Human Readable Output#
Command executed
Command Example#
!sql-command query="delete from TestTable where ID=11211"
Context Example#
Human Readable Output#
Command executed
Troubleshooting#
General Test Connection Error#
In cases where you receive an error that is not clear when you Test the integration instance you can get detailed logs.
- Save the configured instance even though the Test doesn't work.
- In the playground, run the
!sql-commandwithdebug-mode=true. For example:A log file will be generated in the Playground. Examine the log file for further details that explain why the integration is failing.
Microsoft SQL Server#
We provide two options for connecting to Microsoft SQL Server:
- Microsoft SQL Server: Uses the open source FreeTDS driver to communicate with Microsoft SQL Server.
- Microsoft SQL Server - MS ODBC Driver: Official driver from Microsoft for Linux.
If you experience any issues communicating with your Microsoft SQL Sever, try using both options as we've seen cases where one option works while the other doesn't.
When configuring SQL Server, if you receive an error of the form:
It means there is a communication problem from the Generic SQL docker to the SQL Server. It usually means the dns hostname of the sql server is not resolving. You can try using an IP instead of the DNS. You can further test the from docker by running the following command on the Cortex XSOAR machine:
Note: Kerberos authentication is not supported.
Oracle#
If you require connecting to Oracle via a SERVICE_NAME, leave the Database Name parameter empty and add to the Connection Arguments the following:
For example:
Possible Errors:#
- The bind variables lists are not is the same length
- Command is not an existing Generic SQL command