Tuesday, November 22, 2011

Use the Event Viewer, ASP.Net 4.0, and SSRS to make an error report

This article assumes you know how to write SSRS reports.

You have an application that is giving intermittent errors and you need a tool that will allow you to create reports on the errors of your application.  A side effect from this is that you will be able to report on all the errors that are logged in the event viewer.  The easiest way to track errors from your application would be to email yourself when the error occurs, that way you have an immediate notification of when the error happened.  This could be useful in fault isolation as you can move quickly and get a snap shot of the environmental conditions of when the error appeared.  You can email yourself the error by using the Global.asax file.

First and foremost you need to setup SMTP parameters in your web.config file:

<smtp from="NoReply@myServer.com">
<network host="myServer.com" port="25"
userName="NoReply@myServer.com" password="mypassword"/>

This is a pretty basic setup in your web.config file to utilize SMTP services.  All the parameters go in the ConfigSection / system.net portion of your web.config. 

Next we will need to go to your Global.asax file.  The first order of business is to add a reference to the System.Net.Mail namespace.  Additionally, we need to make a reference to the System.Diagnostics namespace.  We will do this at the top of the Global.asax file:

<%@ Application Language="VB" %>
<%@ Import Namespace="System.Net.Mail"%>
<%@ Import Namespace="System.Diagnostics"%>

Notice that it goes directly under the language declaration tag. 

Now that we have referenced the namespace we can begin adding our code to the Application_Error method.  This method is run EVERYTIME an error is encountered.  So if you are interested in every error produced by your “future enhancement” coding job, this is the one stop you want to make.  Now lets add the code to the method.

Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)

   Dim ErrorDescription As String = Server.GetLastError.ToString()

   Dim uRoles As String = "Admin"

   Dim addresses As New System.Collections.Generic.List(Of String)()

   Dim emailAddresses As String = String.Empty

   Dim users As String() = Roles.GetUsersInRole(uRoles)

   For Each username As String In users

       Dim user As MembershipUser = Membership.GetUser(username)

       If user IsNot Nothing Then

           If Not String.IsNullOrEmpty(user.Email) Then


           End If

       End If


   If addresses.Count > 0 Then

       emailAddresses = String.Join(",", addresses.ToArray())

       Dim mail As New MailMessage("WebPresence@MyServer.com", "")

       If Not String.IsNullOrEmpty(emailAddresses) Then


           mail.Subject = "An Error Has Occurred On The WebSite."

           mail.Body = ErrorDescription

           mail.IsBodyHtml = True

           Dim smtp As New SmtpClient


       End If

   End If

End Sub

Now when we encounter an error, we’ll receive an email provided we are a member of the Admin role.  This can easily be changed to a programmatic generated, XML, or SQL for the list of recipients. 

Now that we will be notified when the error occurs, it would be nice to have a historical record of the event which is where the event viewer comes in.  In this case, we are going to add some code that will actually create the entry into the Event Viewer.  You can use this in conjunction with the email notification by adding this snippet between the END IF and END SUB statements.

Dim EventLogName As String = "myApplicationName"
If (Not EventLog.SourceExists(EventLogName)) Then
EventLog.CreateEventSource(EventLogName, EventLogName)
End If
Dim log As New EventLog()
log.Source = EventLogName
log.WriteEntry(ErrorDescription, EventLogEntryType.Error)

Obviously, in place of the variable EventLogName as “myApplicationName” you will want to put the name of your particular application or some other means of identification. 
Now you can go ahead and induce an error on your Default.aspx.vb page_load by dividing zero by a number.  This automatically causes an error.

We should receive an email notifying us of the error and then we should be able to go to the SERVER’s Event Viewer and see the event registered there.

At this point we have the error recorded and emailed to us.  So now we need to get to the nitty gritty of creating the reporting tool.  So open your trusty copy of VS2010 and create a project of Class Library type.  Delete the default class (I hate that Microsoft creates this for you as you inevitably delete it).  Create a new class.

NOTE: It is extremely important to target the .NET 2.0 Framework while creating your class library as the SQL CLR only targets that Framework.

We don’t have to make any references but we do have to import some namespaces:

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Collections
Imports System.Data.SqlTypes
Imports System.Diagnostics

This will allow us to use a specified method of talking to the SQL server and Event Viewer.  From there we build our method using a localized WebService for our compiled DLL (in this instance my method is called viewer):

Public Class Viewer
<SqlFunction(FillRowMethodName:="FillRow")> _
Public Shared Function InitMethod(ByVal logname As String) _
As IEnumerable
Return New EventLog(logname, Environment.MachineName).Entries
End Function

Public Shared Sub FillRow(ByVal obj As Object, _
ByRef timeWritten As SqlDateTime, _
ByRef message As SqlChars, ByRef category As SqlChars, _
ByRef instanceId As Long)
Dim eventLogEntry As EventLogEntry = DirectCast _
(obj, EventLogEntry)
timeWritten = New SqlDateTime(eventLogEntry.TimeWritten)
message = New SqlChars(eventLogEntry.Message)
category = New SqlChars(eventLogEntry.Category)
instanceId = eventLogEntry.InstanceId
End Sub
End Class

The underbars “_” should be removed and the line of code concatenated into a single line (This will help with limiting the size of the DLL and faster processing.  I use them here for display purposes).

Ok, so looking at the code, you can see that we have a localize webservice for an SQLFunction called FillRow.  The init method is the initialization method for the service.  Notice that the method is looking for a logname variable of type String.  This will play an important part on the SQL side, and you could make this auto-populate however, for simplicity and ease of understanding we will keep this method intact.

From here we need to build the class library and remember where we put the dll (….\projectname\projectname\bin\Debug\).

Now we need to create our SQL SP to reference the created library and retrieve our reports:

First we need to create the database that will house our assembly.  So create a new database.  Then we need to set it to TRUSTWORTHY.

alter database [DevelopmentDatabase] set TRUSTWORTHY on

Then we need to create our assembly inside SQL:

FROM 'C:\Sandbox\EventLogViewer\MyViewer


Of course your FROM statement will reflect the physical location of where your DLL is.  You can copy this from the output window of your build from VS2010.  Now we need to create our function:

CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
(logTime datetime,Message nvarchar(4000),
Category nvarchar(4000),InstanceId bigint)
EXTERNAL NAME ReadEventLog.[MyViewer.Viewer].InitMethod

The external name is in the following format:


Now all there is left to do is query the Function:

select * from dbo.ReadEventLog('System')

Where system is the name of the EventLog.  In place of system you would use the name you declared in the Global.asax as the name of the log.

If you receive an error that CLR is not turned on you need to run the following code:

Exec sp_configure 'show advanced options','1';
Exec sp_configure 'clr enabled','1'

This will enable CLR for SQL.

Another way to bring in your log from the Event Viewer is to use LogParser.  You can download LogParser @:


Log Parser 2.2 is one of the most versatile applications there are for reading logs of any type.  You can import the Event Viewer Log into SQL using the following command:

logparser "select EventLog, TimeWritten, EventID, EventCategoryName, Message into LogTable from System" -o:SQL -createTable:ON –server: system\sqlservername -database:DevelopmentDatabase -username:myUsername-password:myPassword -createtable:ON

Happy .Netting… Saleh

No comments:

Post a Comment