[PowerShell] Outlook SMTP reports to MS SQL Express

[raymix]

Hi

I am not sure if I am allowed to release this to public or not, please delete this post if it breaks any rules.

We are an SMB on tight budget and were looking for a free endpoint backup solution for our roaming laptops, tried few other products, but Veeam seems to be by far the best for the job.
The problem was monitoring these backups especially because most of laptops rarely came on VPN and there is no cloud solution for Veeam free.

As an alternative solution, I have set up all our laptops with SMTP details (using sendgrid.net, they give us 12k free emails a month, which is pretty neat). Laptops were backing up multiple times a day (thanks to dedupe, compression and incrementals) and reporting everything back to my mailbox.
For mailbox we actually set up a Distribution list, so all emails were sent to whoever was added to a security group.
I have then created an outlook rule to move all incoming backup emails to a folder of it's own in outlook called "Backups".

The problem was sheer amount of laptops reporting back - we ended up with around 17k emails a year lol.
So I wrote a powershell script that connects to Outlook (task scheduler every 30 mins), reads contents from this backup folder, extracts HTML body, parses data into an array, uploads it to SQL express database on one of IT servers, marks email as read and deletes it.
Script can be found here - https://gist.github.com/raymix/49c4af8d ... 47bfd74674
For database we use latest SQL Express 2016, but it shouldn't matter. I have assigned AD user (myself) to a database and table, this allows my computer to upload data without saving passwords plain text in a script.

I decided to share this script with everyone else who might be in similar situation - wants a way to monitor backups, but can't afford it.
At this point data is only collected into SQL database, I would suggest that you use something like PowerBI Desktop (also free) (or kabana maybe?) to filter this database data into neat readable graphs or tables. PowerBI uses syntax very similar to excel formulas rather than SQL queries, so many of you should find it easy to work with.

Hope this helps.

Some screenshots:
Outlook setup - http://imgur.com/dZxlMp6
SQL express - http://imgur.com/ugYDBYy
PowerBI example - http://imgur.com/tVX6XE7