XSOAR Execute query SQL

[gerardo.rodriguez]

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

[madani]

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'))

[gerardo.rodriguez]

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)

[madani]

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.