Category: EventSentry

How to dynamically toggle your Wireless adapter with EventSentry

ingmar.koecher 0 Comment EventSentry, Monitoring, Tips & Tricks, Tools & Utilities

Most of the time I work on a Lenovo laptop running Windows 7, and I’m overall quite happy with the laptop (especially after the mainboard was replaced and it stopped randomly rebooting). However, a minor nuance had been bugging me for a while: If I plugged my computer into a LAN (I have a docking station at work and at home) while a wireless signal was also available (and configured on the laptop), Windows 7 would keep both connections active.

1. The Problem

So I’d have my laptop in the docking station, connected to a 1Gb Ethernet network, and yet the laptop would also be connected to a WiFi network. While not a big deal per se, it does have a few advantages to automatically disable the WiFi connection when already connected to Ethernet:

  • Avoid potential connectivity issues
  • Increase security by not transmitting data via Wifi when not necessary
  • Increase battery life when connected to a LAN
  • Because you can!

Now, Lenovo equips most (if not all) laptops with a software called “Access Connections”, a pretty nifty and free tool! One of the things it can do, is disable a Wireless adapter when the computer is connected to Ethernet. However, it never re-enables it when you disconnect from the wired network (at least I haven’t found a way), and besides not everybody has Lenovo laptop.

So how could I disable the WiFi connection automatically when I connected the laptop to an Ethernet, yet automatically re-enable it when there is no Ethernet connection?

2. The Research

After some intense brainstorming, I remembered two things:

  1. Most Ethernet NIC drivers log event to the System event log when a network port is connected/disconnected.
  2. A while back, I used the netsh command to configure DNS servers from the command line. Maybe one could toggle the state of network adapters with this tool as well?

3. Evidence Gathering

The first one was easy, a quick look at the system event log revealed the following event:

e1kexpress_event.pngA similar event is logged when the “network link” has connected. The event shown here is specific to the driver of my laptop’s network card (an Intel(R) 82577LM adapter), but most newer drivers will log events when a cable is disconnected or the link is otherwise lost. If you are already running EventSentry with its hardware inventory feature enabled, then you can obtain the name of the network adapter from any monitored host on the network through the hardware inventory page, an example is shown below.

all_nics.pngComing up with a way to enable and disable a particular network connection with netsh.exe was a bit more challenging, but I eventually cracked the cryptic command line parameters of netsh.exe.

Enable a connection
netsh interface set interface “Wireless Network Connection” ENABLED

Disable a connection
netsh interface set interface “Wireless Network Connection” DISABLED

And yes, you do need to specify the word “interface” twice. If you do find yourself wanting to automate network adapter settings with scripts and/or the command line frequently, then you should check out this link.

4. The Solution

So now that we have all the ingredients, let’s take a look at the recipe. In order to accomplish the automatic interface toggling, we need to create:

  • an embedded script called wifi_enable.cmd, using the command line from above
  • an embedded script called wifi_disable.cmd, again using the command line from above
  • a process action “Wifi Enable”, referencing the above wifi_enable.cmd embedded script
  • a process action “Wifi Disable”, referencing the above wifi_disable.cmd embedded script
  • an event log filter for event source “e1kexpress” and event id 27, triggering the “Wifi Enable” action
  • an event log filter for event source “e1kexpress” and event id 32, triggering the “Wifi Disable” action

A couple of clarifications: First, you do not need to use embedded scripts, you can create the scripts in the file system too and then point the process action to those files. I prefer embedded scripts since I don’t have to worry about maintaining the script, as it gets distributed to remote hosts automatically when needed. Second, the event source and event id will depend on the network card you have installed on your network, the above example will only work with Lenovo T410 laptops.

So what happens is pretty straightforward: When I connect my laptop to a LAN, the Intel NIC driver writes event id 32 with the event source e1kexpress to the system event log. EventSentry intercepts the event and triggers the Wifi Disable action, which in turns runs the netsh.exe process, disabling the WiFi connection.

5. Setting it up in the management console

