Forum Discussion
Azure Backup Report
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
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)
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 3, email the chart (this will be a 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 Hi Sir, I would like to get the OS version also(like its windows server 2008 R2), can you help me with this, please.
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 TypeCliveWatson 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
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 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 )
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
