Craig Porteous

A Data Blog

SSRS Log Monitoring

by · Published · Updated

Reading Time: 4 minutes

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.

#-----------------------------------------------------------------------
# DATE:		02/08/2016
# AUTHOR:	Craig Porteous
# USE:		Monitors most recent SSRS Log for errors, writing any 
#     found to a table for a second script to pick up (SSRSLog_Notify.ps1)
#     & notify via email
#-----------------------------------------------------------------------
# Setting Variables
#-----------------------------------------------------------------------
cls
$DBserver = 'DATABASESERVER'
$Database = 'DATABASE'
#This can have as many or as few "nodes" as you have. Edit as needed.
$SSRSScaleOut = "SSRS01", "SSRS02", "SSRS03" 
$result = @()
#-----------------------------------------------------------------------

foreach($Node in $SSRSScaleOut){
		#NOTE: This path is valid for a standrard install of SQL 2014. Please adjust as needed!
	$path = '\\' + $Node + '\c$\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles\'

	$filter = 'ReportServerService_*'

		#Pull the most recent log into a variable
	$latestLog = Get-ChildItem -Path $path -Filter $filter | Sort-Object LastAccessTime -Descending | Select-Object -First 1
	
		#Set log up in a variable and look for Error Lines.
		#NOTE: The addition of -context below will bring in the next (1) line in the error log to provide context to the error captured.
	$errlog = Get-Content $latestLog.Fullname
	$errorlist = $errlog | Select-String  -pattern 'ERROR:'  #-context 0,1

		#Add error & related info to array of objects	
	foreach($row in $errorlist) {
	$myobj = @{
        ErrorMessage	= $row -replace "'",""
		Source			= $Node
        LogFileName		= $latestLog.name
    }
	$result += New-Object PSObject -Property $myobj
}
}
#-----------------------------------------------------------------------
#Create DB connection to add errors to SQL Table
#-----------------------------------------------------------------------
$ConnINSERT = New-Object System.Data.SQLClient.SQLConnection
$ConnINSERT.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"
$ConnINSERT.Open()
		#Step through each record in the result set, check if its already in the table & INSERT if not found.
	foreach($err in $result) {
	    $sqlINSERT ="if not exists (select 1 from [SSRSLogErrors] where ErrorMessage = '$($err.ErrorMessage)' ) 
	        begin 
	        INSERT INTO dbo.SSRSLogErrors([ErrorMessage],[Source],[CaptureTime],[Notified],[LogFileName])
	        SELECT '$($err.ErrorMessage)',
			'$($err.Source)',
			GetDate(),
			0,
			'$($err.LogFileName)'
	        end" 
		$Command = New-Object System.Data.SQLClient.SQLCommand($sqlINSERT, $ConnINSERT)
	    $numROWS = $Command.ExecuteNonQuery()
	}
	
$Connection.Close()
Exit
#-----------------------------------------------------------------------
# End of Script
#-----------------------------------------------------------------------

 

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)

USE [A_Suitable_DB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSRSLogErrors](
	[ErrorMessage] [nvarchar](max) NOT NULL,
	[Source] [nvarchar](30) NOT NULL,
	[CaptureTime] [datetime] NOT NULL,
	[Notified] [int] NOT NULL,
	[LogFileName] [nvarchar](100) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Script 2 – SSRS Log Notification

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

#-----------------------------------------------------------------------
# DATE:		02/08/2016
# AUTHOR:	Craig Porteous
# USE:		Notifies recipients of Errors captured in SSRS Logs by 
#     another script (SSRSLog_Scan.ps1) from SQL table & updates records
#     when notified.
#-----------------------------------------------------------------------
# Setting Variables
#-----------------------------------------------------------------------
cls
$mailServer = "mail.craigporteous.com"
$mailFrom = "Alerts@craigporteous.com"
$mailTo = "Everyone@craigporteous.com"
$mailAdmin = "craig@craigporteous.com"
$result = @()

$DBserver = 'dbservername'
$Database = 'dbname'
$sqlSELECT = 'SELECT [ErrorMessage],
				[Source],
				[LogFileName]
  				FROM [dbname].[dbo].[SSRSLogErrors]
  				WHERE [Notified] = 0'

#-----------------------------------------------------------------------
# Opening connections to DB. To retrieve & update records.
#-----------------------------------------------------------------------
$ConnSELECT = New-Object System.Data.SQLClient.SQLConnection
$ConnSELECT.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"
$ConnSELECT.Open()

$ConnUPDATE = New-Object System.Data.SQLClient.SQLConnection
$ConnUPDATE.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"
$ConnUPDATE.Open()
#-----------------------------------------------------------------------

$Command = New-Object System.Data.SQLClient.SQLCommand($sqlSELECT, $ConnSELECT)

$errorlist = $Command.ExecuteReader()

#Adding resultset to object to be added to array
foreach($row in $errorlist) {
	$myobj = @{
        ErrorMessage	= $row.Item("ErrorMessage")
		Source			= $row.Item("Source")
        LogFileName		= $row.Item("LogFileName")
    }
	$result += New-Object PSObject -Property $myobj
}
$ConnSELECT.Close()
#-----------------------------------------------------------------------
# Build email content.
#-----------------------------------------------------------------------

$font = "<font face=""Verdana"">"
#$body = $font
$body = "$font <H3>Reporting Services - Log Errors</H3>"
$body += "<br><br>Please review these errors."
$body += "<br><br>---------------------------------------------------------"
$body += "<br><br>"
	$a = "<style>"
	$a += "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
	$a += "TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color:#642891;color:white;}"
	$a += "TD{border-width: 1px;padding: 1px;border-style: solid;border-color: black;}"
	$a += "</style>"
$body += $result | Select ErrorMessage,Source,LogFileName | ConvertTo-HTML -Head $a
$body += "<br><br>"

$Subject = "SSRS Log Errors"

If ($result.count -eq 0){
	#No new errors found. Exit
	Exit
}
Else
{
	$selROWS = $result.count
	send-mailmessage -SmtpServer $mailServer -from $mailFrom -to $mailTo -subject $Subject -BodyAsHtml -Body $body -ErrorAction Stop
}
#-----------------------------------------------------------------------
#Updating notified records in table if email sent.
#-----------------------------------------------------------------------

foreach($err in $result) {
	    $sqlUPDATE ="if exists (select 1 from [SSRSLogErrors] where ErrorMessage = '$($err.ErrorMessage)' ) 
	        begin 
	        UPDATE dbo.SSRSLogErrors
	        SET Notified = 1
			WHERE ErrorMessage = '$($err.ErrorMessage)'
			end
	    " 
		$Command = New-Object System.Data.SQLClient.SQLCommand($sqlUPDATE, $ConnUPDATE)
	    $updROWS += $Command.ExecuteNonQuery()
	}
$ConnUPDATE.Close()

#-----------------------------------------------------------------------
#Add check in here to make sure all the rows we report on were updated
# as "notified". Abort & notify "Admin" if failed.
#-----------------------------------------------------------------------
#$updROWS = $numROWS
If ($updROWS -lt $selROWS){
	send-mailmessage -SmtpServer $mailServer -from $mailFrom -to $mailAdmin -subject "SSRS Log Mismatch Notice!" -BodyAsHtml -Body "$selROWS 
		Errors Notified. Only $numROWS Rows updated in table.<br> Please check source:<br> $sqlSELECT <br><br> $body"
}

#------------------------------------------------------------------
# End of Script
#------------------------------------------------------------------

 

Thanks! Happy Powershell..ing?

Tags:

You may also like...

Leave a Reply