Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Reporting Services to Search the SQL Server Log

By Carolyn Richardson,

If there is a lot of activity on your SQL Servers it is not always easy to read the log to find something specific. Using reporting services you can set up a simple report that allows you to filter the results.

This report looks at the logs in both SQL Server versions 2000 and 2005 and caters for the differing formats of the error log it does this by ignoring the continuation lines in the SQL Server 2000 log, it’s a best effort at getting the query to return something meaningful, I’m sure someone on this site could come up with something even better.

I’ve not touched on security here but I have created a user on all my servers for reporting, this may be necessary if your windows account does not have the appropriate rights to run the queries in all the servers you are looking at reporting on.

Create a table to store server names

Firstly you will need a table that holds a list of servers that you are going to report on, that is if you haven’t already got a table with this information :-

CREATE TABLE [dbo].[Server]
( [Server] [nchar](50) NULL
) ON [PRIMARY]

Add server names to this table as required.

ServerList

In Reporting Services add the first dataset to list the servers, to make it simple always use meaningful names:-

The Data source is your connection to the Server table you have just created.

ErrorList

The second dataset to add is to view the error logs. I called this Dataset ErrorList

Query String

SET NOCOUNT ON 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SET lock_timeout 10000 

IF SUBSTRING(@@version,23,4)='2000' 
BEGIN 

   IF EXISTS (SELECT FROM tempdb..sysobjects 
   WHERE id OBJECT_ID(N'[tempdb]..[#TempLog0]')) 
   DROP TABLE #TempLog0 

   IF EXISTS (SELECT FROM tempdb..sysobjects 
   WHERE id OBJECT_ID(N'[tempdb]..[#TempLog]')) 
   DROP TABLE #TempLog 

   CREATE TABLE #TempLog0 
   (ID INT IDENTITYErrorLog VARCHAR (2500), ContinuationRow smallint) 

   INSERT INTO #TempLog0 
   EXEC('xp_readerrorlog') 

   CREATE TABLE #TempLog 
   (LogDate datetimeProcessInfo VARCHAR(50),ErrorLog VARCHAR (1000)) 

   INSERT INTO #TempLog 
       SELECT LEFT(ErrorLog,23AS 'LogDate', 
       SUBSTRING(ErrorLog,24,10AS 'ProcessInfo',  
       SUBSTRING(ErrorLog,34,(LEN(ErrorLog)-33)) AS 'ErrorLog'  
       FROM #TempLog0 
       WHERE ISDATE(LEFT(ErrorLog,23)) 1 
       AND ContinuationRow 0 

   IF @filter IS NULL AND @LogDate IS NULL  
    BEGIN 
     SELECT LogDateErrorLog  
     FROM  #TempLog 
     ORDER BY LogDate DESC 
    END 
   ELSE IF @filter IS NOT NULL AND @LogDate IS NULL  
    BEGIN 
     SELECT LogDateErrorLog   
     FROM  #TempLog 
     WHERE ErrorLog LIKE  '%'+@filter+'%' 
     ORDER BY LogDate DESC 
    END 
   ELSE IF @filter IS NULL AND @LogDate IS NOT NULL  
    BEGIN 
     SELECT LogDateErrorLog  
     FROM  #TempLog 
     WHERE  LogDate BETWEEN CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101) 
       AND CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101) 
     ORDER BY LogDate DESC 
    END 
   ELSE --Both parameters have values 
    BEGIN 
     SELECT LogDateErrorLog  
     FROM  #TempLog 
     WHERE LogDate BETWEEN CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME),101) 
       AND CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101) 
     AND ErrorLog LIKE '%'+@filter+'%' 
     ORDER BY LogDate DESC 
    END 
