System Functions for Azure Backup
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.
Note
System functions are maintained by Microsoft and their definitions cannot be edited by users. If you require editable functions, you can create saved functions in LA.
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.
Note
Currently, system functions return data for up to the last completed day (in UTC). Data for the current partial day isn't returned. So if you are looking to retrieve records for the current day, you'll need to use the raw LA tables.
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
| Parameter Name | Description | Required? | Example value | Data type |
|---|---|---|---|---|
| RangeStart | Use this parameter along with RangeEnd parameter only if you need to fetch all vault-related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each vault. | N | "2021-03-03 00:00:00" | DateTime |
| RangeEnd | Use this parameter along with RangeStart parameter only if you need to fetch all vault-related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each vault. | N | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those vaults that are in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those vaults that are in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve records pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for records across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
Returned Fields
| Field Name | Description | Data type |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the vault | String |
| Id | Azure Resource Manager (ARM) ID of the vault | String |
| Name | Name of the vault | String |
| SubscriptionId | ID of the subscription in which the vault exists | String |
| Location | Location in which the vault exists | String |
| VaultStore_StorageReplicationType | Storage Replication Type associated with the vault | String |
| Tags | Tags of the vault | String |
| TimeGenerated | Timestamp of the record | DateTime |
| Type | Type of the vault, for example, "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
_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
| Parameter Name | Description | Required? | Example value | Data type |
|---|---|---|---|---|
| RangeStart | Use this parameter along with the RangeStart parameter only if you need to fetch all policy-related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each policy. | N | "2021-03-03 00:00:00" | DateTime |
| RangeEnd | Use this parameter along with RangeStart parameter only if you need to fetch all policy-related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each policy. | N | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those policies that are in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those policies that are in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve records of policies pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for records of policies across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
| BackupSolutionList | Use this parameter to filter the output of the function for a certain set of backup solutions used in your Azure environment. For example, if you specify Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM as the value of this parameter, the function only returns records that are related to items backed up using Azure Virtual Machine backup, SQL in Azure VM backup or DPM to Azure backup. By default, the value of this parameter is '*', which makes the function return records pertaining to all backup solutions that are supported by Backup Reports (supported values are "Azure Virtual Machine Backup", "SQL in Azure VM Backup", "SAP HANA in Azure VM Backup", "Azure Storage (Azure Files) Backup", "Azure Backup Agent", "DPM", "Azure Backup Server", "Azure Database for PostgreSQL Server Backup", "Azure Blob Backup", "Azure Disk Backup" or a comma-separated combination of any of these values). |
N | Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM,Azure Backup Agent |
String |
Returned Fields
| Field Name | Description | **Data type ** |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the policy | String |
| Id | Azure Resource Manager (ARM) ID of the policy | String |
| Name | Name of the policy | String |
| TimeZone | Timezone in which the policy is defined | String |
| Backup Solution | Backup Solution that the policy is associated with. For example, Azure VM Backup, SQL in Azure VM Backup, and so on. | String |
| TimeGenerated | Timestamp of the record | Datetime |
| VaultUniqueId | Foreign key that refers to the vault associated with the policy | String |
| VaultResourceId | Azure Resource Manager (ARM) ID of the vault associated with the policy | String |
| VaultName | Name of the vault associated with the policy | String |
| VaultTags | Tags of the vault associated with the policy | String |
| VaultLocation | Location of the vault associated with the policy | String |
| VaultSubscriptionId | Subscription ID of the vault associated with the policy | String |
| VaultStore_StorageReplicationType | Storage Replication Type of the vault associated with the policy | String |
| VaultType | Type of the vault, for example, "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
| ExtendedProperties | Additional properties of the policy | Dynamic |
_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
| Parameter Name | Description | Required? | Example value | **Data type ** |
|---|---|---|---|---|
| RangeStart | Use this parameter along with RangeEnd parameter to retrieve the list of all jobs that started in the time period from RangeStart to RangeEnd. | Y | "2021-03-03 00:00:00" | DateTime |
| RangeEnd | Use this parameter along with RangeStart parameter to retrieve the list of all jobs that started in the time period from RangeStart to RangeEnd. | Y | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those jobs that are associated with vaults in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those jobs that are associated with vaults in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve jobs pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for jobs across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
| BackupSolutionList | Use this parameter to filter the output of the function for a certain set of backup solutions used in your Azure environment. For example, if you specify Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM as the value of this parameter, the function only returns records that are related to items backed up using Azure Virtual Machine backup, SQL in Azure VM backup or DPM to Azure backup. By default, the value of this parameter is '*', which makes the function return records pertaining to all backup solutions that are supported by Backup Reports (supported values are "Azure Virtual Machine Backup", "SQL in Azure VM Backup", "SAP HANA in Azure VM Backup", "Azure Storage (Azure Files) Backup", "Azure Backup Agent", "DPM", "Azure Backup Server", "Azure Database for PostgreSQL Server Backup", "Azure Blob Backup", "Azure Disk Backup" or a comma-separated combination of any of these values). |
N | Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM,Azure Backup Agent |
String |
| JobOperationList | Use this parameter to filter the output of the function for a specific type of job. For example, the backup or restore operations. By default, the value of this parameter is "*", which makes the function search for both Backup and Restore jobs. | N | "Backup" | String |
| JobStatusList | Use this parameter to filter the output of the function for a specific job status. For example, Completed, Failed, and so on. By default, the value of this parameter is "*", which makes the function search for all jobs irrespective of status. | N | Failed,CompletedWithWarnings |
String |
| JobFailureCodeList | Use this parameter to filter the output of the function for a specific failure code. By default, the value of this parameter is "*", which makes the function search for all jobs irrespective of failure code. | N | "Success" | String |
| DatasourceSetName | Use this parameter to filter the output of the function to a particular parent resource. For example, to return SQL in Azure VM backup instances belonging to the virtual machine "testvm", specify testvm as the value of this parameter. By default, the value is "*", which makes the function search for records across all backup instances. | N | "testvm" | String |
| BackupInstanceName | Use this parameter to search for jobs on a particular backup instance by name. By default, the value is "*", which makes the function search for records across all backup instances. | N | "testvm" | String |
| ExcludeLog | Use this parameter to exclude log jobs from being returned by the function (helps in query performance). By default, the value of this parameter is true, which makes the function exclude log jobs. | N | true | Boolean |
Returned Fields
| Field Name | Description | **Data type ** |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the job | String |
| OperationCategory | Category of the operation being performed. For example, Backup, Restore | String |
| Operation | Details of the operation being performed. For example, Log (for log backup) | String |
| Status | Status of the job. For example, Completed, Failed, CompletedWithWarnings | String |
| ErrorTitle | Failure code of the job | String |
| StartTime | Date and time at which the job started | DateTime |
| DurationInSecs | Duration of the job in seconds | Double |
| DataTransferredInMBs | Data transferred by the job in MBs. Currently, this field is only supported for Recovery Services vault workloads | Double |
| RestoreJobRPDateTime | The date and time when the recovery point that's being recovered was created. Currently, this field is only supported for Recovery Services vault workloads | DateTime |
| RestoreJobRPLocation | The location where the recovery point that's being recovered was stored | String |
| BackupInstanceUniqueId | Foreign key that refers to the backup instance associated with the job | String |
| BackupInstanceId | Azure Resource Manager (ARM) ID of the backup instance associated with the job | String |
| BackupInstanceFriendlyName | Name of the backup instance associated with the job | String |
| DatasourceResourceId | Azure Resource Manager (ARM) ID of the underlying datasource associated with the job. For example, Azure Resource Manager (ARM) ID of the VM | String |
| DatasourceFriendlyName | Friendly name of the underlying datasource associated with the job | String |
| DatasourceType | Type of the datasource associated with the job. For example "Microsoft.Compute/virtualMachines" | String |
| BackupSolution | Backup Solution that the job is associated with. For example, Azure VM Backup, SQL in Azure VM Backup, and so on. | String |
| DatasourceSetResourceId | Azure Resource Manager (ARM) ID of the parent resource of the datasource (wherever applicable). For example, for a SQL in Azure VM datasource, this field will contain the Azure Resource Manager (ARM) ID of the VM in which the SQL Database exists | String |
| DatasourceSetType | Type of the parent resource of the datasource (wherever applicable). For example, for an SAP HANA in Azure VM datasource, this field will be Microsoft.Compute/virtualMachines since the parent resource is an Azure VM | String |
| VaultResourceId | Azure Resource Manager (ARM) ID of the vault associated with the job | String |
| VaultUniqueId | Foreign key that refers to the vault associated with the job | String |
| VaultName | Name of the vault associated with the job | String |
| VaultTags | Tags of the vault associated with the job | String |
| VaultSubscriptionId | Subscription ID of the vault associated with the job | String |
| VaultLocation | Location of the vault associated with the job | String |
| VaultStore_StorageReplicationType | Storage Replication Type of the vault associated with the job | String |
| VaultType | Type of the vault, for example, "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
| TimeGenerated | Timestamp of the record | DateTime |
_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
| Parameter Name | Description | Required? | Example value | **Data type ** |
|---|---|---|---|---|
| RangeStart | Use this parameter along with RangeEnd parameter only if you need to fetch all backup instance-related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each backup instance. | N | "2021-03-03 00:00:00" | DataTime |
| RangeEnd | Use this parameter along with RangeStart parameter only if you need to fetch all backup instance-related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each backup instance. | N | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those backup instances that are in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those backup instances that are in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve records of backup instances pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for records of backup instances across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
| BackupSolutionList | Use this parameter to filter the output of the function for a certain set of backup solutions used in your Azure environment. For example, if you specify Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM as the value of this parameter, the function only returns records that are related to items backed up using Azure Virtual Machine backup, SQL in Azure VM backup or DPM to Azure backup. By default, the value of this parameter is '*', which makes the function return records pertaining to all backup solutions that are supported by Backup Reports (supported values are "Azure Virtual Machine Backup", "SQL in Azure VM Backup", "SAP HANA in Azure VM Backup", "Azure Storage (Azure Files) Backup", "Azure Backup Agent", "DPM", "Azure Backup Server", "Azure Database for PostgreSQL Server Backup", "Azure Blob Backup", "Azure Disk Backup" or a comma-separated combination of any of these values). |
N | Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM,Azure Backup Agent |
String |
| ProtectionInfoList | Use this parameter to choose whether to include only those backup instances that are actively protected, or to also include those instances for which protection has been stopped and instances for which initial backup is pending. For Recovery services vault workloads, supported values are "Protected", "ProtectionStopped", "InitialBackupPending" or a comma-separated combination of any of these values. For Backup vault workloads, supported values are "Protected", "ConfiguringProtection", "ConfiguringProtectionFailed", "UpdatingProtection", "ProtectionError", "ProtectionStopped" or a comma-separated combination of any of these values. By default, the value is "*", which makes the function search for all backup instances irrespective of protection details. | N | "Protected" | String |
| DatasourceSetName | Use this parameter to filter the output of the function to a particular parent resource. For example, to return SQL in Azure VM backup instances belonging to the virtual machine "testvm", specify testvm as the value of this parameter. By default, the value is "*", which makes the function search for records across all backup instances. | N | "testvm" | String |
| BackupInstanceName | Use this parameter to search for a particular backup instance by name. By default, the value is "*", which makes the function search for all backup instances. | N | "testvm" | String |
| DisplayAllFields | Use this parameter to choose whether to retrieve only a subset of the fields returned by the function. If the value of this parameter is false, the function eliminates storage and retention point related information from the output of the function. This is useful if you're using this function as an intermediate step in a larger query and need to optimize the performance of the query by eliminating columns which you don't require for analysis. By default, the value of this parameter is true, which makes the function return all fields pertaining to the backup instance. | N | true | Boolean |
Returned Fields
| Field Name | Description | Data type |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the backup instance | String |
| Id | Azure Resource Manager (ARM) ID of the backup instance | String |
| FriendlyName | Friendly name of the backup instance | String |
| ProtectionInfo | Information about the protection settings of the backup instance. For example, protection is configured, protection stopped, initial backup pending | String |
| LatestRecoveryPoint | Date and time of the latest recovery point associated with the backup instance. Currently, this field is only supported for Recovery Services vault workloads. | DateTime |
| OldestRecoveryPoint | Date and time of the oldest recovery point associated with the backup instance. Currently, this field is only supported for Recovery Services vault workloads. | DateTime |
| SourceSizeInMBs | Frontend size of the backup instance in MBs | Double |
| VaultStore_StorageConsumptionInMBs | Total cloud storage consumed by the backup instance in the vault-standard tier | Double |
| DataSourceFriendlyName | Friendly name of the datasource corresponding to the backup instance | String |
| BackupSolution | Backup Solution that the backup instance is associated with. For example, Azure VM Backup, SQL in Azure VM Backup, and so on. | String |
| DatasourceType | Type of the datasource corresponding to the backup instance. For example "Microsoft.Compute/virtualMachines" | String |
| DatasourceResourceId | Azure Resource Manager (ARM) ID of the underlying datasource corresponding to the backup instance. For example, Azure Resource Manager (ARM) ID of the VM | String |
| DatasourceSetFriendlyName | Friendly name of the parent resource of the datasource (wherever applicable). For example, for a SQL in Azure VM datasource, this field will contain the name of the VM in which the SQL Database exists | String |
| DatasourceSetFriendlyName | Friendly name of the parent resource of the datasource (wherever applicable). For example, for a SQL in Azure VM datasource, this field will contain the name of the VM in which the SQL Database exists | String |
| DatasourceSetResourceId | Azure Resource Manager (ARM) ID of the parent resource of the datasource (wherever applicable). For example, for a SQL in Azure VM datasource, this field will contain the Azure Resource Manager (ARM) ID of the VM in which the SQL Database exists | String |
| DatasourceSetType | Type of the parent resource of the datasource (wherever applicable). For example, for an SAP HANA in Azure VM datasource, this field will be Microsoft.Compute/virtualMachines since the parent resource is an Azure VM | String |
| PolicyName | Name of the policy associated with the backup instance | String |
| PolicyUniqueId | Foreign key that refers to the policy associated with the backup instance | String |
| PolicyId | Azure Resource Manager (ARM) ID of the policy associated with the backup instance | String |
| VaultResourceId | Azure Resource Manager (ARM) ID of the vault associated with the backup instance | String |
| VaultUniqueId | Foreign key which refers to the vault associated with the backup instance | String |
| VaultName | Name of the vault associated with the backup instance | String |
| VaultTags | Tags of the vault associated with the backup instance | String |
| VaultSubscriptionId | Subscription ID of the vault associated with the backup instance | String |
| VaultLocation | Location of the vault associated with the backup instance | String |
| VaultStore_StorageReplicationType | Storage Replication Type of the vault associated with the backup instance | String |
| VaultType | Type of the vault, which is "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
| TimeGenerated | Timestamp of the record | DateTime |
_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
| Parameter Name | Description | Required? | Example value | Date type |
|---|---|---|---|---|
| RangeStart | Use this parameter along with RangeEnd parameter only if you need to fetch all billing group related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each billing group. | N | "2021-03-03 00:00:00" | DateTime |
| RangeEnd | Use this parameter along with RangeStart parameter only if you need to fetch all billing group related records in the time period from RangeStart to RangeEnd. By default, the value of RangeStart and RangeEnd are null, which will make the function retrieve only the latest record for each billing group. | N | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those billing groups that are in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those billing groups that are in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve records of backup instances pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for records of billing groups across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
| BackupSolutionList | Use this parameter to filter the output of the function for a certain set of backup solutions used in your Azure environment. For example, if you specify Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM as the value of this parameter, the function only returns records that are related to items backed up using Azure Virtual Machine backup, SQL in Azure VM backup or DPM to Azure backup. By default, the value of this parameter is '*', which makes the function return records pertaining to all backup solutions that are supported by Backup Reports (supported values are "Azure Virtual Machine Backup", "SQL in Azure VM Backup", "SAP HANA in Azure VM Backup", "Azure Storage (Azure Files) Backup", "Azure Backup Agent", "DPM", "Azure Backup Server", "Azure Database for PostgreSQL Server Backup", "Azure Blob Backup", "Azure Disk Backup" or a comma-separated combination of any of these values). |
N | Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM,Azure Backup Agent |
String |
| BillingGroupName | Use this parameter to search for a particular billing group by name. By default, the value is "*", which makes the function search for all billing groups. | N | "testvm" | String |
Returned Fields
| Field Name | Description | Data type |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the billing group | String |
| FriendlyName | Friendly name of the billing group | String |
| Name | Name of the billing group | String |
| Type | Type of billing group. For example, ProtectedContainer or BackupItem | String |
| SourceSizeInMBs | Frontend size of the billing group in MBs | Double |
| VaultStore_StorageConsumptionInMBs | Total cloud storage consumed by the billing group in the vault-standard tier | Double |
| BackupSolution | Backup Solution that the billing group is associated with. For example, Azure VM Backup, SQL in Azure VM Backup, and so on. | String |
| VaultResourceId | Azure Resource Manager (ARM) ID of the vault associated with the billing group | String |
| VaultUniqueId | Foreign key which refers to the vault associated with the billing group | String |
| VaultName | Name of the vault associated with the billing group | String |
| VaultTags | Tags of the vault associated with the billing group | String |
| VaultSubscriptionId | Subscription ID of the vault associated with the billing group | String |
| VaultLocation | Location of the vault associated with the billing group | String |
| VaultStore_StorageReplicationType | Storage Replication Type of the vault associated with the billing group | String |
| VaultType | Type of the vault, for example, "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
| TimeGenerated | Timestamp of the record | DateTime |
| ExtendedProperties | Additional properties of the billing group | Dynamic |
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
| Parameter Name | Description | Required? | Example value | Data type |
|---|---|---|---|---|
| RangeStart | Use this parameter along with RangeEnd parameter to retrieve all backup instance related records in the time period from RangeStart to RangeEnd. | Y | "2021-03-03 00:00:00" | DateTime |
| RangeEnd | Use this parameter along with RangeStart parameter to retrieve all backup instance related records in the time period from RangeStart to RangeEnd. | Y | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those backup instances that are in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those backup instances that are in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve records of backup instances pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for records of backup instances across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
| BackupSolutionList | Use this parameter to filter the output of the function for a certain set of backup solutions used in your Azure environment. For example, if you specify Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM as the value of this parameter, the function only returns records that are related to items backed up using Azure Virtual Machine backup, SQL in Azure VM backup or DPM to Azure backup. By default, the value of this parameter is '*', which makes the function return records pertaining to all backup solutions that are supported by Backup Reports (supported values are "Azure Virtual Machine Backup", "SQL in Azure VM Backup", "SAP HANA in Azure VM Backup", "Azure Storage (Azure Files) Backup", "Azure Backup Agent", "DPM", "Azure Backup Server", "Azure Database for PostgreSQL Server Backup", "Azure Blob Backup", "Azure Disk Backup" or a comma-separated combination of any of these values). |
N | Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM,Azure Backup Agent |
String |
| ProtectionInfoList | Use this parameter to choose whether to include only those backup instances that are actively protected, or to also include those instances for which protection has been stopped and instances for which initial backup is pending. For Recovery services vault workloads, supported values are "Protected", "ProtectionStopped", "InitialBackupPending" or a comma-separated combination of any of these values. For Backup vault workloads, supported values are "Protected", "ConfiguringProtection", "ConfiguringProtectionFailed", "UpdatingProtection", "ProtectionError", "ProtectionStopped" or a comma-separated combination of any of these values. By default, the value is "*", which makes the function search for all backup instances irrespective of protection details. | N | "Protected" | String |
| DatasourceSetName | Use this parameter to filter the output of the function to a particular parent resource. For example, to return SQL in Azure VM backup instances belonging to the virtual machine "testvm", specify testvm as the value of this parameter. By default, the value is "*", which makes the function search for records across all backup instances. | N | "testvm" | String |
| BackupInstanceName | Use this parameter to search for a particular backup instance by name. By default, the value is "*", which makes the function search for all backup instances. | N | "testvm" | String |
| DisplayAllFields | Use this parameter to choose whether to retrieve only a subset of the fields returned by the function. If the value of this parameter is false, the function eliminates storage and retention point related information from the output of the function. This is useful if you're using this function as an intermediate step in a larger query and need to optimize the performance of the query by eliminating columns which you don't require for analysis. By default, the value of this parameter is true, which makes the function return all fields pertaining to the backup instance. | N | true | Boolean |
| AggregationType | Use this parameter to specify the time granularity at which data should be retrieved. If the value of this parameter is "Daily", the function returns a record per backup instance per day, allowing you to analyze daily trends of storage consumption and backup instance count. If the value of this parameter is "Weekly", the function returns a record per backup instance per week, allowing you to analyze weekly trends. Similarly, you can specify "Monthly" to analyze monthly trends. Default value is "Daily". If you're viewing data across larger time ranges, it's recommended to use "Weekly" or "Monthly" for better query performance and ease of trend analysis. | N | "Weekly" | String |
Returned Fields
| Field Name | Description | Data type |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the backup instance | String |
| Id | Azure Resource Manager (ARM) ID of the backup instance | String |
| FriendlyName | Friendly name of the backup instance | String |
| ProtectionInfo | Information about the protection settings of the backup instance. For example, protection is configured, protection stopped, initial backup pending | String |
| LatestRecoveryPoint | Date and time of the latest recovery point associated with the backup instance. Currently, this field is only supported for Recovery Services vault workloads | DateTime |
| OldestRecoveryPoint | Date and time of the oldest recovery point associated with the backup instance | Currently, this field is only supported for Recovery Services vault workloads |
| SourceSizeInMBs | Frontend size of the backup instance in MBs | Double |
| VaultStore_StorageConsumptionInMBs | Total cloud storage consumed by the backup instance in the vault-standard tier | Double |
| DataSourceFriendlyName | Friendly name of the datasource corresponding to the backup instance | String |
| BackupSolution | Backup Solution that the backup instance is associated with. For example, Azure VM Backup, SQL in Azure VM Backup, and so on. | String |
| DatasourceType | Type of the datasource corresponding to the backup instance. For example "Microsoft.Compute/virtualMachines" | String |
| DatasourceResourceId | Azure Resource Manager (ARM) ID of the underlying datasource corresponding to the backup instance. For example, Azure Resource Manager (ARM) ID of the VM | String |
| DatasourceSetFriendlyName | Friendly name of the parent resource of the datasource (wherever applicable). For example, for a SQL in Azure VM datasource, this field will contain the name of the VM in which the SQL Database exists | String |
| DatasourceSetResourceId | Azure Resource Manager (ARM) ID of the parent resource of the datasource (wherever applicable). For example, for a SQL in Azure VM datasource, this field will contain the Azure Resource Manager (ARM) ID of the VM in which the SQL Database exists | String |
| DatasourceSetType | Type of the parent resource of the datasource (wherever applicable). For example, for an SAP HANA in Azure VM datasource, this field will be Microsoft.Compute/virtualMachines since the parent resource is an Azure VM | String |
| PolicyName | Name of the policy associated with the backup instance | String |
| PolicyUniqueId | Foreign key that refers to the policy associated with the backup instance | String |
| PolicyId | Azure Resource Manager (ARM) ID of the policy associated with the backup instance | String |
| VaultResourceId | Azure Resource Manager (ARM) ID of the vault associated with the backup instance | String |
| VaultUniqueId | Foreign key which refers to the vault associated with the backup instance | String |
| VaultName | Name of the vault associated with the backup instance | String |
| VaultTags | Tags of the vault associated with the backup instance | String |
| VaultSubscriptionId | Subscription ID of the vault associated with the backup instance | String |
| VaultLocation | Location of the vault associated with the backup instance | String |
| VaultStore_StorageReplicationType | Storage Replication Type of the vault associated with the backup instance | String |
| VaultType | Type of the vault, for example, "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
| TimeGenerated | Timestamp of the record | DateTime |
_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
| Parameter Name | Description | Required? | Example value | Data type |
|---|---|---|---|---|
| RangeStart | Use this parameter along with RangeEnd parameter to retrieve all billing group related records in the time period from RangeStart to RangeEnd. | Y | "2021-03-03 00:00:00" | DateTime |
| RangeEnd | Use this parameter along with RangeStart parameter to retrieve all billing group related records in the time period from RangeStart to RangeEnd. | Y | "2021-03-10 00:00:00" | DateTime |
| VaultSubscriptionList | Use this parameter to filter the output of the function for a certain set of subscriptions where backup data exists. Specifying a comma-separated list of subscription IDs as a parameter to this function helps you retrieve only those billing groups that are in the specified subscriptions. By default, the value of this parameter is '*', which makes the function search for records across all subscriptions. | N | "00000000-0000-0000-0000-000000000000,11111111-1111-1111-1111-111111111111" | String |
| VaultLocationList | Use this parameter to filter the output of the function for a certain set of regions where backup data exists. Specifying a comma-separated list of regions as a parameter to this function helps you retrieve only those billing groups that are in the specified regions. By default, the value of this parameter is '*', which makes the function search for records across all regions. | N | eastus,westus |
String |
| VaultList | Use this parameter to filter the output of the function for a certain set of vaults. Specifying a comma-separated list of vault names as a parameter to this function helps you retrieve records of backup instances pertaining only to the specified vaults. By default, the value of this parameter is '*', which makes the function search for records of billing groups across all vaults. | N | vault1,vault2,vault3 |
String |
| VaultTypeList | Use this parameter to filter the output of the function to records pertaining to a particular vault type. By default, the value of this parameter is '*', which makes the function search for both Recovery Services vaults and Backup vaults. | N | "Microsoft.RecoveryServices/vaults" | String |
| ExcludeLegacyEvent | Use this parameter to choose whether to query data in the legacy AzureDiagnostics table or not. If the value of this parameter is false, the function queries data from both the AzureDiagnostics table and the Resource specific tables. If the value of this parameter is true, the function queries data from only the Resource specific tables. Default value is true. | N | true | Boolean |
| BackupSolutionList | Use this parameter to filter the output of the function for a certain set of backup solutions used in your Azure environment. For example, if you specify Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM as the value of this parameter, the function only returns records that are related to items backed up using Azure Virtual Machine backup, SQL in Azure VM backup or DPM to Azure backup. By default, the value of this parameter is '*', which makes the function return records pertaining to all backup solutions that are supported by Backup Reports (supported values are "Azure Virtual Machine Backup", "SQL in Azure VM Backup", "SAP HANA in Azure VM Backup", "Azure Storage (Azure Files) Backup", "Azure Backup Agent", "DPM", "Azure Backup Server", "Azure Database for PostgreSQL Server Backup", "Azure Blob Backup", "Azure Disk Backup" or a comma-separated combination of any of these values). |
N | Azure Virtual Machine Backup,SQL in Azure VM Backup,DPM,Azure Backup Agent |
String |
| BillingGroupName | Use this parameter to search for a particular billing group by name. By default, the value is "*", which makes the function search for all billing groups. | N | "testvm" | String |
| AggregationType | Use this parameter to specify the time granularity at which data should be retrieved. If the value of this parameter is "Daily", the function returns a record per billing group per day, allowing you to analyze daily trends of storage consumption and frontend size. If the value of this parameter is "Weekly", the function returns a record per backup instance per week, allowing you to analyze weekly trends. Similarly, you can specify "Monthly" to analyze monthly trends. Default value is "Daily". If you're viewing data across larger time ranges, it's recommended to use "Weekly" or "Monthly" for better query performance and ease of trend analysis. | N | "Weekly" | String |
Returned Fields
| Field Name | Description | Data type |
|---|---|---|
| UniqueId | Primary key denoting unique ID of the billing group | String |
| FriendlyName | Friendly name of the billing group | String |
| Name | Name of the billing group | String |
| Type | Type of billing group. For example, ProtectedContainer or BackupItem | String |
| SourceSizeInMBs | Frontend size of the billing group in MBs | Double |
| VaultStore_StorageConsumptionInMBs | Total cloud storage consumed by the billing group in the vault-standard tier | Double |
| BackupSolution | Backup Solution that the billing group is associated with. For example, Azure VM Backup, SQL in Azure VM Backup, and so on. | String |
| VaultResourceId | Azure Resource Manager (ARM) ID of the vault associated with the billing group | String |
| VaultUniqueId | Foreign key which refers to the vault associated with the billing group | String |
| VaultName | Name of the vault associated with the billing group | String |
| VaultTags | Tags of the vault associated with the billing group | String |
| VaultSubscriptionId | Subscription ID of the vault associated with the billing group | String |
| VaultLocation | Location of the vault associated with the billing group | String |
| VaultStore_StorageReplicationType | Storage Replication Type of the vault associated with the billing group | String |
| VaultType | Type of the vault, for example, "Microsoft.RecoveryServices/vaults" or "Microsoft.DataProtection/backupVaults" | String |
| TimeGenerated | Timestamp of the record | DateTime |
| ExtendedProperties | Additional properties of the billing group | Dynamic |
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=DataTransferredInMBsAll 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=DataTransferredInMBsWeekly 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