Greetings friends, I have been receiving this request really often over the last few months. At the moment, we cannot perform this level of granularity on any Veeam ONE Alarm, or Veeam ONE Report. We can surely report on missing RPO for individual workloads, which is nice, but not at a job level and cascade from there.
Why is this important?
When you are in your day-2 operations, managing hundreds if not thousands of jobs, one failure, or two are not that important. What is more important is if a job has been failing for three or even seven consecutive times, if your jobs are daily that means more than a week without a possibility to go back, certainly want to know those while I take a sip of my morning beverage.
Stay with me to get this fantastic report only when you guys really need it:
Introducing Veeam ONE Stored Procedures
Over a year ago now, in January 2024 I demonstrated the power that Veeam ONE has beyond API, or official reports. Veeam ONE runs all the reports from stored procedures, and knowing how to run them adhoc is what it make this community project possible, anyways in case you forgot, or if you want to tingle around:
This remains unsupported and use it at your own risk, etc.
Building blocks and logic
I have used PowerShell to simplify the backup administrator life, the final flow looks something like this:
- We should download the script, and review it and do our due diligence – https://github.com/jorgedlcruz/veeam-html-reporting/blob/main/veeam-one/VONE_Failed_Jobs_Last_X_Sessions.ps1
- Change the parameters to match our Veeam ONE Microsoft SQL Server
- Change the parameters to adjust how many days to look back, and how many consecutive failure sessions within those days
- Create a Microsoft Azure Application capable of sending email, get a secret, etc.
- Make sure we have the module MSPAL.PS
Let’s go step by step with all the pre-requirements.
How to check current Veeam ONE Microsoft SQL Server
From Veeam ONE Client, let’s open the main menu on the top left, down to Server Settings:
Now let’s navigate all the way to Other > Launch Support utility:
Finally, here we can see the Microsoft SQL Server instance and the database name:
We need to introduce this on the script at the top. Also note the authentication, as it uses Windows Authentication. The script is meant to be used or trigger with windows authentication as well. Meaning if you run it on a machine that is on domain, by an user that has access to that SQL Server, we will be just fine.
Microsoft Azure Application to send Mails
This is the evolution of the older SMTP Servers where we just spammed some user/pass and there you go. Now it is a bit more complex, but for those familiarised with Microsoft Azure a piece of cake. Anyways, let’s make sure we have an App Registration with the next permissions:
If we do, or when we do. Let’s create some secret for it:
We will need the Client ID as well, that we can find under Overview of the App Registration:
Save this secret value and add it to the script as well.
Making sure we have MSPAL.PS
To send emails, and basically to authenticate in Microsoft Azure services, we will need MSPAL.PS it is the standard, all the information can be found here. Installing it is just one line as an admin of the machine:
Install-Module -Name MSAL.PS -Force -AllowClobber
Putting it all together, and running it
Alright, if we have followed all nicely, the last step is just to fill the script parameters:
# Global Parameters (Edit here) $SQLServer = "YOURSQL\INSTANCE" $SQLDBName = "VeeamONE" $reportIntervalDays = 3 $requiredFailureCount = 3 $RecipientEmail = "[email protected]" $TenantId = "YOURTENANT.onmicrosoft.com" $ClientId = "YOURCLIENTIDAZURE" $ClientSecret = ConvertTo-SecureString "YOURAPPREGISTRATIONSECRET" -AsPlainText -Force
If we are all happy and ready, let’s get some colours! The script has a lot of console debug for those who will be watching while running, but that is not the main use-case I guess:
The output part is not complex, just saves an HTML on the path where the script is running, and it gets send by email, how does it look like? GORGEOUS!
How does it look like by email? Well, just an email with an HTML attached 🙂
Next steps
The next steps would be to fine-tune the script, to perhaps scan over the last 3 days and if 3 consecutive fails, or seven days 3 consecutive fails, etc.
Also we can fine tune the HTML that is embedded into the Mail notification, or the colours in the tables, add our own logo? etc.
You said Veeam ONE has alarms based in missing RPO? Tell me more
For those interested on the native Veeam ONE Alarms based on RPO:
- VM with no backup
- VM Backup RPO (oVirt KVM, Proxmox VE, Nutanix AHV)
- Computer with no backup
- Cloud Instance RPO
- Application with no recent data backup sessions
Thank you so much for reading this whole article, please give it a try and let me know.
Leave a Reply