topic Re: XSOAR Playbook SQL Query in Cortex XSOAR Discussions

XSOAR Playbook SQL Query

GSecurity — Thu, 29 Feb 2024 04:29:59 GMT

hello everyone !

I'm new to XSOAR, I hope you'll be patient with me. I have created a small job in which I execute a SELECT type SQL query, the result is a series of records. Here I have some doubts:
- How can I map the result of each record in each field of type "Output"? My goal is to add a new task which executes some conditionals based on the records obtained with the previous query.

In addition to this, I tell you that I have two instances of SQL SERVER (Connections with 2 databases), after executing the SQL SELECT query I cannot identify which database the query is being executed in. Where do I configure so that the SQL query is executed on instance1 or instance2?

My goal is for the final result of the SQL query to be sent to certain emails as a report. I plan to execute this playbook within a job.

Thank you

Greetings

Re: XSOAR Playbook SQL Query

madani — Thu, 29 Feb 2024 12:45:56 GMT

Hi !

What do you mean by Output" in "in each field of type "Output""? An entry in the War Room or the Output settings of an automation ?

Regards

Re: XSOAR Playbook SQL Query

gerardo.rodriguez — Mon, 04 Mar 2024 20:27:45 GMT

hi,

I mean how can I do something similar as I show in the image, basically what I need is to be able to make a conditional to search for a record of the SQL query executed in the query

thanks

Re: XSOAR Playbook SQL Query

madani — Tue, 05 Mar 2024 13:20:47 GMT

To custom the mapping of an automation result you can use the argument "extend-context".
In a playbook task you can find this argument under the "Advanced" tab.
For exemple if you using this automation (https://xsoar.pan.dev/docs/reference/integrations/generic-sql#2-sql-command), you can save the Result key under a new context key with : -extend-context:"NewContextKey=Result"

# Documentation for extend-context
https://xsoar.pan.dev/docs/playbooks/playbooks-extend-context

If you want to make a more advanced mapping, i advise you to make your own script wrapping your SQL command, format the result, and push it to the context with CommandResults()

# Documentation for CommandResults
https://xsoar.pan.dev/docs/integrations/context-and-outputs

For the selection of the instance, you can use the argument "using", also present under the "Advanced" tab in the playbook task panel, where you can precise a name instance. It will also prevent the multi-instance execution

PS : I can't see the image you have mentionned.

Re: XSOAR Playbook SQL Query

gerardo.rodriguez — Tue, 05 Mar 2024 17:13:15 GMT

Thank you for your response, unfortunately it is not clear to me how to specify in my case the field I need in Extend-context. Might you help me? If I need to obtain the "record_number" column, what should I place?

Re: XSOAR Playbook SQL Query

madani — Wed, 06 Mar 2024 11:45:58 GMT

To have the list of the record_number youcan use -extend-context="SQLResult=GenericSQL.GenericSQL.Result.record_number"

After that if you give this list as argument in a an automation, the automation is going to process the list elements (the record_numbers) one by one, same for a conditionnal task.

According to what you want to do with, you maybe need a subplaybook to iterate on each record_number. But keep in mind you can use a filter on the argument instead of a condtionnal task