Automate Azure Backup Reports export

[Daniel Piedra]

Hi Community experts, this customer has enabled Azure Backup Reports and would like to generate and automated backup report in a weekly or monthly basis. The documentation states that it is possible to download the reports to Excel manually https://docs.microsoft.com/en-us/azure/backup/configure-reports#export-to-excel and the customer would like to explore for automated ways to export the contents of the backup report widget as an Excel sheet as-is with existing filters applied and send it via email. Can we use Logic Apps to generate the flow? What other options could be used here? Is there maybe a runbook for automation we could use?

[#2 · Accepted solution]

@Daniel PiedraHi Daniel, it is certainly possible to achieve what you are looking for. First, consider this base query for the task,
"

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

"
You can get all fancy with the above, the next step is to ensure that you funnel all your logs to a WP analytics. 3rd setup the logic app in a manner that it periodically sends the reports to the designated individuals.

The logic app should have a timed trigger. Things you'll need is a mailbox account.

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Run_query_and_visualize_results": {
                "inputs": {
                    "body": "AddonAzureBackupJobs \n| where JobOperation==\"Backup\" \n| summarize arg_max(TimeGenerated,*) by JobUniqueId \n| where JobStatus==\"Completed\" \n| join kind=inner ( CoreAzureBackup \n| where OperationName == \"BackupItem\" \n| where BackupItemType==\"VM\" and BackupManagementType==\"IaaSVM\" \n| distinct BackupItemUniqueId, BackupItemFriendlyName ) on BackupItemUniqueId",
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azuremonitorlogs']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/visualizeQuery",
                    "queries": {
                        "resourcegroups": "DemoEastUS2",
                        "resourcename": "wvdlogwp",
                        "resourcetype": "Log Analytics Workspace",
                        "subscriptions": "This is your subscription",
                        "timerange": "Last 7 days",
                        "visType": "Html Table"
                    }
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Send_an_email_(V2)": {
                "inputs": {
                    "body": {
                        "Attachments": [
                            {
                                "ContentBytes": "@{body('Run_query_and_visualize_results')?['attachmentContent']}",
                                "Name": "@body('Run_query_and_visualize_results')?['attachmentName']"
                            }
                        ],
                        "Body": "<p>Demo backups have completed, refer to the attachement.</p>",
                        "Subject": "This is a demo.",
                        "To": "dummyMail@dummydomain.com"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/Mail"
                },
                "runAfter": {
                    "Run_query_and_visualize_results": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "Recurrence": {
                "recurrence": {
                    "frequency": "Week",
                    "interval": 5,
                    "schedule": {
                        "hours": [
                            "3",
                            "4"
                        ],
                        "weekDays": [
                            "Monday"
                        ]
                    }
                },
                "type": "Recurrence"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azuremonitorlogs": {
                    "connectionId": "/subscriptions/This is your subscriptionID/resourceGroups/DemoEastUS2/providers/Microsoft.Web/connections/azuremonitorlogs",
                    "connectionName": "azuremonitorlogs",
                    "id": "/subscriptions/This is your subscriptionID/providers/Microsoft.Web/locations/eastus2/managedApis/azuremonitorlogs"
                },
                "office365": {
                    "connectionId": "/subscriptions/This is your subscriptionID/resourceGroups/DemoEastUS2/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/This is your subscriptionID/providers/Microsoft.Web/locations/eastus2/managedApis/office365"
                }
            }
        }
    }
}

 In a nutshell, this logic app queries the data from your WP every Monday morning between 4-5 AM, packages the output into an HTML file, and emails to the respective recipients all the backup results.

[#3]

You can easily send backup reports from Backup Center.
Under „Monitoring + reporting“ select „Backup reports“.
Select the subscriptions and Workspaces. Set your filters if required.
And then simply follow the instructions described in following Microsoft documentation:

https://docs.microsoft.com/en-us/azure/backup/backup-reports-email

That’s it.

[#4]

Hi @ralfkrause The real challenge is to retain the rich visualization when downloaded or emailed as Excel file all the look great feature on Wookbook get lost. I'm trying to build something which retains that look to max. extent as possible and also finding pre-published workbook which let us retain rich visualization and brakdown of report as seen exactly on Azure Backup Center.