Extending SQL Server monitoring with Raspberry PI and Lametric

First blog of this new year 2021 and I will start with a fancy and How-To Geek topic

In my last blog post, I discussed about monitoring and how it should help to address quickly a situation that is going degrading. Alerts are probably the first way to raise your attention and, in my case, they are often in the form of emails in a dedicated folder. That remains a good thing, at least if you’re not focusing too long in other daily tasks or projects. In work office, I know I would probably better focus on new alerts but as I said previously, telework changed definitely the game.

I wanted to find a way to address this concern at least for main SQL Server critical alerts and I thought about relying on my existing home lab infrastructure to address the point. Reasons are it is always a good opportunity to learn something and to improve my skills by referring to a real case scenario.

My home lab infrastructure includes a cluster of Raspberry PI 4 nodes. Initially, I use it to improve my skills on K8s or to study some IOT stuff for instance. It is a good candidate for developing and deploying a new app for detecting new incoming alerts in my mailbox and sending notifications to my Lametric accordingly.

Lametric is a basically a connected clock but works also as a highly-visible display showing notifications from devices or apps via REST APIs. First time I saw such device in action was in a DevOps meetup in 2018 around Docker and Jenkins deployment with Eric Dusquenoy and Tim Izzo (@5ika_). In addition, one of my previous customers had also one in his office and we had some discussions about cool customization through Lametric apps.

Connection through VPN to my company network is mandatory to work from home and unfortunately Lametric device doesn’t support this scenario because communication is limited to local network only. So, I need an app that run on my local (home) network and able to connect to my mailbox, get new incoming emails and finally sending notifications to my Lametric device.

Here my setup:

171 - 0 - lametric_infra

There are plenty of good blog posts to create a Raspberry cluster on the internet and I would suggest to read that of Andrew Pruski (@dbafromthecold).

As shown above, there are different paths for SQL alerts referring our infrastructure (On-prem and Azure SQL databases) but all of them are send to a dedicated distribution list for DBA.

The app is a simple PowerShell script that relies on Exchange Webservices APIs for connecting to the mailbox and to get new mails. Sending notifications to my Lametric device is achieved by a simple REST API call with well-formatted body. Details can be found the Lametric documentation. As prerequisite, you need to create a notification app from Lametric Developer site as follows:

171 - 3 - lametric app token

As said previously, I used PowerShell for this app. It can help to find documentation and tutorials when it comes Microsoft product. But if you are more confident with Python, APIs are also available in a dedicated package. But let’s precise that using PowerShell doesn’t necessarily mean using Windows-based container and instead I relied on Linux-based image with PowerShell core for ARM architecture. Image is provided by Microsoft on Docker Hub. Finally, sensitive information like Lametric Token or mailbox credentials are stored in K8s secret for security reasons. My app project is available on my GitHub. Feel free to use it.

Here some results:

– After deploying my pod:

171 - 1 - lametric pod

– The app is running and checking new incoming emails (kubectl logs command)

171 - 2 - lametric pod logs

When email is detected, notification is sendig to Lametric device accordingly

Geek fun good (bad?) idea to start this new year 2021 :-)

Laisser un commentaire