Embedded Scripts
You can manage embedded scripts via Tools -> Embedded Scripts. Click “New”, specify a descriptive name (e.g. wifi_enable.cmd) and paste the command line netsh interface set interface “Wireless Network Connection” ENABLED into the script content window. Then, do the same for the wifi_disable.cmd script, but this time use the netsh interface set interface “Wireless Network Connection” DISABLED command line. You can leave the interpreter empty as long as you give the filename the .cmd extension.

embedded_script.pngActions

Create two process actions, one pointing to wifi_enable.cmd, and one pointing to wifi_disable.cmd. You can access these embedded scripts by clicking the pull-down – you should see the embedded script(s) you created in step one – each prefixed with the @ symbol. The resulting dialog should look like this:

process_wifi_enable.pngI recommend enabling both “Event Log Options”, as this will help with troubleshooting. Now we just need the event log filters, and we are all set.

Like I mentioned earlier, you can also reference any external process or .cmd file with the process action, if you’d rather not use embedded scripts.

Event Log Filters
Since we’ll need two filter, we’ll create a new event log package called “Toggle Wifi” by right-clicking the “Event Log Packages” container and selecting “Add Package”. Inside the package we can then add the two filters: One to trigger the “Wifi Enable” action when the NIC drivers logs its event indicating that the network cable was unplugged, and one that will trigger the “Wifi Disable” action when the NIC drivers logs that the network cable was plugged in. The filter will look similar to this, but note that the event source as well as event id will depend on the network card and driver.

filter_wifi_enable.pngThat’s pretty much it. If you enabled the event log options in the process action earlier, then you can see the output from the process action in the event log, as shown below:

event_action.pngHere are some links to the official EventSentry documentation regarding the features used:

EventSentry v2.92 + iPhone App

ingmar.koecher 0 Comment Announcements, EventSentry, Monitoring

EventSentry v2.92
The main new features in version 2.92 are:

  • Network Services component
  • Embedded performance / environment charts
  • HTTP action
  • Hardware Inventory & Monitoring

Please see the release history or the documentation for a complete list of all bug fixes and new features.

Network Services
The network services component is the main new feature in EventSentry, and adds powerful functionality – the ability to receive SNMP traps – to our monitoring solution. Any network device that supports SNMP v1, v2c or v3 can be configured to send traps to EventSentry, which can then either forward the trap details via email, or log them to the database for archival and searching purposes.

Unlike other products, configuring the snmp trap daemon is easy and uncomplicated. Simply specify any MIBs you want to load, configure any SNMP communities and/or v3 users, and (optionally) setup text filters to include/exclude specific traps based on their content.

The network services component now also includes the syslog daemon, which was previously embedded in the EventSentry agent. This change includes higher availability, since the network services will cache incoming (snmp and/or syslog) packets if the main database is temporarily unavailable.

Another new “feature” is the ability to run the network services daemon on Linux and OS X operating systems. We developed all code in the network services to run on multiple platforms, and will continue to do so with all upcoming new components in EventSentry. Cross-platform functionality is still experimental, and as such we don’t currently have an installer for non-Windows operating systems. If you are interested in running the network services daemon on Linux or OS X, then please send a request to our support team.

Visualizing performance and environment data with embedded charts
Performance monitoring and alerts have always been a popular and useful feature in EventSentry, but a numerical alert can only convey so much information. If an alert shows that the CPU usage has been exceeded, say 80% over a time period of 10 minutes, then it is often helpful to see the performance data in a chart. This would show whether the CPU usage spiked during the beginning of the 10-minute interval and is back in an acceptable range, or whether the CPU usage has been mostly constant. In the past, you would need to review the performance history through the web-based reporting (which is still an option and desirable for long-term analysis), but starting with v2.92 email alerts can now include an embedded chart which shows exact performance information from the monitored time interval.

For example, if your performance alert is configured to generate an alert when the CPU usage exceeds 90% for more than 30 minutes, then you can receive an attachment in addition to the text alert, showing performance data from the last 30 minutes.

eventsentry_attachment_performance.pngEmbedded charts are also included with temperature and/or humidity environment alerts, an example chart is shown below:

eventsentry_attachment_environment.png

