Generic SQL
GenericSQL Pack.#
This Integration is part of theGeneric SQL integration for the Databases: MySQL, PostgreSQL, Microsoft SQL Server and Oracle.
#
Default portsIf 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 ArgumentsSpecify arguments for the configuration of an instance name-value pairs, for example:
Separate pairs using & character, for example:
#
Connection PoolingBy 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 VariablesThere 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.
#
CommandsYou 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. The two commands are the same, they can get the same arguments and will provide the same outputs.
- query
- sql-command
#
1. queryRunning a sql query
#
Required PermissionsPermissions to the database are needed
#
Base Commandquery
#
InputArgument 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 OutputThere 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 (11111, :x , :y)" bind_variables_names=x,y bind_variables_values="test,playbook"
#
Context Example#
Human Readable OutputCommand executed
#
Command Example!query query="delete from TestTable where ID=11111"
#
Context Example#
Human Readable OutputCommand executed
#
2. sql-commandRunning a sql query
#
Base Commandsql-command
#
InputArgument 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 OutputThere 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 (11111, :x , :y)" bind_variables_names=x,y bind_variables_values="test,playbook"
#
Context Example#
Human Readable OutputCommand executed
#
Command Example!sql-command query="delete from TestTable where ID=11111"
#
Context Example#
Human Readable OutputCommand executed
#
Troubleshooting#
General Test Connection ErrorIn 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-command
withdebug-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 ServerWe 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.
#
OracleIf 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