Logic App - Export to CSV Issues

Question

Logic App - Export to CSV Issues

Son 316

Hi,

I am very new to Logic Apps so just learning the ropes currently.

We are using Azure AUM for patching our virtual machines so would like to pull a patch status report, export to CSV and send on via email or even upload directly into Power BI so my manager can view reports on status etc. I have the following so far, I am creating a variable, running a Azure Resource Graph query which collects the required information, but then trying to export to CSV is failing, I am unsure of how I get the resulting data into a variable of an Array type:

Patch Status 1

Patch Status 2

Patch Status 3

This is the result and error:

Patch Status Result

Hoping someone can help or point me in the direction of learning material for this particular use case.

Thanks!

MikeUrnun 9,777 Reputation points Moderator

2023-11-01T15:51:56.1033333+00:00

@Son Following up to see if the answer below helps. Feel free to get back to us if you need any assistance.
Son 316 Reputation points

2023-11-01T17:07:21.71+00:00

Hi Mike,

Replied on the other post below, can you see that? Still having some issues that I hope you can help with.

Accepted answer

0 additional answers

Your answer

MikeUrnun 9,777 Reputation points Moderator

2023-11-01T15:51:56.1033333+00:00

@Son Following up to see if the answer below helps. Feel free to get back to us if you need any assistance.
Son 316 Reputation points

2023-11-01T17:07:21.71+00:00

Hi Mike,

Replied on the other post below, can you see that? Still having some issues that I hope you can help with.

Answer 1

MikeUrnun 9,777 Moderator

Welcome to Azure, @Son , and thanks for reaching out.

Based on the screenshots, the Create CSV table action should have the response data from the HTTP 2 action instead of the PatchStatus variable (which carries the KQL for the Azure Resource Graph). When you place your cursor on the From field in the Create CSV table action, you should have the Dynamic Contents popup show, and in the same popup, you should be able to select the correct response data from the HTTP 2 action.

Adding the response data directly from the HTTP 2 action as-is might result in the same error as the response data will likely be a type of Object whereas the From field in the Create CSV table action expects it to be of type Array. In that case, you can simply leverage the Compose action to construct a new array output which can then be referenced in the From field. The Perform data operations in Azure Logic Apps doc should provide some context & help but feel free to use the comments below for any follow-up questions ;)

Please "Accept Answer" if the answer is helpful so that others in the community may benefit from your experience.

Son 316 Reputation points

2023-11-01T16:05:09.2833333+00:00

Hi Mike, thanks for your welcome and reply!

I have been doing some more digging and think I am getting somewhere.

I have created an open variable, parsed the JSON data and have tried appending the different columns I need into that variable using a For Each loop. The only issue I now have is how to export that data to the CSV table but in a sensible format.

I tried using a Create CSV table method and called my $Results variable but that variable just contains a flood of data not in any structure.

I feel like I am getting close! Thanks for your help so far :)

MikeUrnun 9,777 Moderator

@Son It looks like you're close. Here's the structure you want to build with the Array which will aggregate the CSV format correctly when using the Create CSV table action:

[
    {
        "ITime":"11:11:11",
        "machineName":"agentvm1",
        "patchName":"patch-0001",
        "installationState":"complete",
        "kbld":"success"
    },
    {
        "ITime":"22:22:22",
        "machineName":"agentvm2",
        "patchName":"patch-0002",
        "installationState":"complete",
        "kbld":"success"
    },
    {
        "ITime":"33:33:33",
        "machineName":"agentvm4",
        "patchName":"patch-0004",
        "installationState":"complete",
        "kbld":"success"
    },
    {
        "ITime":"44:4:44",
        "machineName":"agentvm3",
        "patchName":"patch-0003",
        "installationState":"complete",
        "kbld":"success"
    }
]

which can be transformed to CSV as follows:

User's image

which results in the following CSV format:

User's image

If the Create CSV table action turns out to be difficult to use, an alternative approach is: Instead of an Array, consider aggregating the data to String type and take advantage of functions like concat(). CSV is a giant string where the columns are delimited with commas and new rows are denoted as \r\n newline. Once you have the aggregated data that is structured accordingly, you can then specify it as the content of the email attachment.

Son 316 Reputation points

2023-11-02T07:22:20.6033333+00:00

Hi Mike, that's perfect! I am now getting the data in the structure I want and it is starting to make more sense to me, appreciate the help! My final hurdle (I hope) I would like to send this attachment via email but getting the following issue.

The attachment name provides a dynamic content option of the Output from the Create CSV Table step, which for out I have used as Output and appended .csv to the name. However, the actual content only shows me "Current Item" from the For Each loop. When I try saving that it errors with the following:

"Failed to save logic app #######. The template validation failed: 'The inputs of template action 'Send_an_email_(V2)' at line '1 and column '2964' is invalid. Action 'For_each' must be a parent 'foreach' scope of action 'Send_an_email_(V2)' to be referenced by 'repeatItems' or 'items' functions.'."

The attachment contents I thought I would be able to the info from the CSV table we created but seems the For Each step higher in the chain is causing some issues?
MikeUrnun 9,777 Reputation points Moderator

2023-11-02T16:11:02.2233333+00:00
@Son Here's the assignment of each Dynamic Content for creating the CSV attachment in the Outlook connector:

Output from the Create CSV table should be assigned to the Attachment Content.

Attachment Name can be anything you want to name the attachment file but make sure that the .csv extension is there without a blank space.

Not sure why you're getting the Current Item (and you don't need to use it); if both "Create CSV table" and "Send an email" actions are placed inside the same Foreach loop iteration, they need to be outside of the loop.
MikeUrnun 9,777 Reputation points Moderator

2023-11-02T17:10:52.8+00:00

Based on the screenshots, you do have them placed outside the loop which is correct, it's probably the use of Current Item referenced outside the loop that's causing the validation error. So remove the use of Current Item, and reassign the Dynamic Contents as described above, and you should have the whole thing working ;)
Son 316 Reputation points

2023-11-03T07:24:22.96+00:00

Hi Mike,

Sending you virtual beverages of your choice ;).

It's now working, can you believe it that all I had to do was select 'see more' under the Create CSV Table option... Thanks so much for all your help!
MikeUrnun 9,777 Reputation points Moderator

2023-11-03T15:56:31.54+00:00

@Son I fell for that too when I was first using Logic Apps. Thanks for the beverages! ;) Feel free to reach out to us here again!

Share via

Logic App - Export to CSV Issues

0 additional answers

Your answer