HTTP Action
To better interact with web-based solutions like iService, EventSentry now includes a new notification type: HTTP. With the new HTTP action, you can submit event data to any HTTP or HTTPS-based form, making it extremely easy and reliable to interact with web-based systems (e.g. ticketing systems). Setting up a HTTP action is easy: Simply provide the URL, authentication and specify any event fields that you want to submit.

Hardware Inventory & Monitoring
Also new are network link speed monitoring, which can alert you when the NIC speed changes. This can be useful if a host is accidentally connected to a different switch, or simply to inventory all network connections.

EventSentry also monitors connected USB storage devices and can alert you when a drive is added or removed. The S.M.A.R.T. status of drives is also monitored, and junction points are now taken into consideration when evaluating the disk space of a logical drive. Also new is a “physical disks” section in the web reports.

iPhone App
Why yes, there is an app for that! How convenient would it be to review the health status of all your monitored hosts from your iPhone – including health statistics, hardware details and more?

Dream no more, with the EventSentry iPhone app you can review the status of all your servers from your phone (so long as it has access to your web reports of course).

Upon launch the app shows a searchable list of all monitored hosts and their respective health status. Tapping on a host will show detailed information, such as performance data, hardware information and more. Please take a look at the official iTunes page for the EventSentry app for more information and screenshots.

If you are an Android user then there is no reason to worry, an Android version of the EventSentry Mobile app is planned later this year.

Again, please see the version history for a complete list of features and fixes in v2.92.

How to REALLY monitor SMTP, POP3 and IMAP on Exchange 2003

ingmar.koecher 0 Comment EventSentry, Monitoring, Tips & Tricks, Tools & Utilities

Even though Microsoft Exchange Server 2010 has already been released, many organizations still use Exchange 2003. In this article I’ll explain how to thoroughly monitor the various Internet protocols that Exchange 2003 offers, including SMTP, POP3, IMAP (and NNTP for that matter). The reason why I’ll only be looking at Exchange 2003 is because there is a significant difference in architecture between Exchange 2003 and later versions.

It is a common misconception that you can effectively monitor the W3SVC service (commonly referred to as IIS, though IIS encompasses a lot more than just a web server) and other services provided through IIS, such as SMTP and POP3, by simply monitoring their associated service. It’s a misconception, because a given IIS-based service may contain multiple instances – most commonly the case with the World Wide Web Service which often hosts multiple independent web sites. The status of these instances can be controlled independently of the hosting service, though that service needs to be running of course.

Don’t despair though, most server-based windows applications, fortunately, can be monitored by ensuring that their respective service is – well – running. For example, to ensure that the Apache service is up, you “simply” make sure that the Apache service is running. The same goes for countless other services such as MySQL – even SQL Server (of course you can still detach individual databases in SQL Server).

Exchange 2003, due its partnership with the Internet Information Services 6.0, is different though. Yes, IIS and Exchange 2003 are tightly coupled, and if you intend to have your Exchange Server 2003 communicate with any other server using a standard Internet protocol such as SMTP, then you will need IIS.

eventlogblog_2010_01_exchange_iis_components.png

The screenshot above shows that the inetinfo.exe process hosts all the major services (bold name), and that each service can host one or more instance. For more details please see http://technet.microsoft.com/en-us/library/bb124674(EXCHG.65).aspx.

The three most common Internet services your Exchange 2003 server is running are probably SMTP, POP3 and IMAP4. While a lot of attention is being paid to the core Exchange services such as

• Microsoft Exchange Information Store (MSExchangeIS)
• Microsoft Exchange System Attendant (MSExchangeSA)

The services providing SMTP, POP3 and IMAP4 connectivity are usually similarly important, especially the SMTP service. Looking at the EventSentry service status page immediately reveals that the SMTP, POP3 and IMAP4 services are managed by IIS:

Thumbnail image for eventlogblog_2010_01_es_services.png

As you can see, IMAP4Svc, POP3Svc and SMTPSvc all use inetinfo.exe (Executable column) for their host process. So why is this important again?

Since all of these services support multiple instances INSIDE the service (inetinfo.exe), the host process will continue to run even when one or more instances inside the service are stopped. Since most installations only have one instance, stopping that one instance inside the service will still leave the service up and running. The effect of course is the same; the service is not available to the end users while the Windows service will happily continue to run.

