Forum Discussion

azurenewb1984's avatar
azurenewb1984
Copper Contributor
Aug 08, 2019

Help with script to generate monthly backup report using Azure monitor

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 Replies

  • Kalaimani's avatar
    Kalaimani
    Brass Contributor
    Jul 10, 2020

    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

  • Benoit_Paquin's avatar
    Benoit_Paquin
    Copper Contributor
    Sep 26, 2019

    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 

Resources