Generic SQL

Overview


Generic SQL integration for the Databases: MySQL, PostgreSQL, Microsoft SQL Server and Oracle.

Generic SQL Playbook


The initial state of the table is that in each database there are 4 records. While the playbook run, another record is added and later removed from the table.

Configure Generic SQL on Demisto


  1. Navigate to Settings > Integrations > Servers & Services.
  2. Search for Generic SQL.
  3. 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)
  4. 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. 1. query 2. sql-command

1. query


Running a sql query

Required Permissions

Permissions to the database are needed

Base Command

query

Input
Argument NameDescriptionRequired
limitNumber of results you would like to get backOptional
queryThe sql queryRequired
skipNumber of results you would like to skip onOptional
bind_variables_namese.g: "foo","bar","alpha"Optional
bind_variables_valuese.g: 7,"foo",3Optional
Context Output

There is no context output for this command.

Command Example

!query query="select * from TestTable" limit=10 skip=0

Context Example
{
"GenericSQL": {
"GenericSQL": {
"Query": "select * from TestTable",
"InstanceName": "MySQL_new_schema",
"Result": [
{
"LastName": "Grace",
"ID": 22222,
"FirstName": "Bob"
},
{
"LastName": "Jacob",
"ID": 33333,
"FirstName": "Liya"
},
{
"LastName": "James",
"ID": 44444,
"FirstName": "Chris"
},
{
"LastName": "Zohar",
"ID": 55555,
"FirstName": "Tamar"
}
]
}
}
}
Human Readable Output

Query result:

IDLastNameFirstName
22222GraceBob
33333JacobLiya
44444JamesChris
55555ZoharTamar
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 NameDescriptionRequired
limitNumber of results you would like to get backOptional
queryThe sql queryRequired
skipNumber of results you would like to skip onOptional
bind_variables_namese.g: "foo","bar","alpha"Optional
bind_variables_valuese.g: 7,"foo",3Optional
Context Output

There is no context output for this command.

Command Example

!sql-command query="select * from TestTable" limit=10 skip=0

Context Example
{
"GenericSQL": {
"GenericSQL": {
"Query": "select * from TestTable",
"InstanceName": "MySQL_new_schema",
"Result": [
{
"LastName": "Grace",
"ID": 22222,
"FirstName": "Bob"
},
{
"LastName": "Jacob",
"ID": 33333,
"FirstName": "Liya"
},
{
"LastName": "James",
"ID": 44444,
"FirstName": "Chris"
},
{
"LastName": "Zohar",
"ID": 55555,
"FirstName": "Tamar"
}
]
}
}
}
Human Readable Output

Query result:

IDLastNameFirstName
22222GraceBob
33333JacobLiya
44444JamesChris
55555ZoharTamar
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

Possible Errors:

  • The bind variables lists are not is the same length
  • command is not an existing Generic SQL command'