Veeam Backup for M365 Automatic Reporting in PowerBI

Those of you which has read through the Microsoft services agreement might have noticed paragraph 6b where Microsoft recommends that you regularly backup your content and data that you store on the services using third-party apps and services. One example of such third party tool popular by managed service providers is the Veeam Backup for Microsoft 365. This blog post will explain how you can get automatic reporting on licenses and sizes used by this application.

Please note: This is not a sponsored post!

Data Deletion

Data deletion can occur when an attacker deletes your data, usually in a way that makes recovery difficult, if not impossible. A variant of this type of attack includes ransomware. With ransomware, an attacker compromises the network, encrypts data, and then demands a payment to get the key to decrypt the data. This may equate to data deletion since a successful extraction of payment often leads to more targeting by the attacker. Attacker motivations for data deletion covering the tracks of an attack, attempting to do irreparable harm to your business, or simply trying to spite you or your employees

Preventing data deletion

Other than the protection mechanisms you should employ to prevent account breach an elevation of privileges, your core prevention strategy should be to ensure you have sufficient redundancies built into your data management processes to minimize the impact of data deletion. Data in Microsoft 365 is made redundant for maximum availability by the service. However, it's still possible for an attacker to delete data from SharePoint sites and recycle bins, making it almost impossible to recover. There is also examples of bugs where data has been deleted from Teams and Sharepoint. Therefore, it's critical that you have a process for backing up mission critical data to offline stores - just like the Microsoft Services Agreement states.

Veeam Backup for Microsoft Office 365

Veeam Backup for Microsoft 365 is one application which can help eliminate the risk of losing access and control over your Office 365 data, including Exchange Online, SharePoint Online, OneDrive for Business and Microsoft Teams. This product is often used by managed service providers offering their services to customers. One challenge will be to automate a reporting solution showing the usage of the service related to license and storage on repositories.

Report automation

Niels Engelen has described a way to automatically send reports from Veeam by email. This is a simple approach to the standard functionality where PDF report will be sent by e-mail. It just didn't fit my expectations for reporting. 

PowerShell data harvesting

I have studied the Veeam Backup for Microsoft 365 PowerShell Reference and made a script counting all licenses, data usage and repository usage on a daily basis. This data is prepared in a JSON format and uploaded to an Azure Cosmos Database. The Azure Cosmos Database is quite inexpensive for this kind of usage. 

The following query will list all licensed users in a JSON format before uploading each record to the Cosmos database.

# Get VBO Licensed users, convert to JSON and upload to CosmosDB

$CosmosDBCollectionID = 'VeeamBackupLicenses'

$LicensedUser = Get-VBOLicensedUser

$output = foreach ($user in $LicensedUser) {

    $LastBackupDate = (($user.LastBackupDate).toString()).Split(" ")[0]

    $id = $([Guid]::NewGuid().ToString())

    $doc = [pscustomobject]@{

        id               = $id

        Username         = $user.UserName

        LastBackupDate   = $LastBackupDate

        Year             = (($LastBackupDate).toString()).Split(".")[2]

        Month            = (($LastBackupDate).toString()).Split(".")[1]

        LicenseState     = $user.LicenseState

        OrganizationName = $user.OrganizationName

    }

    $document = $doc | ConvertTo-json | Out-String

    # Writing data to CosmosDB

    New-CosmosDbDocument -Context $cosmosDbContext -CollectionId $CosmosDBCollectionID -DocumentBody $document -PartitionKey $id -Encoding UTF-8

}

The next query will get the usage pr. organization and upload this to a CosmosDB in JSON format:

# Get VBO Usage pr Organization, convert to JSON and upload to CosmosDB

$CosmosDBCollectionID = 'VeeamBackupUsage'

$Organizations = Get-VBOOrganization

$Date = get-date -Format "dd.MM.yyyy"

$UsageOutput = foreach ($Org in $Organizations) {

    $UsageData = Get-VBOUsageData -Organization $Org

    # Need to handle the fact that a customer can have data in several repositories

    foreach ($Usage in $UsageData) {

        $id = $([Guid]::NewGuid().ToString())

        $UsedSpaceGb = [MATH]::Round((($Usage.UsedSpace) / 1024 / 1024 / 1024), 1)

        $Udoc = [pscustomobject]@{

            id               = $id

            Date             = $Date

            RepositoryId     = $Usage.RepositoryId

            UsedSpaceB       = $Usage.UsedSpace

            UsedSpaceGB      = $UsedSpaceGb

            OrganizationName = $Usage.Organization.DisplayName

            OrganizationMSID = ($Usage.Organization.Id.Value).Split(":")[0]

        }

        $Udocument = $Udoc | ConvertTo-json | Out-String

        # Writing data to CosmosDB

        New-CosmosDbDocument -Context $cosmosDbContext -CollectionId $CosmosDBCollectionID -DocumentBody $Udocument -PartitionKey $id -Encoding UTF-8

    }

}

The third query will get information about the repositories defined in Veeam Backup for Microsoft 365 and upload this in JSON format to the Cosmos Database. The original data values from the queries are in bytes format.

# Get VBO Repositories, convert to JSON and upload to CosmosDB

$CosmosDBCollectionID = 'VeeamBackupRepositories'

$Repositories = Get-VBORepository

$Date = get-date -Format "dd.MM.yyyy"

$RepositoryOutput = foreach ($Repo in $Repositories) {

    $id = $([Guid]::NewGuid().ToString())

    $RepoCapacityTb = [MATH]::Round((($Repo.Capacity) / 1024 / 1024 / 1024 / 1024), 1)

    $RepoFreeSpaceTb = [MATH]::Round((($Repo.FreeSpace) / 1024 / 1024 / 1024 / 1024), 1)

    $Rdoc = [pscustomobject]@{

        id                    = $id

        Date                  = $Date

        RepositoryId          = $Repo.Id.Guid

        RepoName              = $Repo.Name

        RepoPath              = $Repo.Path

        RepoCapacityB         = $Repo.Capacity

        RepoCapacityTB        = $RepoCapacityTb

        RepoFreeSpaceB        = $Repo.FreeSpace

        RepoFreeSpaceTB       = $RepoFreeSpaceTb

        RepoRetentionType     = $Repo.RetentionType

        RepoRetentionPeriod   = $Repo.RetentionPeriod

        RepoRetentionFreqType = $Repo.RetentionFrequencyType

    }

    $Rdocument = $Rdoc | ConvertTo-json | Out-String

    # Writing data to CosmosDB

    New-CosmosDbDocument -Context $cosmosDbContext -CollectionId $CosmosDBCollectionID -DocumentBody $Rdocument -PartitionKey $id -Encoding UTF-8

}

These different Powershell parts are coordinated and scheduled to run as powershell scripts on a regular basis on the Veeam backup servers.

  <Actions Context="Author">

    <Exec>

      <Command>C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe</Command>

      <Arguments>-ExecutionPolicy bypass -file "C:\Simon\CountVeeam365LicensesDailyToPowerBI.ps1"</Arguments>

    </Exec>

  </Actions>

PowerBI data analyzis

Using PowerBI Desktop, it is easy to connect to the Cosmos Database. With the data loaded into Microsoft PowerBI you can do further manipulations of the data using DAX queries. One example could be to calculate the difference between capacity and free space for the repositories in order to get the used space pr. repository. This could be done like this which will return a separate column with the result ready to use in the report:

RepoUsedSpaceB = CALCULATE(SUM(VeeamRepositories[RepoCapacityB]))-CALCULATE(SUM(VeeamRepositories[RepoFreeSpaceB]))

I have also made a calculation of consumed GB pr user in each company. This is done in two steps. First I calculate number of users pr. company:

AntallBrukere = DISTINCTCOUNT(VeeamLisenser[Bruker])

Then I calculate consumed GB pr user in the company:

GBprBruker = SUM(VeeamUsage[UsedSpaceGB])/Kalkulasjonstabell[AntallBrukere]

Using Power BI we can easily create several reports to visualize the status of the service.

Example of monthly report of all companies and users protected by Veeam 365 Backup which can be the basis for invoicing where this is based on the number of users in the system pr. company.

Example of historical development of backup up users pr. company by Veeam 365 backup.

Example of gigabyte compared to number of users pr. company protected by Veeam 365 backup.

Example of usage of the calculated column for GB pr User. Infinity comes from stored data for customers which have terminated their contract where data still exists. This has been removed from the graph with a visuals filter displaying only companies with more than 0 users.

Example of report for repositories with forecast in the Veeam 365 backup service.

This gives a fully automated always up to date reporting solution showing current usage and historical development related to the provided service, license usage and storage consumptions. The reports can easily be filtered by clicking on the values and graphs giving the consumer of the reports the ability to select the desired view. 

The animation is blured to protect the data exposed in the report

If you upload your PowerBI report to the online PowerBI service, you can set the dataset to automatically update directly from the Cosmos Database. This will allow for online consumption of the report from all your devices. One idea could be to add it as a tab in a suitable team channel in Microsoft Teams giving easy access for everyone interested in the topic.

I do believe someone could have interest in the PowerBI Report file, but unfortunately this can't be shared because my reports contains PII data. 

Conclusion

I hope this could inspire someone to dive into data capturing and report building. If you have thoughts, ideas, comments or ideas after reading this far, please add a comment.