How to push events from Veeam Backup & Replication to instant messengers

Hello! In this post, I will talk about how to deliver information about the results of actions from Veeam Backup & Replication v9.5 to MS Teams. The method described in the post will work not only in this, but in any other messenger - you only need to organize the delivery of messages using the profile API.



To begin, imagine what we want to achieve. We want to receive information from one of our team channels about what is happening on one of our backup software instances. The function is useful, it is useful for both children and adults, it will please your soul mate and will serve as an excellent decoration of the festive table. In other words, you can come up with a specific use case yourself. I do not in any way want to limit your imagination - we consider the prototype.

We will solve the problem in the simplest possible way: rob data from the Veeam SQL database, parse a bit and send to the channel via the Incoming WebHook Connector. Here is an action plan:

1. We connect to the Veeam database and get information from [dbo]. [Backup.Model.JobSessions].
2. Create Incoming WebHook Connector in the channel where we want to send messages.
3. Putting it all together with the help of python magic to make it work.
4. Wrap the resulting sketch in a docker container.
5. It just works!

Connect to Veeam Backup & Replication DB

First, create a user on the side of the MS SQL instance. We consider MS SQL, since this is the default version, pre-installed by our software. We give the user the very minimum of rights - only select and only from one table.

Make it easy. Login to the instance in any way possible for you and book the following request:

CREATE LOGIN ms_teams_watcher WITH PASSWORD = '123@qwe' USE [VeeamBackup] CREATE USER ms_teams_watcher FOR LOGIN ms_teams_watcher GRANT SELECT ON [dbo].[Backup.Model.JobSessions] TO ms_teams_watcher 

Do not forget to substitute the name of the database , come up with a username and password.

If you did everything correctly, then an attempt to adapt ... will not work:



And here - please:



Glory to the GDPR - in this sample, no production base has suffered! Now we have user ms_teams_watcher and we can start collecting data. We will connect via pyodbc - no SQLAlchemy, only hardcore!

First, let's open the connection (see class SQLConnectorVeeamDB ) and get information about all the sessions that were completed during the last run of our script:

s. SQLConnectorVeeamDB.select_completed_job_sessions_during_latest_hour:

 query = 'select job_name,job_type, usn, end_time, result, reason ' \               'from [dbo].[Backup.Model.JobSessions] ' \               'where state = -1 and result != -1 and datediff(HH,[end_time],GETDATE()) <= 1 ' \               'order by usn' 

The most important thing for us is to get the last usn in the table, so that next time we can request it from him, and not by time. If you find nothing, execute the following query:

 query = 'select top 1 [usn] ' \                   'from [dbo].[Backup.Model.JobSessions] ' \                   'order by usn desc' 

Statistically, this usn will be higher than any that will return when the first request is executed, but we do not need JobSessions that do not fall into the first sample. If we did not find the completed (state = -1) sessions with the result not None (result! = -1) - just remember usn, write it in the ini file and repeat the requests after a certain interval (but already SQLConnectorVeeamDB.select_completed_job_sessions_after_usn).

Create Incoming WebHook Connector for MS Teams