END 
ELSE --2005 
BEGIN 

   IF EXISTS (SELECT FROM tempdb..sysobjects 
   WHERE id OBJECT_ID(N'[tempdb]..[#TempLog1]')) 
   DROP TABLE #TempLog1 

   CREATE TABLE #TempLog1 
   (LogDate datetimeProcessInfo VARCHAR(50),ErrorLog VARCHAR (1000)) 

   INSERT INTO #TempLog1 
   EXEC('xp_readerrorlog') 

   IF @filter IS NULL AND @LogDate IS NULL  
    BEGIN 
     SELECT LogDateErrorLog  
     FROM  #TempLog1 
     ORDER BY LogDate DESC 
    END 
   ELSE IF @filter IS NOT NULL AND @LogDate IS NULL  
    BEGIN 
     SELECT LogDateErrorLog   
     FROM  #TempLog1 
     WHERE ErrorLog LIKE  '%'+@filter+'%' 
     ORDER BY LogDate DESC 
    END 
   ELSE IF @filter IS NULL AND @LogDate IS NOT NULL  
    BEGIN 
     SELECT LogDateErrorLog  
     FROM  #TempLog1 
     WHERE LogDate BETWEEN CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME),101) 
       AND CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101) 
     ORDER BY LogDate DESC 
    END 
   ELSE --Both parameters have values 
    BEGIN 
     SELECT LogDateErrorLog  
     FROM  #TempLog1 
     WHERE LogDate BETWEEN CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME),101) 
       AND CONVERT(VARCHAR(50), CAST(@LogDate AS DATETIME), 101) 
     AND ErrorLog LIKE '%'+@filter+'%' 
     ORDER BY LogDate DESC 
    END 
END  
Now you need to make the connection to the Data Source dynamic, we are going to use the server name as a parameter so create a new Data source, I’ve called it Master.

Click on the … button next to the Data source on the query dataset tab.

Add the following as the datasource:- ="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"

Click on OK to save back to the Dataset popup window. Next Click on the Fields tab. Note that the field list cannot be picked up automatically from this data source, so you will need to add them as follows:-

You will return to the Dataset window later, but firstly we need to set up some parameters, so OK the dataset changes to save them.

From the top menu bar select Report/ReportParameters

The first parameter to enter is the ServerName which we are using both in the report to select from the ServerList dataset and is used in the Data source we set up for the ErrorList dataset.

The next parameter is not entirely necessary but allows you to add some version control which is probably good practice, so add the parameter VersionName as follows:-

The Default value being say the name of the report and the date created so add the value ="ErrorLog Version 1.0 (19/12/2007)"  in the Non queried field.

Next add the filter parameter:-

And lastly the LogDate parameter. Both these parameters are set to be Null as you may or may not wish to filter the report:-

With all the parameters set up return to the Data tab and edit the ErrorList dataset (… button by ErrorList name).

Click on the Parameters tab, you can now point to the parameters that you have just set up:-

The report uses the 3 parameters ServerName, Filter and LogDate, so set them up as per the illustration. You are now finally ready to write the report.

Layout

Move to the layout tab and add the details. 

In the body add a table, with 2 columns, the default gives you more columns that you need but you can delete them by right clicking the header and selecting delete columns. Drag the fields LogDate and ErrorLog into the detail selection of the table. This should give you titles in the header, but you can always amend/add the text as required.

To add a Page Header/Page Footer right click on the far top left corner (little black square, within a square) and highlight the ones you want to add.

In my header I added a text box to give the report the name Error Log. I also added a field which might be relevant if the report is printed, from the toolbox I dragged a new text box onto the report and added the value ="Report Time: " & Globals!ExecutionTime

In the footer I added another text box with the value:- =Parameters!VersionName.Value this being taken from the VersionName parameter I added before.

For one final touch select the title Log Date field in the table and in the SortExpression (Under UserSort in Properties) I added =Fields!LogDate.Value, this will allow me to sort the records in the field order.

I did the same with the Sort Expression against the Error Log header field, but obviously here I enter the =Fields!ErrorLog.Value as the expression.

Preview to check its all working then build and deploy.

Use the Null boxes to decide if a parameter is to be used or not. The beauty of this report is that you can schedule it to run for a specific server and specific messages and get it emailed to your inbox daily to see if a specific event you are monitoring is reported…

My New Years resolution for 2008 sit back and let the work come to you don’t go looking for it.

This report was created using Microsoft SQL Server Reporting Services Designers Version 9.00.3042.00. The sorting might not be available in earlier versions.

Report xml code ErrorLog.xml (see below)

Resources:

ErrorLog.xml | ErrorLog.sql
Total article views: 7296 | Views in the last 30 days: 2
 
Related Articles
FORUM

Optional grouping depending on selected parameter in reporting services

Optional grouping depending on selected parameter in reporting services

FORUM

Hide parameter selections for SSRS based on reporting level seletions

Hide parameter selections for SSRS based on reporting level seletions

FORUM

Can we align parameters of Report in selection

Can we align parameters of Report in selection

FORUM

SSRS report error : Multi value parameter report error only with Select All

Multi value parameter report error only with Select All

FORUM

SSRS Reporting : Multi value parameter report error only with Select All

Multi value parameter report error only with Select All

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones