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!
Table of Contents
Data Loss
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
Data can also be lost in several other ways. It doesn’t need to be caused by evil attackers. It can also be caused by accidents or damage from insiders, such as employees, contractors, or partners, who have access to an organization’s sensitive information. Accidental deletion or modification of data by users can result in data loss which can be hard to detect.
Preventing Data Loss
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:WindowsSystem32WindowsPowerShellv1.0powershell.exe</Command>
<Arguments>-ExecutionPolicy bypass -file "C:SimonCountVeeam365LicensesDailyToPowerBI.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.
This is an 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.
Her is an example of historical development of backup users pr. company by Veeam 365 backup.
This is an example of usage of gigabyte compared to number of users pr. company protected by Veeam 365 backup.
The following example shows usage of the calculated column pr 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.
Another report example
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.
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.
Be First to Comment