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	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	westeurope	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	eastus	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	germanywestcentral	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	westeurope	albert.einstein@contoso.com
Resource	microsoft.compute/virtualmachines	switzerlandnorth	albert.einstein@contoso.com
Resource	microsoft.sql/servers/databases	eastus
Resource	microsoft.storage/storageaccounts	westeurope
Resource	microsoft.storage/storageaccounts	switzerlandnorth	robert.fish@contoso.com
Resource	microsoft.storage/storageaccounts	eastus	robert.fish@contoso.com
Resource	microsoft.storage/storageaccounts	germanywestcentral	robert.fish@contoso.com
Resource	microsoft.storage/storageaccounts	germanywestcentral	robert.fish@contoso.com
Resource	microsoft.storage/storageaccounts	eastus
Resource	microsoft.storage/storageaccounts	eastus
Resource	microsoft.storage/storageaccounts	eastus	hello.world@contoso.com
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:

albert.einstein@contoso.com
robert.fish@contoso.com
hello.world@contoso.com

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:

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

score 1 · Accepted Answer · answered Dec 20 '22 at 04:45

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": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "switzerlandnorth",
    "name": "Resource",
    "tags_createdBy": "albert.einstein@contoso.com",
    "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": "robert.fish@contoso.com",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "robert.fish@contoso.com",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "robert.fish@contoso.com",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "robert.fish@contoso.com",
    "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": "hello.world@contoso.com",
    "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.

enter image description here

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

enter image description here

RESULTS:

enter image description here

Thank you, how have you added the `empty{...}` in the Filter Array? — Francesco Mantovani, Dec 26 '22 at 13:52
There was a discrepancy between your code which has `"tags_createdBy": "",` and my code which has `"tags_createdBy": null,` but I fixed that. Thank you — Francesco Mantovani, Dec 26 '22 at 16:02

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

1 Answers1