Forum Discussion

Lucas Chies's avatar
Lucas Chies
Copper Contributor
Jul 20, 2020

Query for Azure Backup

Hello All,

 

I'm using the below script to get the backup information, but in this script I can't found were or how can I get the error message, error code and the recommendations message.

Can anyone help me on this? 🙂

 

 

let Events = AzureDiagnostics

| where Category == "AzureBackupReport" ;

Events

| where OperationName == "Job" and JobOperation_s == "Backup"

| project ProtectedServerUniqueId_s, JobStatus_s, Resource , TimeGenerated , JobStartDateTime_s , JobDurationInSecs_s

| join kind=inner

(

Events

| where OperationName == "ProtectedServer"

| where ProtectedServerFriendlyName_s != ""

| distinct ProtectedServerUniqueId_s, ProtectedServerFriendlyName_s

| project ProtectedServerUniqueId_s, ProtectedServerFriendlyName_s

)

on ProtectedServerUniqueId_s

| project ProtectedServerFriendlyName_s, JobStatus_s, Resource, TimeGenerated , JobStartDateTime_s , JobDurationInSecs_s

| extend Vault= Resource

| summarize count() by ProtectedServerFriendlyName_s, JobStatus_s, Vault, TimeGenerated , JobStartDateTime_s , JobDurationInSecs_s

 

  • AddonAzureBackupJobs
    | where JobOperation=="Backup"
    | summarize arg_max(TimeGenerated,*) by JobUniqueId
    | where JobStatus=="Completed"
    | join kind=inner
    (
    CoreAzureBackup
    | where OperationName == "BackupItem"
    | where BackupItemType=="VM" and BackupManagementType=="IaaSVM"
    | distinct BackupItemUniqueId, BackupItemFriendlyName
    )
    on BackupItemUniqueId

    I hope this code helps you out.
  • Lewis-H's avatar
    Lewis-H
    Iron Contributor
    AddonAzureBackupJobs
    | where JobOperation=="Backup"
    | summarize arg_max(TimeGenerated,*) by JobUniqueId
    | where JobStatus=="Completed"
    | join kind=inner
    (
    CoreAzureBackup
    | where OperationName == "BackupItem"
    | where BackupItemType=="VM" and BackupManagementType=="IaaSVM"
    | distinct BackupItemUniqueId, BackupItemFriendlyName
    )
    on BackupItemUniqueId

    I hope this code helps you out.
    • Blacktivity's avatar
      Blacktivity
      Copper Contributor

      Lewis-H 

      Hi, I used your query and it works fine. Can you please add lines to display database backups (SQL in Azure VM)

       

       

       

       

    • GBADARO's avatar
      GBADARO
      Copper Contributor

      Lewis-H 

      Tried the same, but neither Error Message nor Recommended action are returned by this query. 

      • GBADARO's avatar
        GBADARO
        Copper Contributor
        Hi Everyone,
        just to make sure, this thread should be re-opened until a working solution is achieved, right?
  • Lucas Chies 

     

    If you remove the filters, like this, do you see the Columns of data you need?  I don't see names like Error message or error code but they may have other names?  If you find the right data you can add them to the Project and Distinct lines

    let Events = AzureDiagnostics
    | where Category == "AzureBackupReport" ;
    Events
    | where OperationName == "Job" and JobOperation_s == "Backup"
    //| project ProtectedServerUniqueId_s, JobStatus_s, Resource , TimeGenerated , JobStartDateTime_s , JobDurationInSecs_s
    | join kind=inner
    (
    Events
    | where OperationName == "ProtectedServer"
    | where ProtectedServerFriendlyName_s != ""
    //| distinct ProtectedServerUniqueId_s, ProtectedServerFriendlyName_s
    //| project ProtectedServerUniqueId_s, ProtectedServerFriendlyName_s
    )
    on ProtectedServerUniqueId_s
    //| project ProtectedServerFriendlyName_s, JobStatus_s, Resource, TimeGenerated , JobStartDateTime_s , JobDurationInSecs_s
    | extend Vault= Resource
    

     

    • Lucas Chies's avatar
      Lucas Chies
      Copper Contributor

      Hello CliveWatson 

       

      Tks for your help.

      So, all scripts doesn't show the recommendation. I'm trying to find this message on the below screen:

      I'm trying use the "RecommendedAction_s", but doesn't show any information.

      let Events = AzureDiagnostics 
      | where Category == "AzureBackupReport"; Events 
      | extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "") 
      | where OperationName == "Job" and JobOperation_s == "Backup" and JobStatus_s == "Failed" and JobOperationSubType_s != "Log" and JobOperationSubType_s != "Recovery point_Log"
      | distinct JobUniqueId_g, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s, RecommendedAction_s
      | project BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s, RecommendedAction_s

       

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft

        Lucas Chies 

        .

        That is an "Alert" operation so you need to add that. ie. 

        | where OperationName == "Alert"

        Amended query:
        let Events = AzureDiagnostics 
        | where Category == "AzureBackupReport";
         Events 
        | extend JobOperationSubType_s = columnifexists("JobOperationSubType_s", "") 
        | where OperationName == "Alert" or  OperationName == "Job" 
                and JobOperation_s == "Backup" and JobStatus_s == "Failed"
                and JobOperationSubType_s !in ("Log","Recovery point_Log")
        | distinct JobUniqueId_g, BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s, RecommendedAction_s
        | project BackupItemUniqueId_s, JobStatus_s, Resource, JobFailureCode_s, JobStartDateTime_s, RecommendedAction_s

          

Resources