Headless Edgesight: Using Stored procedures to have key metrics emailed to you.

If you have been coming to the site now for the last few years you have probably noticed that we kind of specialize in a “Headless” Edgesight deployment. Dave, Alain and I all tend to steer away from the Edgesight console and focus more on running ad hoc queries or specialized reports. In this entry, I want to focus on leveraging stored procedures to provide you with the data you need via an email every morning so that you can validate a few things in your environment and perhaps put yourself in a position to “right the ship” before your onrush of users pile on the to the system at 8am.

What we will be covering today is coupling a stored procedure with an HTML formatted SQL Mail. To do this we require a few pre-requisites.

1.) You have SQL Mail configured (if you do not, there are better blogs than mine to walk you through that).

2.) That you have enough of a basic understanding of SQL Server to schedule a stored procedure to run. (or the wherewithal to follow the directions I provide).

While there are a number of canned reports in Edgesight, navigating through them or getting the exact metric you are looking for can be painstaking. Using the stored procedure method, you can schedule them to run and provide you with key metrics made up of those custom queries you choose in a format that will work both on your desktop outlook as well as be readable on your smart phone.

First Stored Procedure:

Title: SP_CTX_REBOOTS

Purpose: Most of us have a reboot regimen that we put our XenAPP Servers through. This query/stored proc will email you the status of your nightly reboots. It will be up to you and your operations staff to know which servers need to get rebooted but you can at least find out that 10 of your 25 xenapp servers IMA service did not start before you get your morning rush of users.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_REBOOTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
 --Author:        <John Smith>
-- Create date: <04/02/2012>
-- Description:   <Email Reboot Records>
-- =============================================
CREATE PROCEDURE [dbo].[SP_CTX_REBOOTS]
-- Add the parameters for the stored procedure here
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
declare @Result NVARCHAR(MAX) ;
declare @today datetime ;
set @today = convert(varchar, getdate(),111) ;
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +
 N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +
N'<th>Time</th>'+

N'<th>Machine</th>'+

N'<th>Status</th>'+

