Logic Apps :: parse JSON or CSV and make a list of e-mails separated by semi column

Question

I created a Logic Apps that allows me to query the Azure Resource Graph Explorer and send a list of untagged resources through e-mail.

The Logic Apps is parsing JSON and putting the results in CSV. The CSV looks like this:

name	type	location	tags_createdBy
Resource	microsoft.compute/virtualmachines	westeurope	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	westeurope	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	eastus	[email protected]
Resource	microsoft.compute/virtualmachines	germanywestcentral	[email protected]
Resource	microsoft.compute/virtualmachines	westeurope	[email protected]
Resource	microsoft.compute/virtualmachines	switzerlandnorth	[email protected]
Resource	microsoft.sql/servers/databases	eastus
Resource	microsoft.storage/storageaccounts	westeurope
Resource	microsoft.storage/storageaccounts	switzerlandnorth	[email protected]
Resource	microsoft.storage/storageaccounts	eastus	[email protected]
Resource	microsoft.storage/storageaccounts	germanywestcentral	[email protected]
Resource	microsoft.storage/storageaccounts	germanywestcentral	[email protected]
Resource	microsoft.storage/storageaccounts	eastus
Resource	microsoft.storage/storageaccounts	eastus
Resource	microsoft.storage/storageaccounts	eastus	[email protected]
Resource	microsoft.storage/storageaccounts	eastus
Resource	microsoft.storage/storageaccounts	westeurope

I would like to parse the tags_createdBy column and take a list of e-mails.

In this case in example the e-mails will be only 3:

• [email protected]
• [email protected]
• [email protected]

I then would like to insert those e-mails in the list of recipients because I want them to receive that e-mail, like this:

So the question is how to:

1. parse JSON or CSV file
2. take each value only one and erase NULLs
3. separate each value by a column or semi column
4. insert that into the e-mail recipient in Logic App.

Answer 1

After reproducing from my end, I could able to get this work by Parsing the json initially. Considering below is the Json, I have used Parse JSON action in order to extract tags_createdBy column.

[
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "switzerlandnorth",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.sql/servers/databases"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "switzerlandnorth",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  }
]

Here is the flow of my logic app which extracts tags_createdBy from the json and saves it in an array variable using Append to array variable.

To remove duplicates and get only the unique values, I have used union and filtered the array to remove null values from the resultant. Below is the expression and the flow used in my logic app.

union(variables('tags_createdBy'),variables('tags_createdBy'))

Expression to replace ',' with ';'

replace(string(body('Filter_array')),',',';')

RESULTS: