Iterate through json output of table(rows and columns) resulted from query
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.
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:
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:
Thanks @Kyle_M , As I mentioned, How can I remediate multiple emails (add "Delete mail" action) wherever DeliveryLocation=Inbox/folder.
Thanks @Kyle_M , As I mentioned, How can I remediate multiple emails (add "Delete mail" action) wherever DeliveryLocation=Inbox/folder.
@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.
Is there any documentation around supported operators for this? I’d like to use a ‘startswith’ check.
Sorry I’m still understanding the usecase…
Graph Mail takes a single input of comma separated IDs
e.g. 1111,2222,3333. So that is what we need to provide as input from the JSON output above
Placeholder functions will iterate through loops and pull out keys.
Image 1 - I am building a JSON object here to test against
Image 2 - Using placeholder functions, I look for each match, then for the match extract ID
Image 3 - The result, this appears to be in the right format.
Would this work to filter your mail results, and pass the ID throug to delete?
Note - Playbooks loops could also help here in a different way, but I don’t think we need them
