SOLVED

Automate Azure Backup Reports export

%3CLINGO-SUB%20id%3D%22lingo-sub-1919818%22%20slang%3D%22en-US%22%3EAutomate%20Azure%20Backup%20Reports%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919818%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Community%20experts%2C%20this%20customer%20has%20enabled%20Azure%20Backup%20Reports%20and%20would%20like%20to%20generate%20and%20automated%20backup%20report%20in%20a%20weekly%20or%20monthly%20basis.%20The%20documentation%20states%20that%20it%20is%20possible%20to%20download%20the%20reports%20to%20Excel%20manually%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fbackup%2Fconfigure-reports%23export-to-excel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fbackup%2Fconfigure-reports%23export-to-excel%3C%2FA%3E%20and%20the%20customer%20would%20like%20to%20explore%20for%20automated%20ways%20to%3CSPAN%3E%26nbsp%3Bexport%20the%20contents%20of%20the%20backup%20report%20widget%20as%20an%20Excel%20sheet%20as-is%20with%20existing%20filters%20applied%26nbsp%3B%3C%2FSPAN%3Eand%20send%20it%20via%20email.%20Can%20we%20use%20Logic%20Apps%20to%20generate%20the%20flow%3F%20What%20other%20options%20could%20be%20used%20here%3F%20Is%20there%20maybe%20a%20runbook%20for%20automation%20we%20could%20use%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1919818%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Backup%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUpdates%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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?

 

 

1 Reply
best response confirmed by Daniel Piedra (Microsoft)
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.