Azure Workbooks

I’m saddened that the Workspace Summary is being deprecated in Log Analytics Workspace. I am trying to reproduce it in workbooks. While it isn’t an exact match, workbooks provide me with enough information to use and share with others.

• You can get additional resource examples and explanations about Azure Workbooks from Microsoft. Workbooks are available in different Azure resources, including Log Analytics Workspaces.

These queries only work if you have enabled Azure SQL audit and write it to a log analytics workspace. If you want to know how to set up an Azure SQL Database audit, please visit my blog page on auditing.

Workspace Summary

This is an excellent, high-level summary. It doesn’t get me everything I need, but it is a quick overview. It’s going away – eventually.

This summary can be found here:

Workbooks

This is a possible replacement for the workspace summary. It has different functionality, but it can give you some great options for visualizing and querying your audit data.

You can find the Workbooks option in your LAW here:

Some default ones are provided to you, but they don’t actually query audit data you store from Azure SQL databases. Here is a screenshot of some of the default workbooks available to you:

Creating Your Workbook

I will guide you through creating your workbook from an empty template.

This will open a blank workbook, as you might have guessed. First, we will visualize the count of queries run against each database.

Adding a Chart to Your Workbook

Choose + Add, then Add query.

This will bring up a blank query box. Put this query into it and click Run Query:

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
    and TimeGenerated > ago(1d) 
| summarize count () by database_name_s
| render barchart

Then click on Advanced Settings and enter a title:

Click Save at the top of the page. When you start working on a workbook, save it immediately. I noticed that it doesn’t always prompt you to save if it’s newly created and you navigate away from the page.

This will bring up a pane allowing you to name your workbook and click Apply. I put my workbook in the same location as my LAW.

Once you save it, it will show up in your workbook gallery under recently modified:

Now, you have one chart without text boxes, parameters, or other queries.

If you are done editing, click Done Editing at the top. If you want to add more, click + Add at the bottom. You most likely want more information than this chart provides since you can’t click on it to drill down.

Adding More Details to Your Workbook

I want more details about specific databases and timeframes. We will need to set parameters and use them to return query results. By clicking + Add, you can add these parameters:

So that you know, you will need to fill in the parameter name. I also added a display name. Click the parameter required checkbox, and don’t allow multiple selections. I also make it a dropdown and populate it from a query:

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| distinct database_name_s

Add the query and click Run Query before saving:

I also added a Time Generated parameter:

Click Done Editing on the parameters box:

You must add a query to use these parameters by clicking + Add and Add query. Use this query there:

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
   and database_name_s == '{DatabaseName}'
| project
    event_time_t,
    database_name_s,
    action_name_s,
    statement_s,
    server_principal_name_s,
    succeeded_s,
    client_ip_s,
    application_name_s,
    additional_information_s,
    data_sensitivity_information_s
| order by event_time_t desc
| limit 10000

To use the time-generated parameter, you must choose that from the dropdown settings on the query window:

Once you have your query and selected Time Generated for your Time Range, you must select values for your parameters. This is because right now, they will be unset and cause an error:

Once you select the values, you can click Run Query, and results will display if there are values for that period.

At this point, you can click Done Editing at the top of the page and SAVE!!

Importing/Exporting Your Workbook

I added more bits and pieces, like titles or explanations, to the page with text boxes. I also added another chart at the bottom to show the count of the different audit actions. These are optional. I’ve exported this as a workbook file, so you can copy this into your setup without having to set it all up manually.

If you want to export/import this all in, you can do so with the <> button. You need to be in edit mode to see and use this button.

After clicking the <> button, you will see two options: Gallery Template and ARM Template. You can click the download button to save a copy. If you want to use an existing template you previously downloaded, you can copy and paste that here and click Apply:

If you copy my workbook, you will have the same setup I have at that point:

I’m still trying to figure out a way to make a chart clickable – without a separate parameter/query section below the chart. I’ll post again if I find a way to do that!

The post Azure Workbooks appeared first on sqlkitty.