Routing Azure Resource Logs

Diagnostic logs provide rich, frequent data about the operation of an Azure resource. Azure Monitor makes the following two types of diagnostic logs available:

– **Tenant logs:** These logs come from tenant-level services that exist outside of an Azure subscription, such as Azure Active Directory logs.
– **Resource logs**: These logs come from Azure services that deploy resources within an Azure subscription, such as network security groups or storage accounts.

In this hands-on lab, we discuss how to make resource logs available to other Azure services, such as Azure Storage and Azure Monitor. Today, we assume the role of an Azure administrator for Coder Analytics, Inc. We are currently utilizing an Azure SQL database for our production data mining software. We’ve been asked by several other groups to maintain logging data for this database:

– IT management would like to see an overview of SQL logging and metrics.
– IT security would like to maintain a copy of the logging data for archival purposes.

In this lab, we take a look at the various services where we can route this monitoring data. We then generate sample data and view it in each of these services.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log On to the Azure Portal and Prepare the Azure Resources

Log in to the Azure portal with the credentials provided.
Click All Resources in the navigation hub menu and become familiar with the Azure resources that have been provisioned. These include:
- 1 Azure SQL database
- 1 Azure SQL database server
- 1 Log Analytics workspace
- 1 Azure storage account
Note the naming convention of the SQL resources, particularly the 5-character lab code suffix. This is unique for all labs.

Enable SQL Firewall Access to the Virtual Network

Click on the SQL server named sqls-XXXXX, where XXXXX is a 5-character unique lab ID for this lab.
In the sidebar menu, scroll down to Security and select Networking.
Click + Add your client IPv4 address (Your_IP_Address) to automatically add your IP address to the server’s firewall.
Click Save to save the changes.

Deploy Azure SQL Analytics (Preview)

In the Azure portal, search for "Azure SQL Analytics" in the search bar at the top of the page.
Deploy the solution in the Log Analytics workspace named laws-XXXXX, where XXXXX is a 5-character unique lab ID for this lab.

Configure Diagnostic Settings for the Azure SQL Database

In the Azure portal, under All resources, click the entry for the SQL server named sqldb-XXXXX, where XXXXX is a 5-character unique lab ID for this lab.

Configure Diagnostic Settings

Click Diagnostic settings in the database blade menu.
Click + Add Diagnostic setting and configure the following settings:
- Name: SQLDiag1
- Archive to a storage account: Checked
  - Subscription: Leave as default
  - Storage Account: saXXXXX, where XXXXX is a 5-character unique lab ID for this lab
- Send to Log Analytics: Checked
- Subscription: Leave as default
- Log analytics workspace: laws-XXXXX, where XXXXX is a 5-character unique lab ID for this lab
Check all logs and metrics and set the retention to 7 days.
Click Save to update the diagnostic settings.

Query the SQL Database to Generate Metrics Data

In the Azure portal, click All Resources.
Click on the SQL database named sqldb-XXXXX, where XXXXX is a 5-character unique lab ID for this lab.
Click Query editor (preview) in the blade navigation menu.
Sign in to the Azure database using azureadmin as the login and LA!2019!Lab1 as the password.
Enter the following T-SQL query into the window and click Run to execute the query:

SELECT TOP (1000) *
FROM SalesLT.SalesOrderHeader AS Header JOIN SalesLT.SalesOrderDetail AS Detail
ON Detail.SalesOrderID = Header.SalesOrderID
JOIN SalesLT.Product AS Product
ON Detail.ProductID=Product.ProductID

Execute the query several times to generate database performance load.

Note: Wait about 5-25 minutes before proceeding to the next objective.

View Logging Data in the Various Resources

Azure Storage

In the Azure Portal, click All Resources.
Click on the storage account named saXXXXX, where XXXXX is a 5-character unique lab ID for this lab.
In the blade menu, click Containers.
Notice the containers that now exist in the storage account. Explore the database logs in the containers.

Containers

Blob1

Azure Log Analytics

In the Azure portal, click All Resources.
Click on the Log Analytics workspace named laws-XXXXX, where XXXXX is a 5-character unique lab ID for this lab.
In the workspace’s sidebar menu, in the Classic section, select Legacy Solutions.
Select AzureSQLAnalytics(laws-XXXXX).
In the Overview pane, click View Summary under the Azure SQL Analytics (Preview) box.