Log Analytics data model for Azure Backup data

This article describes the Log Analytics data model for Azure Backup that's added to the Azure Diagnostics table (if your vaults are configured with diagnostics settings to send data to a Log Analytics workspace in Azure Diagnostics mode). You can use this data model to write queries on Log Analytics data to create custom alerts or reporting dashboards.

In this article, you'll learn about:

Use the Azure Backup data model

You can use the following fields provided as part of the data model to create visuals, custom queries, and dashboard according to your requirements.

Alert

The following table provides details about alert related fields.

BackupItem

The following table provides details about the backup item-related fields.

BackupItemAssociation

The following table provides details about backup item associations with various entities.

BackupManagementServer

The following table provides details about backup item associations with various entities.

Job

The following table provides details about job-related fields.

Policy

The following table provides details about policy-related fields.

PolicyAssociation

The following table provides details about policy associations with various entities.

Protected Container

The following table provides basic fields about Protected Containers. (Was ProtectedServer in v1)

Storage

The following table provides details about storage-related fields.

StorageAssociation

The following table provides basic storage-related fields connecting storage to other entities.

Vault

The following table provides details about vault-related fields.

Backup Management Server

The following table provides basic fields about Backup Management Servers.

PreferredWorkloadOnVolume

The following table specifies the workload(s) a Volume is associated with.

ProtectedInstance

The following table provides basic protected instances-related fields.

RecoveryPoint

The following table provides basic recovery point related fields.

Sample Kusto queries

Below are a few samples to help you write queries on Azure Backup data that resides in the Azure Diagnostics table:

• All successful backup jobs

  AzureDiagnostics
  | where Category == "AzureBackupReport"
  | where SchemaVersion_s == "V2"
  | where OperationName == "Job" and JobOperation_s == "Backup"
  | where JobStatus_s == "Completed"
  

• All failed backup jobs

  AzureDiagnostics
  | where Category == "AzureBackupReport"
  | where SchemaVersion_s == "V2"
  | where OperationName == "Job" and JobOperation_s == "Backup"
  | where JobStatus_s == "Failed"
  

• All successful Azure VM backup jobs

  AzureDiagnostics
  | where Category == "AzureBackupReport"
  | where SchemaVersion_s == "V2"
  | extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "")
  | where OperationName == "Job" and JobOperation_s == "Backup" and JobStatus_s == "Completed" and JobOperationSubType_s != "Log" and JobOperationSubType_s != "Recovery point_Log"
  | join kind=inner
  (
      AzureDiagnostics
      | where Category == "AzureBackupReport"
      | where OperationName == "BackupItem"
      | where SchemaVersion_s == "V2"
      | where BackupItemType_s == "VM" and BackupManagementType_s == "IaaSVM"
      | distinct BackupItemUniqueId_s, BackupItemFriendlyName_s
      | project BackupItemUniqueId_s , BackupItemFriendlyName_s
  )
  on BackupItemUniqueId_s
  | extend Vault= Resource
  | project-away Resource
  

• All successful SQL log backup jobs

  AzureDiagnostics
  | where Category == "AzureBackupReport"
  | where SchemaVersion_s == "V2"
  | extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "")
  | where OperationName == "Job" and JobOperation_s == "Backup" and JobStatus_s == "Completed" and JobOperationSubType_s == "Log"
  | join kind=inner
  (
      AzureDiagnostics
      | where Category == "AzureBackupReport"
      | where OperationName == "BackupItem"
      | where SchemaVersion_s == "V2"
      | where BackupItemType_s == "SQLDataBase" and BackupManagementType_s == "AzureWorkload"
      | distinct BackupItemUniqueId_s, BackupItemFriendlyName_s
      | project BackupItemUniqueId_s , BackupItemFriendlyName_s
  )
  on BackupItemUniqueId_s
  | extend Vault= Resource
  | project-away Resource
  

• All successful Azure Backup agent jobs

  AzureDiagnostics
  | where Category == "AzureBackupReport"
  | where SchemaVersion_s == "V2"
  | extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "")
  | where OperationName == "Job" and JobOperation_s == "Backup" and JobStatus_s == "Completed" and JobOperationSubType_s != "Log" and JobOperationSubType_s != "Recovery point_Log"
  | join kind=inner
  (
      AzureDiagnostics
      | where Category == "AzureBackupReport"
      | where OperationName == "BackupItem"
      | where SchemaVersion_s == "V2"
      | where BackupItemType_s == "FileFolder" and BackupManagementType_s == "MAB"
      | distinct BackupItemUniqueId_s, BackupItemFriendlyName_s
      | project BackupItemUniqueId_s , BackupItemFriendlyName_s
  )
  on BackupItemUniqueId_s
  | extend Vault= Resource
  | project-away Resource
  

V1 schema vs V2 schema

Earlier, diagnostics data for Azure Backup Agent and Azure VM backup was sent to Azure Diagnostics table in a schema referred to as V1 schema. Subsequently, new columns were added to support other scenarios and workloads, and diagnostics data was pushed in a new schema referred to as V2 schema.

For the backward-compatibility reasons, diagnostics data for Azure Backup agent and Azure VM backup is currently sent to Azure Diagnostics table in both V1 and V2 schema (with V1 schema now on a deprecation path). You can identify which records in Log Analytics are of V1 schema by filtering records for SchemaVersion_s=="V1" in your log queries.

See the third column 'Description' in the data model described above to identify which columns belong to V1 schema only.

Modify the queries to use the V2 schema

As the V1 schema is on a deprecation path, we recommend you to use only the V2 schema in all your custom queries on Azure Backup diagnostic data.

To update your queries to remove dependency on V1 schema, follow these steps:

1. Identify if your query is using any field that's only applicable to the V1 schema. Assume you have a query to list all the backup items and their associated protected servers as follows:

   AzureDiagnostics
   | where Category=="AzureBackupReport"
   | where OperationName=="BackupItemAssociation"
   | distinct BackupItemUniqueId_s, ProtectedServerUniqueId_s
   

   The above query uses the field ProtectedServerUniqueId_s, which is only applicable to V1 schema. The V2 schema equivalent of this field is ProtectedContainerUniqueId_s (refer tables above). The field BackupItemUniqueId_s is applicable even to V2 schema and the same field can be used in this query.

2. Update the query to use the V2 schema field names. It's a recommended practice to use the filter where SchemaVersion_s=="V2" in all your queries, so that only records corresponding to V2 schema are parsed by the query:

   AzureDiagnostics
   | where Category=="AzureBackupReport"
   | where OperationName=="BackupItemAssociation"
   | where SchemaVersion_s=="V2"
   | distinct BackupItemUniqueId_s, ProtectedContainerUniqueId_s
   

Next step

After the data model review is complete, start creating custom queries in Azure Monitor logs to build your own dashboard.