A screen shot from the System Manager application shows instances listed inside:

eventlogblog_2010_01_system_manager.png

As you can see with the IMAP4 protocol, we have two virtual servers setup that are both hosted inside the “Microsoft Exchange IMAP4” service. To stubbornly illustrate my point further I took a screenshot that shows both IMAP4 instances stopped while the service itself is running:

eventlogblog_2010_01_system_manager_services.png

So I think we’re all in agreement now that monitoring the POP3, SMTP etc. services in Exchange 2003 is not enough if you want to ensure that these services are actually available. So how do we monitor all of these instances?
The easiest way is actually with a VBScript, which is included below. VBScript works well since the cscript.exe interpreter is readily installed on Windows 2003, so no additional installation of tools is required. The script enumerates all instances of a given protocol, and checks whether they are running or not. If at least one instance is not running, the tool will return 1, thus setting the ERRORLEVEL to 1.

This VBScript can then be embedded into EventSentry, which will then run the script at set intervals using the application scheduler, notifying you via email (with the proper filter setup) when an instance is stopped. There’s a screencast for that, you can view it at https://www.eventsentry.com/screencasts/eventsentry-application-scheduler/eventsentry-application-scheduler.htm. It shows you how to create an embedded script and setup EventSentry to notify you when the scripts returns an error. Note that the screencast uses an older version of the script which only monitored web sites (not SMTP, IMAP4, …), but the process of setting up the script with EventSentry is exactly the same.

You should be able to use the script as-is, just configure which protocols are monitored by adjusting the values in the “Define which protocols to monitor here” section. The script always prints all installed instances and their status, and any stopped instance is prefixed with an asterisk. Below is what an email from EventSentry looks like:

eventlogblog_2010_01_appscheduler_email.png

The line with the stopped instance won’t be yellow in the actual email, I just added this for readability. The script can also easily be modified to automatically start any stopped instances – simply add the line

Instance.Start

after line 102. This will still trigger an email (or error) to notify you that it was stopped, but a subsequent run of the script at the next monitoring interval should not trigger an error again if the start was successful.

A note of caution here though – I have seen the script hang indefinitely with this line added when an instance that is currently stopped can’t be started because it’s not configured correctly. Hence, it’s not included by default.

 

‘ Lists the state of all IIS protocols configured on the local machine
‘ and returns an %ERRORLEVEL% of 1, if at least one instance is not in
‘ the “Started” state.
‘
‘ When scheduling this script with EventSentry’s application scheduler,
‘ make sure that the interpreter is set to “cscript.exe”

Option Explicit

Dim allInstancesAreRunning

Dim monitorSMTP, monitorPOP3, monitorIMAP4, monitorNNTP, monitorFTP, monitorWWW

‘ Define which protocols to monitor here

monitorSMTP  = 1
monitorPOP3  = 1
monitorIMAP4 = 1
monitorNNTP  = 1
monitorFTP   = 1
monitorWWW   = 1

‘ Define which protocols to monitor here

‘ ==================== EXECUTION STARTS HERE ====================
allInstancesAreRunning = EnumerateAllInstances

If allInstancesAreRunning = 0 Then
WScript.Echo vbCRLF & “WARNING: One or more IIS components are not running” & vbCRLF
End If

If allInstancesAreRunning = 0 Then
WScript.Quit 1
End If

‘ ==================== FUNCTIONS ====================
Function EnumerateAllInstances

EnumerateAllInstances = 1

If monitorSMTP = 1 Then
EnumerateAllInstances = EnumerateAllInstances And EnumerateInstances(“localhost”, “SMTPSVC”)
End If

If monitorPOP3 = 1 Then
EnumerateAllInstances = EnumerateAllInstances And EnumerateInstances(“localhost”, “POP3SVC”)
End If

If monitorIMAP4 = 1 Then
EnumerateAllInstances = EnumerateAllInstances And EnumerateInstances(“localhost”, “IMAP4SVC”)
End If

