Automating Azure Workbook Queries with Azure Logic Apps

[Guest inbalsilis]

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:

 

 

 

Scope	Current Score	Diff 7 Days	Diff Month
GCPNinjaConnector	34.65	-0.488	0
Azure-subscribtion-prod1	39.59	-0.302	-5.423
AWSNinjaConnector	34.25	0.175	44.515

 

 

 

Further Reading:

 

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

 

Secure score over time

 

Continue reading...