Help with script to generate monthly backup report using Azure monitor

[azurenewb1984]

Hi Team 

I need help in generating a backup report using Azure monitor. 

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

The report does not provide me the output that I want.

a) It listS backups for last 30 days, whereas I need backup of last month i.e 1st day of the month to last day of the month 12:00 AM -12:00 AM.

b) 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.

c) Also is there a easier way to get the result saved in Excel format for furthre reporting to see number of backups completed successfully or failed for each client last month

Any help is appreciated

[#2]

Hi @azurenewb1984 

I can help with a) by using the make_datetime() function:

let dt = datetime_add("month",-1,now()); 
let year= datetime_part("year",dt);
let month = datetime_part("month",dt);
let startmonth = make_datetime(year,month,1,0,0,0);
let endmonth = datetime_add("month",1,startmonth);
print year,month,startmonth,endmonth

Benoit 

[#3]

@azurenewb1984 Have you get the script, I'm also looking for the same  

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