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:
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):
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:
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:
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:
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:
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:
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.
Value for Value
If you received any value from reading this post, please help by becoming a supporter.
Thanks for reading,
Alain