Everything is simple here. Ask your MS Teams admin (Respect if it's you) to enable this feature, as described here . Now create a connector of type Incoming WebHook . Happened? You are beautiful, nothing else needs to be done, copy the url and save.

Putting it all together

Here you can download the ready-made Dockerfile, or the sketch itself, which we will consider further. Just do not forget to download the configuration.py_ , put it next to the Dockerfile, fill in and remove the underscore from the extension.

Here's what the conceptual scheme of the sketch looks like (the image is clickable):



As can be seen from the diagram, the most interesting thing is to parse the date obtained from the Veeam database, creating at the same time objects of the VeeamEvent class (just the date-handler) and the process of sending the notification itself.

class VeeamEvent (object)

In the table from which we take the date, all the tasks that Veeam performs are logged.
Each one has a job_type attribute. There are a lot of them, but I identified the most interesting ones for us - do not we want to send a message stating that someone opened the program console or finished the restorer?

• 0 - Backup job
  
• 1 - Replication job
  
• 3 - SureBackup job
  
• 24 - File to tape job
  
• 28 - Backup to tape job
  
• 51 - Backup Copy job
  
• 100 - Configuration Backup
  

Write in the comments, if interested, I will prompt other types of task on request. Completed tasks can be either success or warning, and sometimes even failed.

As a result, we create an object that stores in itself all this data + the name of the task, usn, a description of its completion status (if any) and the completion time. It is this object and is transmitted to send a message to the messenger.

send_notification_to_web_hook ()

It's still easier here - we use the ready-made pymsteams library, which simply collects the message according to the specification and sends it via requests.post. In my sample, I didn’t even use the features of O365 cards, which are also supported by WebHooks, but just made a few message templates - depending on the result of the task.

 team_connection = pymsteams.connectorcard(web_hook_url)   if event_object.job_type_name is not None:       if event_object.result_text == 'success':           text = 'A Veeam ' + event_object.job_type_name + ' **"' + str(event_object.job_name) + '"** has finished **successfully** at ' + str(event_object.end_time)[:-7]           team_connection.color('005f4b')  # it's a brand color named "Veeam Sapphire", btw 

Here fantasy comes in handy - add any text, pictures, buttons, etc. Here are the kinds of messages I’ve made:


For a backup job where everything went smoothly


For failed and warning tasks

Create a Docker Container

If you do not have a Docker yet - read A Docker Tutorial for Beginners . If there is, we need to create the following Dockerfile:

 # Version: 1.0 FROM python:3.6.2 MAINTAINER Dmitry Rozhdestvenskiy <dremsama@gmail.com> RUN apt-get update && apt-get install -y --no-install-recommends apt-utils RUN apt-get -y install locales RUN echo "en_US.UTF-8 UTF-8" > /etc/locale.gen RUN locale-gen RUN apt-get -y install apt-transport-https freetds-dev unixodbc-dev git RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - RUN curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list RUN apt-get -y update && ACCEPT_EULA=Y apt-get install msodbcsql RUN mkdir /veeam_to_msteams RUN git clone https://github.com/daymer/Veeam-to-MS-Teams-notification-sender-app /veeam_to_msteams RUN pip install --upgrade pip RUN pip install -r /veeam_to_msteams/requirements.txt RUN mkdir /var/log/veeam_to_msteams/ ADD configuration.py /veeam_to_msteams/ RUN chmod +x /veeam_to_msteams/launch_veeam_to_msteams.sh CMD ["/bin/bash", "/veeam_to_msteams/launch_veeam_to_msteams.sh"] 

All necessary Docker itself downloads from the repository (FROM python: 3.6.2), Github (RUN git clone ...) and packages.microsoft.com. Put the dockerfile in% directory_name%, next to configuration.py (didn't you forget to download and fill it out?). Build the image with the following command:

 docker build -t veeam_to_msteams:1.0 -f /path/%directory_name%/Dockerfile /path/%directory_name%/ 

Run the container:

 docker run  --restart=always  -it --name veeam_to_msteams -d veeam_to_msteams:1.0  bin/bash /veeam_to_msteams/launch_veeam_to_msteams.sh 

If you want to run the script on another platform or don’t want to use the container at all - it doesn’t matter, just run the main.py file itself, with no arguments.

---

It is time for me to fill a cup of coffee, and for you, if you are interested, to finish my sketch for the needs of your production. “You pay only for delivery”, more precisely, all you have to do is implement this very delivery into the preferred channel.

I bring together useful links:

1. My project on Github
   
2. Python SQL Driver - pyodbc
   
3. Creating Incoming WebHook Connector
   
4. How to enable Incoming WebHook Connector for MS Teams
   
5. Pymsteams library
   
6. A Docker Tutorial for Beginners