Iterate through json output of table(rows and columns) resulted from query

[Tejaswini139]

Hi team,

I want to run "Execute custom query" from Microsoft365Defender Integration. query is:
EmailEvents | where Subject contains "testvalue".
This query returns result table which has rows and columns in json format.
for example,
row1 {col1:val1, col2:val2,..}, row2{col1:val1, col2:val2,..},row...
DeliveryLocation is one of the column names from the json result.
I need to check whether DeliveryLocation=Inbox/folder and for each row where this condition is met, should remediate those mails accordingly.
I exported the json result of this action and imported it in the IDE for this integration.
How can I iterate through each row from json result and fetch DeliveryLocation. 
In the placeholder of the condition, check for DeliveryLocation and remediate particular emails in every row where DeliveryLocation=Inbox/folder.

Thanks,
Tejaswini

[Kyle_M]

Hi @Tejaswini139 

You can accomplish this with the Expression Builder. It will allow you to filter the objects in the list and return the objects that match.  Below is an example where I want to return the AlertId of each object in the list if the Severity is Medium.  The Expression Builder will return a comma separated string of AlertIds since I chose to return the AlertId attribute.

For your use case your filter would look something like this: filter("DeliveryLocation", "=", "Inbox/folder").  You can use the Run button to verify your expression before moving on.  From there you can return what makes sense for the next action in the use case. 

Here's some additional documentation around the expression builder:

https://cloud.google.com/chronicle/docs/soar/respond/working-with-playbooks/using-the-expression-bui...

https://cloud.google.com/chronicle/docs/soar/respond/working-with-playbooks/use-cases-for-expression...

[Tejaswini139]

Thanks @Kyle_M , As I mentioned,  How can I remediate multiple emails (add "Delete mail" action)  wherever DeliveryLocation=Inbox/folder.

[Kyle_M]

@Tejaswini139 I would need more details to give you an exact answer, but normally you would add the delete email action to the playbook. The parameter for the mailboxes to delete from usually takes a comma separated list, so you can click on the placeholder icon to leverage the expression builder to filter out the JSON data and return a comma separated list. You can try the filter I mentioned in my response, but you will need to validate it and return the field that holds the mailbox.