Azure Backup Report

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.

Kalaimani 

So for #1 we could add a test for Backups that start with A-M or N thru Z).  I'm assuming there will be a range of names, you may need to spilt further A-E, F-M etc...?  You can then run these as parallel queries in your Logic App.

let Events = AzureDiagnostics
| 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
// add name filter (starts with A thru M)
| where BackupItemFriendlyName_s matches regex @"\A([A-Ma-m])"
//| where BackupItemFriendlyName_s matches regex @"\A([N-Zn-z])"
| 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, Vault, BackupItemUniqueId_s, JobFailureCode_s, NewDateTime=dt, JobStartDateTime_s //, HostName =Resource1, FQDN

 For #2, is that because you don't have a Heartbeat table? In a previous reply I asked you which Tables you do have, Heartbeat and VMconnection contain the data you are after, but you don't seem to have them, AzureDiagnostics doesn't have this detail so we do need to correlate with another source, there are 100s of tables, so you need to tell me which ones you have that have OS info.  If you do have Heartbeat maybe I've joined on the wrong value?


I do think we are replicating much of what is available in the UI https://docs.microsoft.com/en-gb/azure/backup/configure-reports  or could be done as a Workbook, if the primary goal to have a schedule report still?  If not users can go to the UI or Workbooks anytime to see the status?

CliveWatson  Dear Sir,  Really thank you for helping me with this.

 #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 ? 

Yes, if it's possible. I have not tried it and not sure 

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

Yes, if it's possible. I have not tried it and not sure 

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

Yes, all servers are backing up at a different time but within 24 hours.

#2

Heartbeat not working for me.

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?