System functions on Azure Monitor Logs

This article describes how to write custom queries on Azure Monitor Logs using system functions.

Azure Backup provides a set of functions, called system functions or solution functions that are available by default in your Log Analytics (LA) workspaces.

These functions operate on data in the raw Azure Backup tables in LA and return formatted data that helps you easily retrieve information of all your backup-related entities, using simple queries. Users can pass parameters to these functions to filter the data that is returned by these functions.

We recommend you to use system functions for querying your backup data in LA workspaces for creating custom reports, as they provide a number of benefits, as detailed in the section below.

Benefits of using system functions

• Simpler queries: Using functions helps you reduce the number of joins needed in your queries. By default, the functions return ‘flattened’ schemas that incorporate all information pertaining to the entity (backup instance, job, vault, and so on) being queried. For example, if you need to get a list of successful backup jobs by backup item name and its associated container, a simple call to the _AzureBackup_getJobs() function will give you all of this information for each job. On the other hand, querying the raw tables directly would require you to perform multiple joins between AddonAzureBackupJobs and CoreAzureBackup tables.

• Smoother transition from the legacy diagnostics event: Using system functions helps you transition smoothly from the legacy diagnostics event (AzureBackupReport in AzureDiagnostics mode) to the resource-specific events. All the system functions provided by Azure Backup allows you to specify a parameter that lets you choose whether the function should query data only from the resource-specific tables, or query data from both the legacy table and the resource-specific tables (with deduplication of records).

  • If you have successfully migrated to the resource-specific tables, you can choose to exclude the legacy table from being queried by the function.
  • If you're currently in the process of migration and have some data in the legacy tables which you require for analysis, you can choose to include the legacy table. When the transition is complete, and you no longer need data from the legacy table, you can update the value of the parameter passed to the function in your queries, to exclude the legacy table.
  • If you're still using only the legacy table, the functions will still work if you choose to include the legacy table via the same parameter. However, it's recommended to switch to the resource-specific tables at the earliest.

• Reduces possibility of custom queries breaking: If Azure Backup introduces improvements to the schema of the underlying LA tables to accommodate future reporting scenarios, the definition of the functions will also be updated to take into account the schema changes. Thus, if you use system functions for creating custom queries, your queries won't break, even if there are changes in the underlying schema of the tables.

Types of system functions offered by Azure Backup

• Core functions: These are functions that help you query any of the key Azure Backup entities, such as Backup Instances, Vaults, Policies, Jobs and Billing Entities. For example, the _AzureBackup_getBackupInstances function returns a list of all the backup instances that exist in your environment as of the latest completed day (in UTC). The parameters and returned schema for each of these core functions are summarized below in this article.

• Trend functions: These are functions that return historical records for your backup-related entities (for example, backup instances, billing groups) and allow you to get daily, weekly and monthly trend information on key metrics (for example, Count, Storage consumed) pertaining to these entities. The parameters and returned schema for each of these trend functions are summarized below in this article.

List of system functions

Core Functions

_AzureBackup_GetVaults()

This function returns the list of all Recovery Services vaults in your Azure environment that are associated with the LA workspace.

Parameters

Returned Fields

_AzureBackup_GetPolicies()

This function returns the list of backup policies that are being used in your Azure environment along with detailed information about each policy such as the datasource type, storage replication type, and so on.

Parameters

Returned Fields

_AzureBackup_GetJobs()

This function returns a list of all backup and restore related jobs that were triggered in a specified time range, along with detailed information about each job, such as job status, job duration, data transferred, and so on.

Parameters

Returned Fields

_AzureBackup_GetBackupInstances()

This function returns the list of backup instances that are associated with your Recovery Services vaults, along with detailed information about each backup instance, such as cloud storage consumption, associated policy, and so on.

Parameters

Returned Fields

_AzureBackup_GetBillingGroups()

This function returns a list of all backup-related billing entities (billing groups) along with information on key billing components such as frontend size and total cloud storage.

Parameters

Returned Fields

Trend Functions

_AzureBackup_GetBackupInstancesTrends()

This function returns historical records for each backup instance, allowing you to view key daily, weekly and monthly trends related to the backup instance count and storage consumption, at multiple levels of granularity.

Parameters

Returned Fields

_AzureBackup_GetBillingGroupsTrends()

This function returns historical records for each billing entity, allowing you to view key daily, weekly and monthly trends related to frontend size and storage consumption, at multiple levels of granularity.

Parameters

Returned Fields

Sample Queries

Below are some sample queries to help you get started with using system functions.

• All failed Azure VM backup jobs in a given time range

  _AzureBackup_GetJobs("2021-03-05", "2021-03-06") //call function with RangeStart and RangeEnd parameters set, and other parameters with default value
  | where BackupSolution=="Azure Virtual Machine Backup" and Status=="Failed"
  | project BackupInstanceFriendlyName, BackupInstanceId, OperationCategory, Status,  JobStartDateTime=StartTime, JobDuration=DurationInSecs/3600, ErrorTitle, DataTransferred=DataTransferredInMBs
  

• All SQL log backup jobs in a given time range

  _AzureBackup_GetJobs("2021-03-05", "2021-03-06","*","*","*","*",true,"*","*","*","*","*","*",false) //call function with RangeStart and RangeEnd parameters set, ExcludeLog parameter as false, and other parameters with default value
  | where BackupSolution=="SQL in Azure VM Backup" and Operation=="Log"
  | project BackupInstanceFriendlyName, BackupInstanceId, OperationCategory, Status,  JobStartDateTime=StartTime, JobDuration=DurationInSecs/3600, ErrorTitle, DataTransferred=DataTransferredInMBs
  

• Weekly trend of backup storage consumed for VM "testvm"

  _AzureBackup_GetBackupInstancesTrends("2021-01-01", "2021-03-06","*","*","*","*",false,"*","*","*","*",true, "Weekly") //call function with RangeStart and RangeEnd parameters set, AggregationType parameter as Weekly, and other parameters with default value
  | where BackupSolution == "Azure Virtual Machine Backup"
  | where FriendlyName == "testvm"
  | project TimeGenerated, VaultStore_StorageConsumptionInMBs
  | render timechart 
  

Next steps

Learn more about Backup Reports