If monitorNNTP = 1 Then
EnumerateAllInstances = EnumerateAllInstances And EnumerateInstances(“localhost”, “NNTPSVC”)
End If

If monitorFTP = 1 Then
EnumerateAllInstances = EnumerateAllInstances And EnumerateInstances(“localhost”, “FTPSVC”)
End If

If monitorWWW = 1 Then
EnumerateAllInstances = EnumerateAllInstances And EnumerateInstances(“localhost”, “W3SVC”)
End If

End Function

Function MapServiceToInstance( Service )

If Service = “SMTPSVC” Then
MapServiceToInstance = “IIsSmtpServer”
ElseIf Service = “POP3SVC” Then
MapServiceToInstance = “IIsPop3Server”
ElseIf Service = “IMAP4SVC” Then
MapServiceToInstance = “IIsImapServer”
ElseIf Service = “W3SVC” Then
MapServiceToInstance = “IIsWebServer”
ElseIf Service = “NNTPSVC” Then
MapServiceToInstance = “IIsNntpServer”
ElseIf Service = “FTPSVC” Then
MapServiceToInstance = “IIsFtpServer”
End If

End Function

Function EnumerateInstances( Server, Service )
On Error Resume Next

Dim VirtualServerService
Dim Instance, InstanceID

EnumerateInstances = 1

