SSRS Log Monitoring

This is something Ive put together to keep track of errors coming through the SSRS logs.

We capture the majority of priority errors through other methods but I find some still slip through if you’re not checking your log files regularly. It still baffles me that SSRS doesnt have better error monitoring/logging for subscriptions in particular. Even in 2016 we still use a custom SQL job to monitor & alert on failed subscriptions.

Here’s a quick overview of what Im doing with the logs:

So the idea of this is both scripts are ran either from Task Scheduler or SQL Agent jobs. Script1 (SSRSLog_Scan.ps1) should be ran every 30 mins or less. Script2 (SSRSLog_Notify.ps1) should be ran every 1-2 hours. You can adjust these as needed or even combine them into one single script. Ive left these separate to minimize overhead and Ill likely schedule Script2 to run every 4 hours as we get all our important alerting through System Center (SCOM).

There’s a lot of assumptions made in the below scripts & processes so if you have any questions drop me a mail or pop in a comment.

I plan to develop the data collected in the SSRSLogErrors table in future & will relate any posts back to this one.

  • One example of a further development would be to introduce a security alert that specifically picks out Access Denied¬†errors and copies the user in on the alert email to quickly address an access request or security issue.

Script 1 – SSRS Log Scanning

The first thing to do is to set your relevant variables for Script1 before testing it out. Ive set these all at the start so you shouldnt need to change anything below the first “line”. Nothing is stored locally so you could place these scripts in any folder on any relevant server.

 

Database Table

You need to manually create the database table to house all of the log data. This will be useful for trend & historical analysis but you may want to alter the script to include more context for the errors. (highlighted that with a comment in Script 1)

Script 2 – SSRS Log Notification

As before, please update the variables to those relevant to you

 

Thanks! Happy Powershell..ing?

You may also like...

Leave a Reply