Implementing Azure Monitor

45 minutes
  • 4 Learning Objectives

About this Hands-on Lab

This hands-on lab is designed to teach you the basics of Azure Monitor. You will explore how Azure Monitor works in a SQL database through the implementation of Azure Monitor and the configuration of alerts, security logs, log analytics queries, and basic metric charts. This is designed as a beginner-friendly lab with little to no prior experience required. We will not be diving deep into Log Analytics or query language but will instead focus on the implementation aspects of Azure Monitor.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Prepare the Environment
  1. Provision a SQL database.

    • Create a new server in the US West region:
      • Basic (5 DTU), 2 GB data size
      • Public endpoint with Azure services and client IP address added
      • Sample existing data
      • No trial
  2. Provision a Log Analytics instance in the West US region.

Implement Monitoring in SQL Database

Now that we have our environment provisioned, let’s go into SQL Database and implement monitoring. In order to do that, we will implement security audit logs, create an alert rule, implement diagnostic settings, and create charts for Avg DTU Limit and Avg Data Space.

  1. Implement security auditing and send logs to a newly created storage account.
  2. Create an alert rule.
    • When DTU used goes above 70% (Static, with a 5-minute aggregation and 1-minute frequency).
    • Create an action group with an email notification.
  3. Create a chart for Avg DTU Limit and Avg Data space used.
  4. Create a diagnostic setting to send logs for errors, timeouts, and basic metrics to Log Analytics.
Review and Create a Log Query

Create some activity within SQL Database and then learn how to look at logs and utilize the query functionality.

Note: For the Azure Log Analytics page to work properly, you must enable cookies for the site.

Run the following query in logs:

// Avg CPU usage
// Avg CPU usage in the last hour by resource name.
//consistently high averages could indicate a customer needs to move to a larger SKU
| where ResourceProvider == "MICROSOFT.SQL" // /DATABASES
| where TimeGenerated >= ago(60min)
| where MetricName in ('cpu_percent')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource  // subtract Resource name for _ResourceId
| summarize CPU_Maximum_last15mins = max(Maximum), CPU_Minimum_last15mins = min(Minimum), CPU_Average_last15mins = avg(Average) by Resource , MetricName

Note: This query may not show immediately in your lab. If you run the query and get no results, you likely need to let more time elapse in the environment for Azure to pick up on the activity.

Review Audit Logs and Find Storage Logs

Run sample queries in the query editor, and then look for them in the security audit logs and in the Azure storage.

  1. Run 2 sample queries using the query editor:

    SELECT TOP 20 pc.Name as CategoryName, as ProductName
        FROM SalesLT.ProductCategory pc
        JOIN SalesLT.Product p
        ON pc.productcategoryid = p.productcategoryid;
    INSERT INTO [SalesLT].[Product]
          ( [Name]
          , [ProductNumber]
          , [Color]
          , [ProductCategoryID]
          , [StandardCost]
          , [ListPrice]
          , [SellStartDate]
          ,GETDATE() );
  2. Find your queries in the audit logs.

  3. Find your logs in your newly created storage account.

Additional Resources

You work for Squirrel's Squirrelly House of Sumptuous Saltines (no idea... you're the one working there). In the wake of increased fraudulent activity you have been asked to prepare a cloud environment for production by implementing security logs for storage and viewing as well as creating charts and alerts to optimize the environment.

To get started, log in to the Azure portal using the credentials provided.

"Note: For the Azure Log Analytics page to work properly, you must enable cookies for the site. In Chrome, click the padlock icon next to the web address, choose cookies, look in the blocked tab, and allowed the blocked cookies. After that, refresh the page."

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?