Automating Azure Workbook Queries with Azure Logic Apps

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Azure Workbooks is a powerful tool for data visualization and analysis. By integrating with Azure Logic Apps, you can automate your workbook queries and receive the results via email or other platforms. In this blog, we'll show you how to use Azure Logic Apps to send information from your Azure Workbook queries directly to your inbox, on a schedule that suits your needs.

Before I show you how to build this solution, lets briefly talk on Azure Workbooks and Azure Logic Apps.

Azure Workbooks is a feature of Azure Monitor that allows you to create interactive, web-based reports using data from Azure Monitor logs. You can use Workbooks to analyze and visualize data, create alerts, and share your reports with others. Workbooks are a useful tool for monitoring and troubleshooting your Azure resources, as they allow you to quickly and easily view data from multiple sources in a single place.

Going in depth on Azure Workbooks is beyond the scope of this blog but if you’re interested then click here

Azure Logic Apps is a cloud service that helps you automate and orchestrate tasks, business processes, and workflows when you need to integrate apps, data, systems, and services across enterprises or organizations. Logic Apps simplifies how you design and build scalable solutions for app integration, data integration, system integration, enterprise application integration (EAI), and business-to-business (B2B) communication, whether in the cloud, on premises, or both. With Logic Apps, you can create automated workflows by using a visual designer to connect to various services, triggers, and actions

If you’re interested in the connectors available in Logic Apps take a look here

If you’re interested in detail documentation on the product click here

Example Solution

I’m going to walk through creating a report that is sent out once a week. For this walkthrough we will use Azure Workbooks of type "Secure Score Over Time", select relevant query, format it into a HTML file, and attach that result to an email. To accomplish this, we need to:

1. Select the query that will pull this information
2. Design a Logic Apps job to schedule the query and then email the results out

Selecting the Kusto Query

In this scenario we already have Defender for Cloud enabled with workbook "Secure Score Over Time" been used (please make sure the precondition for running the workbook were done)

To start with open the workbook and shift to edit mode:

Go to this section and press on the Edit:

The edit will allow you to review the queries behind the workbook section and to copy the query. The edit for the selected section will look like this:

This present the query of secure score over time.

You can copy the query and change it in Log Analytic workspace before you deploy it to Logic Apps, for visibility and alignment with business needs.

For example:

•  I want to change the column from DiffSevenDays to Diff 7 Days
•  I want to change the column from DiffMonth to Diff Month
•  I want to remove the Grace period column

You can edit the query in log analytic workspace, test it, and once you feel satisfied with the results, you can continue to Logic Apps.

To learn more on KQL, please press here

Logic Apps

Now we need to move over to Logic Apps to build the logic that will email out the above query's results.

In Azure Logic Apps we’ll start by clicking “Add”.

Give it a descriptive name like ‘Weekly-Secure-Score-Report’ and hit create to get started.

Once its created, start designing your solution by going to “Logic app designer”

Start with the Recurrence connector

Change the recurrence to once per week and click on “+ New step”

Search for “Azure Monitor Logs” and choose “Run query and visualize results”

Click on “Sign in” to log into Azure and select the Azure Log Analytics Workspace you want to query. 

Once you sign in you’ll need to provide the following information:

                Subscription: Azure Subscription where the Log Analytics Workspace is located

                Resource Group

                Resource Type: Workspace

                Query - please insert the query from first step

                Time Range

                Chart Type

In the Time Range, please select custom value and type: Last 30 days

Once you have this filled out click on the “+ New Step” below your “Run query and visualize” activity. Search for “Office 365” and choose “Office 365 Outlook.”

We can choose either “Send an email (V2)” or “Send an email from a shared mailbox (v2)” In this case because I don’t have a shared mailbox to use I’ll choose the first option.

Sign into your Office 365 account to get started. Fill out the information that you want for:

                To

                Subject

                Body

Add Attachment parameter:

this will supply you with two new fields to fill out. The attachment name and attachment content. We want to use the data from the previous step. First click in the box for the “Attachment Name.” This will bring up a window on the right-hand side where you can select dynamic data. In this case we only had one previous step which was to run the query. So under “Run query and visualize results” choose “Attachment Name”.

Do the same for attachment content.

That’s it! We need to first click on “Save” to commit our changes. After the save is complete, we can click on the “Run” option to test our solution.

You should get an email soon after with an attachment that looks something like the following:

Further Reading:

Common scenarios, examples, tutorials, and walkthroughs for Azure Logic Apps

Secure score over time