Aug2008
25

ASP.NET Health Monitoring - Building an EventLogWebEventProvider - Part 3

by nmgomes

On my previous posts on this health monitoring series I explain to you how and why I made my own EventLogWebEventProvider and which benefits you can achieve by using this provider or by making your own.

Now I'll write about how to use this new provider in one application.

Well, almost everything have been written about this topic and Microsoft has one good article on "How To: Use Health Monitoring in ASP.NET 2.0" so I wont even try to explain all the possible scenarios and configurations, I will simply explain the standard scenario.

Typically I simply want to keep track on two Web Event types:

  1. the ones related to application errors
  2. an the others related to application life cycle

As far as I notice there are many people that simply track those from point 1.

Another decision to make is where to store these Web Events data, You do this by choosing a Web Event provider.

ASP.NET give us out-of-the-box several providers and you can make your own provider. Once your provider is done you can use it just like all the others.

In this example I will use the newly created provider.

Now that I know exactly what to track and where to store it I can update my configuration.

All health monitoring configuration data is stored in the system.web\healthMonitoring section and my standard configuration will look like:

<healthMonitoring enabled="true">  <providers>    <add name="ExtendedEventLogWebEventProvider" type="NG.Web.Management.EventLogWebEventProvider, NG.Web" source="MyEvtLogSource" />  </providers>  <rules>    <clear />    <add name="Application Lifetime Events Default" eventName="Application Lifetime Events" provider="ExtendedEventLogWebEventProvider" profile="Default" />    <add name="All Errors Default" eventName="All Errors" provider="ExtendedEventLogWebEventProvider" profile="Default" />  </rules></healthMonitoring>

Notice the source attribute from the ExtendedEventLogWebEventProvider, using it you can set which EventLog source to use.

Remember that when you create a EventLog source you can choose to create a brand new EventLog and if you choose so, all entries written by this provider in this application context will be isolated from all the others providing one easy way for visual tracking and filtering.

With the health monitoring data in place you should now be able to see the entries appearing in EventLog.

Filed in: ASP.NET | CodeProject

Aug2008
24

ASP.NET Health Monitoring - Building an EventLogWebEventProvider - Part 2

by nmgomes

In the first post of this series I've manage to find the correct eventId for each Web Event type, and by this time the major problem has been solved, but I cannot yet write a correct entry into the EventLog.

I still have to decided the best severity type and category to apply.

Severity

If you look at entries in the EventLog generated by the default EventLogWebEventProvider you will find that they are marked mainly as Information and a few of them are also marked as Warning but no one is ever marked as Error.

Since I'm making my own provider I will take this chance to map the EventLog entry severity type according to the source Web Event.[more]

Web Event

EventLog entry Severity
WebBaseEvent EventLogEntryType.Information
WebManagementEvent EventLogEntryType.Information
WebApplicationLifetimeEvent EventLogEntryType.Information
WebRequestEvent EventLogEntryType.Information
WebHeartbeatEvent EventLogEntryType.Information
WebBaseErrorEvent EventLogEntryType.Warning
WebRequestErrorEvent EventLogEntryType.Error
WebErrorEvent EventLogEntryType.Error
WebAuditEvent EventLogEntryType.Information
WebSuccessAuditEvent EventLogEntryType.SuccessAudit
WebAuthenticationSuccessAuditEvent EventLogEntryType.SuccessAudit
WebFailureAuditEvent EventLogEntryType.FailureAudit
WebAuthenticationFailureAuditEvent EventLogEntryType.FailureAudit
WebViewStateFailureAuditEvent EventLogEntryType.FailureAudit

Naturally, you can decided to map the Web Events exactly has the default EventLogWebEventProvider does.

Finally all I need is to do is set the correct category for the Web Events.

Category

Although all Web Events belong to the same category 'Web Events', the problem is how to make the correct text appear.

The value shown in the category property is a resource string and to select the correct category value is necessary to set the  exact resourcekey.

That is not so hard to do, I could have made my own resource assembly with only one resource, but I have a better way ... I will use the same resource assembly that ASP.NET use.

To figure out which assembly to use I simply used regedit.exe to look at "HK_LM\System\CurrentControlSet\Services\EventLog\Application\ASP.NET 2.0.50727.0"

registry_EventLog

What I look for are the settings used by the "ASP.NET 2.0.50727.0" EventLog source which is the source used to write the Web Events EventLog entries.

The settings I will use are the CategoryCount and CategoryMessageFile keys because they are the ones that instruct which category assembly to load.

Name

Data
CategoryCount 5
CategoryMessageFile C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_rc.dll

Please note that the CategoryMessageFile may change according to operating system and ASP.NET version.

Finally, I need to know the exact resourcekey of the "Web Event" string, and since there simply 5 resources in assembly it took no time to get the following table:

Category Resource Key

Category
0 None
1 Setup
2 Uninstall
3 Web Event
4 File Monitoring
5 Admin Service

Now I simply need to configure my EventLog source to use those same settings.

As I said in part 1, you can create an EventLog source using the EventLog.CreateEventSource method, but for setting the CategoryCount and CategoryMessageFile values you must use this specific overload EventLog.CreateEventSource(EventSourceCreationData sourceData).

That's it ... I've got everything I need and the final result look like this:

EventLog

It looks similar to the usual but if you look closer you will find the differences.

Benefits

What you need to keep in mind is that:

  • the EventId and Category values are keep unchanged;
  • the entry Type respects the Web Event type as opposing to the EventLogWebEventProvider given by ASP.NET that only uses Information and Warning;
  • the EventLog source can be one of my choise (not the usual "ASP.NET x.xxx") and can change by application;
  • you can choose either to use the existing EventLogs or to create a brand new one for your application(s) .

As a final note remember that with this provider you can now organize your application EventLog entries into specific EventLogs, and you may also filter them by application Source.

Download the code here.

Filed in: ASP.NET | CodeProject

Aug2008
18

SQL Server - Undocumented Stored Procedure sp_MSforeachtable

by nmgomes

I'm not an every day SQL Server user but I use SQL Server regularly since 7.0 version until the 2005 version (not yet tried 2008 in a serious way) and from time to time I still find some nice hidden gems.

A few days ago I needed to created a cleanup script for an application and one of the tasks was to drop all tables that match a specific name pattern.

My first thought was to use a cursor to loop or a dynamic SQL statement ...

... but this time I decided to google for some other approach, and I found the amazing undocumented sp_MSforeachtable stored procedure from the master database.

It does the same but it requires considerably less code and improves the script readability.

Below is the syntax for calling the sp_MSforeachtable SP: [more]

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,   @command3, @whereand, @precommand, @postcommand

Where:

  • @RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
  • @command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
  • @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
  • @command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
  • @whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
  • @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
  • @postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

As you can see, this stored procedure offer us some flexibility, but for the most common uses you will only use one or two of them.

Back to my problem, drop all tables with a specific naming pattern, I ended up using a script just like this:

declare @appName varchar(128)declare @mycommand varchar(128)declare @mywhereand varchar(128)set @appName = 'xpto'set @mycommand = 'drop table ?'set @mywhereand = 'and o.name like ''' + @appName + '__Log__%'' escape ''_''print 'Dropping all tables belonging to ' + @appName + ' application ...'exec sp_MSforeachtable                 @command1 = @mycommand,                 @whereand = @mywhereand

What I'm saying here is that the command 'drop table' should be executed for every table that match the criteria name like 'xpto_Log_%'.

As you can see its fairly simple and clean and this is just the top of the iceberg.

For more detail about sp_MSforeachtable go here and here.

Filed in: CodeProject | MS SQL