Forum Discussion

Kalaimani's avatar
Kalaimani
Brass Contributor
Jul 09, 2020
Solved

Azure Backup Report

Hi Team,  Can anyone help me to generate the report using Log Analytics workspace in azure backup
azure monitor
Query Language
  • CliveWatson's avatar
    CliveWatson
    Jul 10, 2020

    Kalaimani 

     

    I think you need to add the filter later:

     

    let Events = AzureDiagnostics | where Category == "AzureBackupReport"; 
Events
| where OperationName == "Job"
| where TimeGenerated > ago(30d)
| project BackupItemUniqueId_s , JobStatus_s , JobStartDateTime_s
| join kind=inner 
(
    Events
    | where OperationName == "BackupItem"
    | distinct BackupItemUniqueId_s, BackupItemFriendlyName_s
    | project BackupItemUniqueId_s, BackupItemFriendlyName_s 
) on BackupItemUniqueId_s
| where BackupItemFriendlyName_s in ("CH-DCVM10","FAKEVM0001","FAKE1111")
| summarize count() by  BackupItemFriendlyName_s, JobStatus_s, JobStartDateTime_s


     Go to Log Analytics and run query

CliveWatson's avatar
CliveWatson
Former Employee
Oct 19, 2020

Kalaimani 

 

We can answer #2 with this script, which uses HeartBeat data to get the OS info 

let Events = AzureDiagnostics
//| project Computer, HostName, OperatingSystemFullName
| 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)
// join to Heartbeat to get some OS details 
| join (Heartbeat | project Resource, OSType, FQDN=Computer, ComputerIP, OSMajorVersion, OSMinorVersion ) on $left.BackupItemFriendlyName_s == $right.Resource
| summarize count() by BackupItemFriendlyName_s, JobStatus_s, Vault, BackupItemUniqueId_s, JobFailureCode_s, NewDateTime=dt, JobStartDateTime_s, HostName =Resource1, FQDN

 

 

for #1

 

1. As you have exceed a hard limit of 10,000 are you able to accept a solution that shows maybe the report in two groups (Servers A-M, N-Z) etc ? 

2. Could we show the report filtered by Country, Region or ResourceGroup or some other divider to get us below 10k?

3. Are all 15k servers are backed up each time - is that right?

Kalaimani's avatar
Kalaimani
Brass Contributor
Aug 11, 2021

CliveWatson Dear Sir,  Recently we have changed the Destination table from Azure diagnostics to Resource specific, now we cannot get the result Resource specific. We need to join the Azure diagnostics and Resource specific in a single report. Kindly help with this.

 

 
below is one working for AzureDiagnostics, not Resource specific
 
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 

Resources