Reporting on Alerts

Intro

EdgeSight allows to you to create alerts that trigger on many criteria.  In this post, we will configure an alert and show how to query the database directly to get this information.

Creating an alert

For the purposes of this post, I have created a Process Hung alert for outlook.exe.  This is a built-in Application Error alert that can trigger on the EXE file name, the application description, the process file version, and/or the process company name.  The actual alert will show up in the Farm Monitor and Alert List view under the Monitor Tab in the EdgeSight console.

Now you will get a near real-time alert in the console that looks like this:
image

I found that this alert triggered quite often and while you can use the “Process Not Responding Alert” report, this blog is all about pulling back the veil.

The Query

We will use the VW_ES_ARCHIVE_ALERT view for this query.  Here is an example of all the columns in this view (customer specific information hidden):

image

For our purposes, I want to get the date of the alert, the machine name, the username, the process name, the process description, and the actual text of the alert.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name,alert_text
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

This gives me:
image

If you look at the alert_text field, you will see some information that doesn’t look right.  You can see “Microsoft Office Outlook”, a weird character, and a series of numbers.  These numbers are in fact the actual process hang measured in milliseconds.  You can see this if you go back to the farm monitor and select the detail for an alert:

image

You have the information you need to determine who is having a real long delay, but how can we sort or organize this delay information.  There is no built-in MSSQL function to break this column up into two useful fields.  A Google search pointed me to a user-written function that will strip non-alphanumeric from a column.

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression =  '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END

Once you execute this in the MSSQL Management Studio, you can reference the function in your query:

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, dbo.fn_StripCharacters(alert_text, '^a-z0-9')
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

This now gives us:
image

Now the special character is gone, but how can you split the process delay out of the column?  You can use a built-in MSSQL function call SUBSTRING.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, SUBSTRING(dbo.fn_StripCharacters(alert_text, '^a-z0-9'),23,6) AS 'Delay'
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

Now we get:

image

To finish up, we’ll divide the Delay by 1000 to get the delay in seconds.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, CONVERT(INTEGER,SUBSTRING(dbo.fn_StripCharacters(alert_text, '^a-z0-9'),23,6),10)/1000.0 AS 'Delay'
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)
ORDER BY 'Delay' desc

Our end result:

image

With this information, you can do further manipulation including counting the number of alert instances for a user or tracking a single user over time.

As always I welcome all questions and comments.

Thanks,
Alain

2 thoughts on “Reporting on Alerts”

  1. Thanks for this post, always appreciate the value add this blog brings to edgesight. Wish this was a standard report in the product.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s