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

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

Kalaimani

Brass Contributor

Oct 20, 2020

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

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

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

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.

Heartbeat not working for me.

CliveWatson

Former Employee

Oct 20, 2020

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?

Forum Discussion

Azure Backup Report