topic Re: XSOAR Execute query SQL in Cortex XSOAR Discussions

XSOAR Execute query SQL

gerardo.rodriguez — Mon, 04 Mar 2024 21:13:31 GMT

Hello !!

I have created an automation to execute a SQL query (SELECT), however additional fields appear in the result ("module name", "brand", etc). How can I remove these fields? My intention is to email the result of the SELECT query only the fields that are within the query. Thank you

Re: XSOAR Execute query SQL

madani — Tue, 05 Mar 2024 13:32:46 GMT

Hello

You have several ways to do that :

1) In your automation when you call another automation use execute_command() instead of demisto.executeCommand(). But like that you can use some automations like is_error().

2) With demisto.executeCommand() the data you want are present under the [0]["Contents"]. The index 0 can change if your executed command have multiple outputs.

# Code

results = demisto.executeCommand("SQLQuery") if is_error(results): demisto.error(results[0].get('Contents')) return demisto.results(results[0].get('Contents'))

Re: XSOAR Execute query SQL

gerardo.rodriguez — Tue, 05 Mar 2024 16:12:09 GMT

Thanks for your response, I have tried the following without success:

a= demisto.executeCommand("sql-command", {"query":"select record_number, date_time, esg_message_id from [esglogdb76_40].[dbo].[esg_detail_policy_process_results_email_address]"})

if is_error(a):

demisto.error(a[0].get('Contents.record_number'))

return demisto.results(a[0].get('Contents.record_number')

"record_number" is a field in the sql query This gives me an error:

Command: !SQL-SCRIP Hide

reason reason

Error from Scripts is: Script failed to run:

Error: [Traceback (most recent call last):

File "<string>", line 8

demisto.results(a[0].get('Contents.record_number')

SyntaxError: '(' was never closed

] (2604) (2603)

Re: XSOAR Execute query SQL

madani — Wed, 06 Mar 2024 11:36:31 GMT

You just forgot a ')' in your code at line 8.

Write "demisto.results(a[0].get('Contents'))" instead of "demisto.results(a[0].get('Contents.record_number')"

By checking your post in this topic https://live.paloaltonetworks.com/t5/cortex-xsoar-discussions/xsoar-playbook-sql-query/td-p/578755 , 'Contents' will be typed as list and not dict, so you can't use .get() method on a list.