Swimlane Relational Databases

the swimlane relational databases connector allows for direct sql operations and interactions with various relational databases directly from the swimlane platform the swimlane relational databases connector allows users to directly interact with various relational databases, such as ms sql server, postgresql, mysql, and oracle, within the swimlane turbine platform it enables the execution of sql queries, modifications, and stored procedures, streamlining data manipulation and retrieval tasks in security workflows by integrating this connector, users can automate database interactions, enhance incident response, and enrich security playbooks with dynamic data access, all without the need for complex coding prerequisites to utilize the swimlane relational databases connector effectively, ensure you have the following prerequisites custom authentication configured with the necessary parameters database type specify the type of relational database (e g , mysql, postgresql) username the username credential for database access password the corresponding password for the provided username host the network address of the database server database name the specific database to connect within the rdbms capabilities this connector is compatible with the following databases microsoft sql server postgresql mysql oracle although the connector was tested against the mentioned databases, it is possible to use it with other databases as long as the engines are compatible to modify data in a database (i e , insert, etc ), use the task run modification query to retrieve data from a database (i e , select), use the task run query to execute stored procedures, use the task run stored procedure asset information the asset requires a username , password , host , port , and database name database type & database engine additionally, the asset requires an engine dialect to supply the database type if supplying a database type, the connector will use the default dialects for the corresponding database ex postgresql will default to postgresql+pg8000 available types are ms sql server, postgresql, mysql, oracle it is possible to provide the specific dialect to use in case its needed available dialects are database available recommended postgres postgresql, postgresql+pg8000 postgresql+pg8000 mysql mysqlconnector mysqlconnector oracle oracle, oracle+cx oracle oracle microsoft sql server mssql+pyodbc, mssql+pymssql mssql+pymssql note in order to use mssql+pyodbc, you must install the package "pyodbc" in turbine tasks setup and filtering the task "direct query" allows inputs to be passed as wild cards any "?" present will be replaced, in order, with supplied "parameters " example query = select id from person where id = %s or id < %s parameters = \["10","20"] the following query will be executed select id from person where id = 10 or id < 20 configurations asset authentication for databases configuration parameters parameter description type required database type database type available ms sql server, postgresql, mysql, oracle string required username username to connect to the database string required password password string required host server host string required database name name of the database string required port host port to use number optional database dialect database dialect this will override the database type supplied see readme for available example postgresql+pg8000 string optional actions run modification query execute a non returning sql query such as insert on a database, requiring an input of the query string input argument name type required description query string required query using parameters is acceptable ('select from foo where id = %s or id = %s') parameters need to be supplied through the parameter input in order parameters array optional a list of parameters should be in order input example {"parameters" \["idparam1","idparam2"],"query" "insert into example1 values (2, 'bob');"} output parameter type description result string result of the operation query executed string output field query executed row count number count value output example {"result" "success","query executed" "insert into example1 values (2, 'bob');","row count" 1} run query executes a specified sql query on the database and returns the results required input 'query' input argument name type required description query string required query using parameters is acceptable ('select from foo where id = %s or id = %s') parameters need to be supplied through the parameter input in order parameters array optional a list of parameters should be in order to csv boolean optional when true, returns a csv as an attachment input example {"parameters" \["idparam1","idparam2"],"query" "select table name from dba table","to csv"\ false} output parameter type description result array result of the operation query executed string output field query executed row count number count value file object file file file name string name of the resource file file string output field file file output example {"result" \[{"id" 1,"name" "adam"}],"query executed" "select from table1","row count" 1} run stored procedure executes a specified stored procedure in the relational database and retrieves the resulting data input argument name type required description stored procedure name string required name of the stored procedure to run stored procedure params array optional parameters in order that need to be executed with the stored procedure to csv boolean optional when true, returns a csv as an attachment input example {"stored procedure name" "s getperson","stored procedure params" \["1","2"],"to csv"\ false} output parameter type description result array result of the operation query executed string output field query executed row count number count value file object file file file name string name of the resource file file string output field file file output example {"result" \[{"id" 1,"name" "adam"}],"query executed" "select from table1","row count" 1} response headers header description example content type the media type of the resource application/json date the date and time at which the message was originated thu, 01 jan 2024 00 00 00 gmt