Need to join the Azure diagnostics and Resource specific

[Kalaimani]

Dear Team,

I am looking for getting the result of both tables (Azure diagnostics and Resource specific) in a single query. 

we have configured with both options in the log analytics workspace server

I have a query about Azure diagnostics.

1. To get failed backup job

let Events = AzureDiagnostics   
| where Category == "AzureBackupReport"; Events     
| extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "")    
| where OperationName == "Job" and JobOperation_s == "Backup" and JobStatus_s == "Failed" and JobOperationSubType_s != "Log" and JobOperationSubType_s != "Recovery point_Log"  
| distinct JobUniqueId_g, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s 
| project BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s 
| join kind=leftouter ( Events  
| where OperationName == "BackupItem"   
| distinct BackupItemUniqueId_s, BackupItemFriendlyName_s   
| project BackupItemUniqueId_s , BackupItemFriendlyName_s ) on BackupItemUniqueId_s     
| project BackupItemFriendlyName_s, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s   
| extend Vault= Resource    
| extend dt = todatetime(JobStartDateTime_s)    
| summarize count() by BackupItemFriendlyName_s, JobStatus_s,JobFailureCode_s, Vault, BackupItemUniqueId_s, NewDateTime=dt, JobStartDateTime_s 

2 Backup History for Selected VM

let Events = AzureDiagnostics
| where TimeGenerated > ago(30d)
| where Category == "AzureBackupReport"; Events
| extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "")
| where OperationName == "Job" and JobOperation_s == "Backup" and JobOperationSubType_s != "Log" and JobOperationSubType_s != "Recovery point_Log"
| distinct JobUniqueId_g, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s
| project BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s
| join kind=leftouter ( Events
| where OperationName == "BackupItem"
| distinct BackupItemUniqueId_s, BackupItemFriendlyName_s
| project BackupItemUniqueId_s , BackupItemFriendlyName_s ) on BackupItemUniqueId_s
| project BackupItemFriendlyName_s, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s
| extend Vault= Resource
| extend dt = todatetime(JobStartDateTime_s)
| where BackupItemFriendlyName_s in ("GZ-xxxxxxx","GX-xxxxxxxxx")
| summarize count() by BackupItemFriendlyName_s, JobStatus_s,JobFailureCode_s, Vault, NewDateTime=dt, JobStartDateTime_s

3 Restore History for Selected VM

let Events = AzureDiagnostics
| where TimeGenerated > ago(300d)
| where Category == "AzureBackupReport"; Events
| extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "")
| where OperationName == "Job"
| where JobOperation_s == "Restore" or JobOperation_s == "Recovery"
| distinct JobUniqueId_g, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s , JobOperation_s
| project BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s, JobOperation_s
| join kind=leftouter ( Events
| where OperationName == "BackupItem"
| distinct BackupItemUniqueId_s, BackupItemFriendlyName_s
| project BackupItemUniqueId_s , BackupItemFriendlyName_s ) on BackupItemUniqueId_s
| project BackupItemFriendlyName_s, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s , JobOperation_s
| extend Vault= Resource
| extend dt = todatetime(JobStartDateTime_s)
| where BackupItemFriendlyName_s in ("GZ-xxxxxxxxx","GX-xxxxxxxxxx")
| summarize count() by BackupItemFriendlyName_s, JobStatus_s,JobFailureCode_s, Vault, NewDateTime=dt, JobStartDateTime_s ,JobOperation_s

[#2]

Hi,
Have you looked into using workbooks to visualize this information? Even if it is possible to get all this data in one query result, I think it would make more sense to build a workbook that you can use as a backup report.

[#3]

@Anders Bengtsson yes, we are using log analytics workspace. It not sure how to build a single query. Can you help with this, please? 

[#4]

Hi,
Before we start writing new queries, I want to make sure you have looked at Backup Explorer.

If you go into your Backup Vault, there is a link to Backup Explorer. It is in early status but might be what you are looking for. For more information see https://docs.microsoft.com/en-us/azure/backup/monitor-azure-backup-with-backup-explorer#:~:text=Back....

[#5]

Dear Anders Bengtsson,

Yes, I have checked the Backup Explorer. But we have 2000 Recovery Services vault and 4 log analytics workspaces. So we want to get in one short using the query. Currently using above one but due to recent changes, we need a modification. Kindly help on those, please.

[#6]

Hi,
At https://docs.microsoft.com/en-us/azure/backup/backup-azure-monitoring-use-azuremonitor#sample-kusto-... you can see example queries for working with backup logs. Make sure you have configured your backup vaults to send logs to the Log Analytics workspace.

If you have multiple log analytic workspaces you can do a cross-workspace-query to see data from all workspaces. https://docs.microsoft.com/en-us/azure/azure-monitor/logs/cross-workspace-query.

[#7]

@Anders Bengtsson -I have gone through the page already. I was looking for the custom one, which I was updated initially on top. Kindly someone help with this.