Thomas Thornton – Microsoft Azure MVP – GitHub Star – HashiCorp Ambassador

Microsoft Azure Blog – Microsoft MVP

Log Analytics queries to CSV emailed using Azure Logic Apps

on by 12 Comments

Log Analytics queries to CSV emailed using Azure Logic Apps can be used for automated reporting – using 4 steps! In this blog post, I will demo how this is done

What are Logic Apps?

A little resource with a big outcome, Logic Apps assist you with automated workflow; scheduling, automating, coposing a task or even rewiting a task.

Logic Apps run in a “trigger”-type fashion, it requires an event to happen or when something is specifically met, to begin the flow of the specific app.

What is Log Analytics?

A Log Query resource that collects data within Azure from numerous points, resources and also Azure Monitor. Data is then stored and Kusto query language is used to retrieve this data in various formats and displays.

The Solution

Will consist of a Logic App, running the Kusto query on a schedule once a day. Once query is ran, the queried data will be converted to .csv and emailed.

Previous Blog Post

In a previous blog post; I detailed a similar approach but instead it had emailed the queried data with .html attachment. See here for further details on that blog post

This blog post also shows how to create a Logic App, review this blog post to see the creation steps.

The Solution

Consists of 4 stages within my logic app.

Recurrence:- The trigger essential, to run at a specific hour or day etc
Run query and list results:- Run Log Analytics query
Create CSV Table:- Creates CSV table of the queried data from the Log Analytics Query
Send an email:- Sends email with attached .csv file

Logic App Break Down

Recurrence Task/Trigger Step

In this example, I have set it to run once per day

Run query and list results Step

Sign-in with user credentials or select Service Principal

Subscription: Select Subscription of Log Analytics Workspace
Resource Group: Log Analytics Resource Group
Workspaces Name: Log Analytics Workspace
Query: Enter Log Analytics query you want to report on
Time Range: Set in query

Log Analytics query showing data in Log Analytics:-

Create CSV Table Step

Value is taken from the above step

Send an email (V2) Step

Body:- test email text
Subject:- Subject for email
To:- Sender Address
Attachment Contents:- Contents output from Create CSV Table Step
Attachment Name: Name of csv

Select “see more” in Create CSV table tab to get the output for above

Lets run the Logic App to test

Reviewing the Create CSV Table Step , shows the output of Log Analytics query in .csv format

Sample data from test.csv file

12 comments

  1. Hello Thomas,
    Thank you for this blog post, it is indeed exactly what I am trying to do since the beginning but I have a problem attaching the results to the mail.
    When I am in the field ‘Attachment Content’, I can’t select the output of the ‘create csv table’. I have just a message saying that there is no output to match the content of the input format. ( https://postimg.cc/k2CbD2mR )
    But when I am in the field ‘Attachment name’, then I can select the Output of the ‘create csv table’ ( https://postimg.cc/d7Lt0FLT ).
    The query ( https://postimg.cc/Dm2s2J7W ) is working well as it it the same as the one I am using for sending the result as html (many thanks for your blogpost, it was very helpful).
    Marc

    Reply

    1. Hi Marc,

      I’ve updated my blog with a screenshot that you provided – highlighted in yellow is the part you need to select to get the “output”

      See part in blog:- “Select “see more” in Create CSV table tab to get the output for above”

      Thanks

      Thomas

      Reply

  2. Thomas,
    If I may add another thing to my problem; waiting to find a solution for sending the file by mail, I tried to put the file on sharepoint and guess what …. the exact same problem.
    Right after the Run query and list result + Create CSV table, I added a step Create File. In the Create File step I entered the site address, folder path, no problem with that but in the File Name field I can put the output of the csv table and in the File Content, nothing, again the message ‘we can’t find any outputs to match this input format’.

    I even tried the Create File with the html file (from your previous blog post). So I have Run query and visualize results + Create file
    In the field File Name, I can choose attachment content, attachment name, body.
    But in the field File content, same story, the message ‘we can’t find any outputs to match this input format’.

    I am sure I am missing a small thing but I can’t find what.

    Marc

    Reply

      1. I just received the notification of your message when I had hit the send button of my second message.

        Oh woaw…… I read this message so many times and never clicked on it.
        Thank you very much Thomas for pointing my stupid mistake.
        I am very sorry, of course now everything is working fine (create csv, create file) 🙂

        Thank you, thank you.
        Marc

      2. Hi Marc,

        Great news – Glad I could have helped, have fun with your Logic Apps! 😀

        Thanks

        Thomas

  3. And now that it is working, my manager will give me a lot of fun ahahah 😀
    Thanks
    Marc

    Reply

  4. Hi Thomson,

    Can we add multiple KQL queries and send the attachments using same logic app ?

    Thanks in advance
    Melvin Williams

    Reply

Leave a Reply