SOLVED

Azure Backup Report

Brass Contributor

Hi Team,  Can anyone help me to generate the report using Log Analytics workspace in azure backup

22 Replies

@Kalaimani 

 

Azure Backup example queries are here: https://docs.microsoft.com/en-us/azure/backup/backup-azure-monitoring-use-azuremonitor

Enable the logging here:
https://docs.microsoft.com/en-us/azure/backup/backup-azure-diagnostic-events

 

Then use the first links to create the reports you need
Thanks 

sorry it not working... but below one its working

let Events = AzureDiagnostics | where Category == "AzureBackupReport"; Events
| where OperationName == "Job"
| where TimeGenerated > ago(30d)
| where * has "VT-PRD-CA02"
| 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
| project BackupItemFriendlyName_s , JobStatus_s, JobStartDateTime_s

a) Need it for multiple servers
where * has "VT-PRD-CA02" provides me the result for one.
I tried various combinations eg | where BackupItemFriendlyName_s in ("Client1","Client2") etc but that is not giving me any result.



b) need failed and completed status for single report for one server
best response confirmed by Kalaimani (Brass Contributor)
Solution

@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 Thanks Dear, I really appreciate your response. 

 

is there any possibility to send the failed backup report to email.  

@Kalaimani 

 

For this I'd typically suggest a Playbook to automate the report.  Here I show an example of a report that runs at the sane time each week.  You could also do a similar task, but creating a Azure Monitor Alert using your query and sending that, when the job finishes. https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-overview

 

 

Step1 – establish a schedule/recurrence (this is a weekly example, runs on Friday)

step1 .jpg

Step2 – run the KQL  (I'm running two separate queries and sending to two different emails - so you can ignore the right-hand parallel branch)

Step 2.jpg

Step 3, email the chart (this will be a JPG)

 

step3.jpg

@Kalaimani - adding my reply back to the thread (from a private) so its useful to others.  This change displays a column "NewDateTime" which is in the UTC format

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

 

@CliveWatson thank you So much Sir, you certainly deserve a Great Thanks for your excellent support. 

 

I have 4 Log Analytics Servers, is there any option can get the report in a single shot (like one scrip adding all 4 servers )

@Kalaimani 

 

The "run query and visualise results" control in the Logic App (playbook) has the workspace as an entry, so you can run 4 parallel jobs to get the data from each workspace?

@CliveWatson thank you Sir, but really I'm not sure how to do that 

@CliveWatson  Dear Sir, I have around 500 subscriptions but I am looking for only 150 subscriptions to generate the report. if it's possible. can you help me with this? 

@CliveWatson Hi Sir, I would like to get the OS version also(like its windows server 2008 R2), can you help me with this, please.

@Kalaimani

 

For Q1, you could add a list of 150 subs (see line 1 and last line)?

let include_subs = dynamic(["5733bcb3-7fde-4caf-8629-nnnnn","12345","678910"]);
let Events = AzureDiagnostics 
| where Category == "AzureBackupReport"; 
Events 
| where SubscriptionId in (include_subs)

For Q2, what data sources do you have that contain this info?


This tells you the table name(s), you would then have to confirm the data and then JOIN the computer name from the report to this table.

 

search "Windows Server"
| summarize count() by Type

@CliveWatson  Thank you, Sir, The Q2 getting the computer name(Os Name) in the report, it's not working for me.

 

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
| project BackupItemFriendlyName_s, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s
| extend Vault= Resource
| extend dt = todatetime(JobStartDateTime_s)

search "Windows Server"
| summarize count() by BackupItemFriendlyName_s, JobStatus_s, Vault, BackupItemUniqueId_s, JobFailureCode_s, NewDateTime=dt, JobStartDateTime_s, type

 

@Kalaimani 

 

This was to run on it's own, to discover if you have the data in any of your tables

 

 

search "Windows Server"
| summarize count() by Type

 

 

When I ran the above (slightly modified) I see I have the data in VMCONNECT 

 

search "Windows Server 2008"
| where TimeGenerated > ago(7d)
| summarize count() by Type
| order by count_ desc

 

 

 

VMComputer
| summarize by  OperatingSystemFullName 

 

 

I can use this to JOIN with, if you don't have data that has the OS version / Name this wont work. 


 

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
| project BackupItemFriendlyName_s, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s
| extend Vault= Resource
| extend dt = todatetime(JobStartDateTime_s)
// this is a sample - and only works if you have this Table!
| join 
  VMComputer
  on $left.BackupItemFriendlyName_s == $right.HostName
| project Computer, HostName, OperatingSystemFullName  

 

 

@CliveWatson Hi Sir, its failed with the bellow error, 

 

'join' operator: Failed to resolve table or column expression named 'VMComputer' If issue persists, please open a support ticket. Request id: 1ba0f22c-512e-409d-8112-4ae0afe11ef8

 

we are using Azure Backup Monitoring Solution. is there any way to get the computer name 

@Kalaimani - Dear Clive Watson  - we are able to get up to 10000 results only. can you help me to get the complete result? 

 

this the warring getting while running the query 

 

Showing the first 10,000 results. Learn more on how to narrow down the result set.
 

@Kalaimani 

 

10k is the query limit (and while its possible to get 500k with the api); most people will want a short report of the data - I doubt you'll want to read 10k rows?

 

You could swap

 

 

| project Computer, HostName, OperatingSystemFullName  

 

to something like this, the important part is the summarize which allows you to group your returned values.  So rather than getting many rows for each "BackupFriendlyName_s" they are grouped together,  I used count() so you can see how many are as well.  You may need to experiment to find a method that works for you. 

| summarize count() by BackupItemFriendlyName_s, JobFailureCode_s, Resource, JobStatus_s

@CliveWatson  Dear Sir,

Yes, we have more than 15000 servers in the cloud. those all need to be generated in a single backup report. so need to get more than 10000. we are using the below query to get the result 

 

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

| 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

 

below warring, we are getting while running the above query.

 

Completed. Showing partial results from the last 24 hours.

00:06.0

10,000+ records

Showing the first 10,000 results. Learn more on how to narrow down the result set.

=============================================================

 
Also, we are trying the get the computer name, hostname name, and OS version but not able to get it Please help with this. 

 

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)

| summarize count() by BackupItemFriendlyName_s, JobStatus_s, Vault, BackupItemUniqueId_s, JobFailureCode_s, NewDateTime=dt, JobStartDateTime_s, HostName, OperatingSystemFullName

 

 

'project' operator: Failed to resolve scalar expression named 'HostName' If issue persists, please open a support ticket. Request id: 43b5c644-ee20-470a-a741-dcc74036e6af

 

 the below error getting when I try to get the Computer, HostName, OperatingSystemFullName (OS version )

 

this is the most needed for me. Please help me with this.

 

 

 

 

 

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

1 best response

Accepted Solutions
best response confirmed by Kalaimani (Brass Contributor)
Solution

@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

View solution in original post