Set VirtualServerService = GetObject(“IIS://” & Server & “/” & Service)

If Err.Number = 0 Then
InstanceID = MapServiceToInstance(Service)

For Each Instance in VirtualServerService

If Instance.KeyType = InstanceID Then

If SiteIsNotRunning(Instance.ServerState) Then
WScript.StdOut.Write “*”
EnumerateInstances = 0
End If

WScript.StdOut.Write Instance.ServerComment & ” (” & Service & “): ” & State2Desc(Instance.ServerState) & vbCRLF
End If
Next
End If

End Function

Function SiteIsNotRunning( nState )

If nState <> 2 Then
SiteIsNotRunning = 1
Else
SiteIsNotRunning = 0
End If

End Function

Function State2Desc( nState )

Select Case nState
Case 1
‘MD_SERVER_STATE_STARTING
State2Desc = “Starting”
Case 2
‘MD_SERVER_STATE_STARTED
State2Desc = “Running”
Case 3
‘MD_SERVER_STATE_STOPPING
State2Desc = “Stopping”
Case 4
‘MD_SERVER_STATE_STOPPED
State2Desc = “Stopped”
Case 5
‘MD_SERVER_STATE_PAUSING
State2Desc = “Pausing”
Case 6
‘MD_SERVER_STATE_PAUSED
State2Desc = “Paused”
Case 7
‘MD_SERVER_STATE_CONTINUING
State2Desc = “Continuing”
Case Else
State2Desc = “Unknown state”
End Select

End Function

 

You can also download the script from here.

Until next time,
Ingmar.

Announcing EventSentry v2.91

ingmar.koecher 0 Comment Announcements, Event Log, EventSentry, Tools & Utilities

Now that EventSentry v2.91 has been released, I’m happy to have the opportunity to blog about our monitoring solution again.

The most significant new feature in EventSentry is the Health Matrix, a new way to see your network status in a space-efficient way. In fact, you can see the overall health status of your entire network on a single screen, even if it consists of hundreds of hosts.

We also made numerous other changes throughout the web reports, and added some exciting new filtering capabilities with our event log filters, as well as improved speed with the event log engine and file checksum generations.

EventSentry v2.91 also includes many minor improvements throughout the application, including service monitoring, process tracking and more. We have also updated EventSentry Light, and a new version will be released in the coming days after we have completed testing.

But now to the new features in version 2.91:

Health Matrix
In the health matrix, each host is displayed as a colored square, circle or rectangle, with the color indicating the overall health of the monitored computer. When all of the monitored components of a host are in an OK status, the color of the square is green. The color will change to orange or red when a problem is detected, depending on the number or severity of the issue.

clip0580.pngThe health matrix is highly customizable, for example both the size and shape of the icons can be adjusted depending on the size of the network (and your monitor).

clip0583.pngEvent Log Monitoring
In 2.91, the event log filtering engine was improved, resulting in reduced CPU usage of the event log monitoring component. Since the CPU usage of the EventSentry agent is already quite low, you will most likely only notice this improvement on hosts that generate an extremely large number of events, such as domain controllers.

Also new is the ability to filter events based on insertion strings in addition to just filtering based on the event message text. This means that one can now match individual strings inside event messages against strings, numbers, file checksums and group memberships. If you are not familiar with the term “insertion string”, then I highly recommend my previous post about event message files before you read on.

Consider the following hypothetical example: The environment-monitoring component of EventSentry logs event id 10908:

The temperature (78.21 degrees F) has fallen outside the configured range (60F to 76F).

which is defined as:

The temperature (%3 degrees %4) has fallen outside the configured range (%1%4 to %2%4).

This event obviously informs us, that the current temperature has exceeded a set limit. Now let’s say that we wanted to get an email when the temperature exceeds the limit, but also send a page when the temperature exceeds 90 degrees.

The new filtering feature allows you to do just that, by using the numerical comparison functionality with insertion strings (of course you would also need to set the hour/day properties). Assuming that you already have a filter in place for regular email notifications, you would simply setup an additional include filter that would evaluate insertion string 3 (%3) and only match if the number is above 90. See the screen shot below for the example. The result is a filter that only matches when then the temperature exceeds 90 degrees.

blog_es291_filter_1a.png2.91 also includes two more comparison options, file checksums and group membership. So, if an insertion string represents a filename (e.g. from a security event), then EventSentry can create a SHA checksum from the specified file and compare it with the value that you specified. Another example would be a security event that includes a username in an insertion string, in which case you could setup a filter that would only match if that user is a member of particular group you specify. Both examples are mostly applicable for security events, since those are most likely to contain either filenames or usernames.
Using file checksums, you can be notified whenever a user plays solitaire, even when the user renames the executable.

blog_es291_filter_2.pngSimply create a checksum of the file first using shachecksum.exe (included in the free NTToolkit, make sure you account for different OS versions and platforms) and intercept the corresponding 4688 event.

Service Monitoring
Service Monitoring now collects the username as well as the executable of a service. These additional properties are available in the web reports and in events generated, for example when the username of a service changes.

blog_es291_service_monitoring.pngSoftware Monitoring
Software monitoring has been overhauled in 2.91, and some limitations and bugs have been removed. On Vista, Win2k8 and later, Windows patches are now monitored and included in the software inventory. 64-bit software is now classified as such and searchable, and searching for installed Windows Updated patches has also been simplified.

SNMP Traps
EventSentry can now send version 2c and version 3 traps, previously only version 1 traps were sent by the agent. The SNMP trap daemon was originally set to be released as part of 2.91, but this feature has been pushed back to v2.92.

Web Reporting
We have made a number of improvements in the web reporting to make using our web-based interface easier:

•    Reports are now easily accessible from every page, in addition to the reports p
age.
•    The database usage page now shows the actual page name in addition to the table name.
•    The dashboard page has been overhauled
•    The network status page can be customized (performance counters & disks)

blog_es291_dashboard.pngMiscellaneous Improvements

There have of course been other improvements across the board, such as:

•    Notes can now be applied to computers
•    AD-linked groups can be sorted, and authentication properties can be set globally
•    Hardware monitoring now includes the IP address of an interface
•    Process tracking can capture the command line of a process
•    Logon tracking includes group information
•    File checksum generation has been optimized and will now use fewer CPU resources (affects file monitoring and file access tracking)
•    The minimum database interval for environment monitoring has been reduced to 5 minutes from 15 minutes
•    Software uninstallation events now include the same information as software installation events

If you have an active maintenance agreement, then this 2.91 release will of course be free of charge. If you are not already using EventSentry, then you can download a free 30-day evaluation version from https://www.eventsentry.com/downloads_downloadtrial.php.

Happy Holidays,
Ingmar.

Auditing Changes to Microsoft SQL Server Database Tables

tames.mctigue 0 Comment Event Log, EventSentry, Tips & Tricks

Database servers store massive amounts of data, often including sensitive information. It is not uncommon for there to be databases holding millions of rows of data, where a small subset of rows are considered critical or sensitive. This could be anything from a Social Security number to an EventSentry entry of a security event. Being notified when existing data in your database changes is crucial for log data, and can be accomplished by using triggers with Microsoft SQL Server.

For those of you not familiar with triggers, a database trigger executes code in response to events on a table or database. Triggers are essentially hooks into a table, and they usually execute SQL statements as a response to another SQL statement.

Since we love the windows event log, we’ll take advantage of SQL Server’s ability for triggers to log an event to the event log when a row in a table is modified. This allows us to not only log that activity, but also get notified immediately when suspicious or important activity occurs in the EventSentry database.

In EventSentry, we have a table named ESEventlogMain that stores Windows event information. This table constantly gets new data inserted into it, and it often gets purged as well to manage the size of the database. However, there is no reason this data should ever be modified. If it is, then we know that something is amiss and we want to trigger an event in the event log. It is also useful to know what account made that change.

The first step is to create the message in SQL. You can use this SQL statement to create it:

sp_addmessage 80000, 10, ‘Data Integrity Alert: %s’, @with_log = TRUE

The first argument is a unique SQL server message ID that should be 50001 or higher, you can delete it again using sp_dropmessage. The number 10 is the severity level, but you can read more about the different options for sp_addmessage here.

Now we create the trigger that will use this message:


CREATE TRIGGER Trigger_ESEventlogMain_Modified ON
ESEventlogMain
FOR UPDATE
AS

IF UPDATE(eventmessage) OR UPDATE(eventid) OR UPDATE(eventtime) OR UPDATE(eventcomputer)
BEGIN

     DECLARE @Msg VARCHAR(8000)
     DECLARE @EventNumber INT
     DECLARE @EventID INT
     DECLARE @Computer VARCHAR(255)
     DECLARE @EventMessageOld VARCHAR(8000)
     DECLARE @EventMessageNew VARCHAR(8000)

     SET @EventNumber = (SELECT eventnumber from deleted)
     SET @EventID = (SELECT eventid from deleted)
     SET @Computer = (SELECT A.eventcomputer from ESEventlogComputer as A, deleted as B WHERE A.id = B.eventcomputer)
     SET @EventMessageOld = (SELECT eventmessage from deleted)
     SET @EventMessageNew = (SELECT eventmessage from inserted)

     SET @Msg = ‘ESEventlogMain modified by ‘ + CONVERT(VARCHAR(20), USER_NAME(USER_ID())) + ‘ at ‘ + CONVERT(VARCHAR(20), GETDATE()) + ‘. Computer: ‘ + @Computer + ‘, Event ID: ‘ + CONVERT(VARCHAR(8), @EventID) + ‘, Event Number: ‘ + CONVERT(VARCHAR(16), @EventNumber) + ‘, EventMessage (old) =’ + @EventMessageOld + ‘, EventMessage (new) = ‘ + @EventMessageNew

     RAISERROR( 80000, 10, 1, @Msg)
END

This creates a trigger which will generate an event when the eventmessage column in the ESEventlogMain table is modified. You can remove the “IF UPDATE(eventmessage) …” clause (as well as the BEGIN & END statements) if you want to be notified of any changes to that table, this might however create some noise since acknowledging events will also perform an UPDATE on this table.

FYI: “deleted” and “inserted” are keywords that refer to either the old
record that was updated (=deleted) or the new data (=inserted).

dbtriggers_event.jpgAs you can see from the screen shot above, the message text from a logoff event was renamed to “Trigger Test”. So now that the event is in the event log, we can set up a filter in EventSentry to alert us:

trigger_filter.pngEvents generated from triggers always have the event id 17061, so it’s a good idea to restrict the filter further using the “Content Filter” field. From now on, when the ESEventlogMain table is modified, we will get an entry in the event log as well as an email.
Just remember that any database administrator can delete or modify triggers, so it’s crucial that you keep dba access to your database as restricted as possible.

Please see the Table Relationships topic in the EventSentry help file for more information on the database tables used by EventSentry.

Best,
Tames, Ingmar + Ryan.

Read more