cast ((Select distinct td=  dateadd(hh,-5,time_stamp),           '               ',

td= Machine_name,             '               ',

td= Service_State_name,             '               '

from vw_ctrx_archive_service_state

where dateadd(hh,-5,time_stamp) > getdate()-1

order by machine_name

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Server Reboots',

@importance = 'High' ;

end ;

Second Stored Procedure:

Title: SP_CTX_Alerts

Purpose: This is actually a very valuable alert, this will provide you with the top errors/popups users receive when they are on your system. This is made up of both Windows and Application level alerts. I have noted a number of application (custom written errors) that show up here because they popped up during the users session. This can serve as a very valuable QA tool for both Systems Admins and Developers to run down the errors that they see.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_ALERTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:        <John Smith>

-- Create date: <04/02/2012>

-- Description:   <Email top Errors and popups>

-- =============================================

CREATE PROCEDURE [dbo].[SP_CTX_ALERTS]

-- Add the parameters for the stored procedure here

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Begin

DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @Result NVARCHAR(MAX) ;

declare @today datetime ;

set @today = convert(varchar, getdate(),111) ;

SET @tableHTML =

N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +

N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +

N'<th>EXE-Name(If Any)</th>'+

N'<th>Alert Text</th>'+

N'<th>Context</th>'+

N'<th>Total</th>'+

cast ((Select td= CASE

WHEN exe_name IS NULL Then 'None'

Else exe_name

END,          '               ',

td= replace(Alert_text,'°',''),             '               ',

td= Context_text,             '               ',

td= count(alert_text),             '               '

from vw_es_archive_alert

where dateadd(hh,-5,time_stamp) > getdate()-1

group by exe_name, alert_text, context_text

having count(alert_text) > 20

order by count(alert_text) desc

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Alerts and Popups',

@importance = 'High' ;

end ;

Scheduling the Stored Procedure:

Step 1: Add a new SQL Server Agent Job.

 

Step 2: Enter in the name of the Stored Procedure


Step 3: Got to “Steps” Configure the Steps by telling it to execute the stored procedure. (Set the Database Name to the Edgesight Database)


Step 4: Schedule the job to run so that the report is waiting for you when you get in that morning.

What does it look like: (You may have saw this at Briforum ’11)

Basically it is a very simple table that can auto size for your smart phone.

Time

Machine

Status

2012-04-05T02:03:15 SERVER19 Machine Boot
2012-04-05T02:05:06 SERVER19 Service Started
2012-04-05T02:03:48 SERVER20 Machine Boot
2012-04-05T02:05:40 SERVER20 Service Started
2012-04-05T02:15:33 SERVER21 Machine Boot
2012-04-05T02:17:27 SERVER21 Service Started
2012-04-05T02:04:28 SERVER22 Machine Boot
2012-04-05T02:06:19 SERVER22 Service Started
2012-04-05T02:05:55 SERVER23 Machine Boot
2012-04-05T02:07:47 SERVER23 Service Started
2012-04-05T02:06:44 SERVER24 Machine Boot
2012-04-05T02:08:36 SERVER24 Service Started
2012-04-05T02:07:38 SERVER25 Machine Boot
2012-04-05T02:09:33 SERVER25 Service Started
2012-04-05T02:10:08 SERVER27 Machine Boot
2012-04-05T02:12:07 SERVER27 Service Started
2012-04-05T02:10:38 SERVER28 Machine Boot
2012-04-05T02:12:31 SERVER28 Service Started
2012-04-05T02:11:46 SERVER29 Machine Boot
2012-04-05T02:13:35 SERVER29 Service Started
2012-04-05T02:12:55 SERVER30 Machine Boot
2012-04-05T02:14:45 SERVER30 Service Started
2012-04-05T02:02:28 SERVER35 Machine Boot
2012-04-05T02:04:18 SERVER35 Service Started
2012-04-05T02:04:59 SERVER35 Service Started
2012-04-05T02:10:50 SERVER35 Service Started

Conclusion:
So, the two queries included in the stored procedures are very useful but the point of the post this time is not to provide a useful query so much as it is to provide a useful method. If you have some rudimentary SQL Skills you should be able to reverse engineer this to include any custom Edgesight Query that you see on this post or that you have made up yourself. Sometimes it is nice to be able to have a simple, formatted report automatically sent to you, a customer or your boss so that they can get the key metrics they need on a consistent basis. I have found Edgesight to actually be more useful in a headless configuration (ad hoc and these stored procs) than to use the console.

Thanks for reading! If you are at Synergy and you see me (big ugly guy w/thick glasses) say “heya”

John

5 thoughts on “Headless Edgesight: Using Stored procedures to have key metrics emailed to you.”

  1. Hi John,

    I always look forward to your informative column … just a quick question …
    What ES version are the above stored procedures written for?
    I don’t seem to have a view called “vw_ctrx_archive_service_state” in my ES 5.3 deployment.

    Thanks,
    Sam

    1. I used a customer site for this instance and it is version 5.2, is there a chance it was upgraded and the new veiw was not set up?

      I would have to believe if it is in 5.2 it would be in 5.3 right?

      Good to hear from you again, did you submit for Briforum?

      Take care

      John

      1. I would *hope* that views would persist from version to version, but Citrix has done much stranger things. 🙂
        I submitted for BriForum Europe … didn’t make it, but I’ll still be submitting for Chicago… you?

        Best,
        Sam

  2. Can you show an example of the parameters you used in the line following the CREATE PROCEDURE line? Below it there is the comment to
    “Add the parameters for the stored procedure here” ?

    Not having SQL skills I’ve tried various ways to do that but always get errors.
    I was hoping this would work:
    CREATE PROCEDURE [dbo].[SP_CTX_REBOOTS]
    — Add the parameters for the stored procedure here
    (
    @machine_name NVARCHAR(30)=NULL,
    @service_state_name VARCHAR(29)=NULL,
    )
    AS

    I’ve also tried leaving off the =NULL part.

  3. I ran into an issue with the SP_CTX_Alerts stored procedure. I had to omit ‘TYPE’ from this line:
    FOR XML PATH(‘tr’), TYPE
    Then I was able to execute the stored procedure and e-mail the results.

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