Jul 09 2020
04:50 AM
- last edited on
Apr 08 2022
10:32 AM
by
TechCommunityAP
Jul 09 2020
04:50 AM
- last edited on
Apr 08 2022
10:32 AM
by
TechCommunityAP
Hi Team, Can anyone help me to generate the report using Log Analytics workspace in azure backup
Jul 09 2020 07:23 AM
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
Jul 10 2020 07:15 AM
Jul 10 2020 08:59 AM
Solution
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
Jul 12 2020 08:02 AM
@CliveWatson Thanks Dear, I really appreciate your response.
is there any possibility to send the failed backup report to email.
Jul 13 2020 01:28 AM
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)
Jul 13 2020 08:59 AM
@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
Jul 15 2020 01:25 AM
@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 )
Jul 15 2020 01:53 AM
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?
Jul 20 2020 12:43 AM
@CliveWatson thank you Sir, but really I'm not sure how to do that
Jul 24 2020 11:00 PM
@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?
Aug 06 2020 12:58 AM
@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.
Aug 17 2020 11:58 AM
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
Aug 18 2020 01:48 AM
@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
Aug 18 2020 05:01 AM
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
Aug 19 2020 07:21 AM
@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
Oct 10 2020 09:37 AM
@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
Oct 11 2020 11:49 PM - edited Oct 11 2020 11:52 PM
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
Oct 19 2020 08:36 AM
@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.
Oct 19 2020 09:58 AM
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?
Jul 10 2020 08:59 AM
Solution
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