Tag Archives: EdgeSight

Article: How to Truncate EdgeSight’s Alert and Alert_Param Table

[NOTE: I had to recently follow similar steps to reduce the size of my EdgeSight database to save disk space]

Document ID: CTX137750   /   Created On: Jul 8, 2013   /   Updated On: Jul 8, 2013

Summary

This document describes how to truncate EdgeSight’s alert and alert_param table. This operation deletes all the “stability” (such as agent’s windows logs like application, system, and security) data.

Requirements

There are many other conditions that will also justify the need to clean out the “stability” data. Following are some guidelines.
If you have an appropriate reason or requirement, skip the “Pre-requisite” section.

Pre-requisite

Required conditions (so you do not arbitrarily empty out the alert and alert_param table)-

  • Core_zbatch_run.log shows [alert] table grooming resulted in timeout (in excess of 240 minutes) error. The ES web console timeout, “Background Services Query” has default setting of 14400 (seconds, 60s/m * 60m/hr * 4hr).
    The setting can be found in “Configure (top tab) > Server Configuration (left tab) > Settings; Timeouts (main window tab).”
  • Many days of failure in core_zbatch_run.log.
  • The filegroup5 is very large, with alert and alert_param tables being excessively large.
  • The customer consent to having the data deleted.
  • And one of the conditions:
  • The database server had run out of disk space or is about to.
  • The “Background Services Query” timeout was extended to a larger value- and timeout failure continues.

Background

Preceding screenshot displays EdgeSight 5.4 Web Server database diagram for “alert” table and its related tables. The highlighted key is “alert_param” table references “alertid,” therefore this relationship must be broken when truncating the “alert” table.

Procedure

As a pre-requisite for the procedure, do this as late in the working day as possible, it will avoid the grooming job and most of the current day’s performance payloads will have been processed.

You must have access to the EdgeSight database. The step described utilizes the SQL Server Management Studio.

Complete the following steps to truncate the EdgeSight’s alert and alert_param table:

  1. Stop the EdgeSight Server’s “Citrix RSSH Admin Service” (it stops the dependent “Citrix RSSH Application Manager” service).

  1. Save a script to recreate the “FK_alert_param_alert” index in “alert_param” table. This script is used to restore the FK.
    Right click on FK_alert_param_alert and select Script Key as > CREATE To > New Query Editor Window.


  1. Drop the FK_alert_param_alert key.
    Right click on FK_alert_param_alert and select Delete.

  1. Click OK on the Delete Object form. (OK button is not displayed in the following screenshot).

  1. Obtain a New Query window. Run both SQL commands here truncate table alert_param and truncate alert.
    In this example, all four commands have been issued at the same time and verified that the tables are empty (0 rows).

  1. Switch back to the Query Window (for example “SQLQuery6.sql…”) to recreate foreign key. Click Execute button to execute the ALTER TABLE commands.

  1. Right click on FK_alert_param_alert and select Refresh.

  1. Expand Keys and verify the FK_alert_param_alert key is present.

  1. Start the EdgeSight Server’s Citrix RSSH Application Manager service (it starts the dependent Citrix RSSH Admin Service service).

More Information

CTX138381 – FAQ – Truncate EdgeSight’s Alert and Alert_Param Table

This document applies to:

Thanks,
Alain

Advertisements

EdgeSight: Timezone offsets

Intro

If you have implemented any of the ad hoc SQL queries available on this site, you may have noticed that most time queries are offset by –4 or –5 hours. This is because the EdgeSight database uses GMT to record time and I am located in the U.S. Eastern Time Zone.

In this post we will take a look at some tables in the EdgeSight database that you can use to make your queries more local and portable.

seamonsterThere Be Monsters Here!

Most of my experience with EdgeSight has been with the database views that summarize and organize the vast amount of data that EdgeSight collects. On occasion I’ve gone where few dare to tread to look directly at the tables for the data I need.

EdgeSight’s views are dizzying enough, but the table structure of the EdgeSight database is intimidating to the SQL neophyte. Despite this, I decided to look deeper after David did his post on session counts. His query uses the ‘timezone’ table to find the time offset for his query and this got me curious. How can I use this to make my queries easier to maintain and more portable?

Timezone table

Lets take a look at the timezone table

SELECT *
FROM timezone

image

The above picture is only part of the table. It consists of 74 rows. Yeah makes total sense right? Naturally, I had to do some more checking. If you check the company table, we get a clue.

SELECT *
FROM company

image

As you can see in the above picture, each company in the EdgeSight database has an associated Time Zone and Language. In this case, we have a timezone id (tzid) of 13 and a culture_name of en-US. If we cross reference the tzid with the timezone table we get:

image

Looking at the result above, we can see that this is for the U.S. Eastern time zone and includes daylight savings time as well. You can configure this in the EdgeSight console by clicking on the Configure tab. Look under the Server Configuration section and click on Companies to see where to add/edit company information.

image

So for the example above, I have the language set to English and the time zone set to U.S. Eastern Time which has a GMT offset of –5 hours.

How does this help me?

Let’s take a look at a query I’ve posted on this site before:

DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111), 'username'

As you can see above, all the timedate fields are offset by –4 hours. To keep from having to change the offset to –5 or –4 depending on what time of year it was (standard vs. daylight savings time), I developed a simple select query that determines the current offset by checking the timezone table.

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13

In layman’s terms, look at the timezone table where the timezone id (tzid) is equal to 13. If the field ‘use_daylight’ is equal to zero, use the ‘standard_bias’ otherwise use the ‘daylight_bias’.

I’m setting whatever this query returns equal to the variable @tzbias. I then use the @tzbias variable in my timedate fields in my queries. If we rewrite the above query with the tzbias variable, we get the following:

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(mi,@tzbias,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111), 'username'

Since the timezone bias is in minutes, I had to change the DATEADD functions to use mi for minutes. Now I can use my queries year around without worrying about daylight savings time changes.

I hope this provides you some options when doing ad hoc queries against the EdgeSight database. As always, I welcome all comments and questions.

Thanks,
Alain

Are You There EdgeSight? It’s me Worker

Intro

If you rely on EdgeSight to provide accurate and timely information about your farm you have to assume that all your EdgeSight Worker Agents are functioning as expected.  Or do you?  In this post, we will review the information that the EdgeSight console provides you as well as creating a dashboard that can give you a detailed information on your EdgeSight Worker agents.

EdgeSight Console: Configuration Tab

The first place you can review the health of your EdgeSight server and its agents in under the Configuration Tab

image

Along the left-hand side of this screen you will see way to configure your workers, alerts, and other server settings. We’re going to spotlight some items under Server Configuration and Server Status. image

Server Configuration: Status
Your first overview of server health comes when you click on Status under Server Configuration.

image

The first line lists the workers that were and were not updated in the current 24 hour period as well as newly added workers.  Right away you see (in this case) that 48 workers updated and 32 did not.  That’s a large portion of EdgeSight agents that have not uploaded their data into your database and therefore any reports you are running will not include these systems.  The question becomes which systems did not update and why?

ES_ZQUEUE…Gesundheit!

The service on the EdgeSight server that processes payloads from the worker agents is the es_zqueue (seen under Server Script Host Status).  This process is not reporting any issues and there are no pending payloads to process (we’ll look at this more later).

Server Status: Messages
Message Status lists all the system messages generated by EdgeSight.  This includes Agent errors, payload errors, and new agents alerts to name a few.

image

Here you will see which servers had a payload issue (Data Upload), but not a reason why systems have not updated the database.

Server Status: Server Script Host
Clicking on this in your EdgeSight Console will show you the following screen:

image

Here you will see the various modules that keep the EdgeSight database updated, cleaned, and running smoothly. The core_zpd_loader 1 and 2 manage the data payloads from devices with the EdgeSight agent including errors. Clicking on the triangle will reveal the following menu.

image

Clicking on View Log will allow us to investigate why a payload might have failed or created an error.

4/13/2012 5:18:03 AM: PayloadLoader: Starting payload load for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd
4/13/2012 5:18:11 AM: PayloadLoader: Payload load completed with errors for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd. Error: -2146233088: Citrix.EdgeSight.Loader. System.Exception

As we can see if this example, the payload completed with an error and we can try searching Citrix to see if there is a resolution related to this error, but we do not see which server failed to upload any data.

I’ve walked through the diagnostic information that is available in the EdgeSight console to show that we still do not have a clear sign of which servers have updated the EdgeSight database recently. To address this issue, I did some digging around in the EdgeSight database and created a query that links the instance, machine, and OS_version tables.

The Query

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
SELECT    i.instid, m.name as 'System', ip_address AS 'IP', product_version AS 'ES Version',
CASE dept_set_type    WHEN 1 THEN 'XenApp' WHEN 2 THEN 'Endpoint' END AS 'ES Agent',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_sync),100) AS 'Last Sync',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_config_start),100) AS 'Last Config Check',
CONVERT(DECIMAL(19, 2),(last_db_size/1048576.0)) AS 'Last FBDB Size (MB)',
CASE o.short_name    WHEN 'Windows Server 2008' THEN 'W2K8'
WHEN 'Windows Server 2008 R2' THEN 'W2K8R2'
WHEN 'Windows Server 2003' THEN 'W2K3'
WHEN 'Windows XP'           THEN 'XP'
ELSE 'Other' END AS 'OS',
CASE o.ptype        WHEN 'Standard x64 Edition' THEN 'Std x64'
WHEN 'Professional'         THEN 'Pro'
WHEN 'Enterprise Edition'   THEN 'Ent'
WHEN 'Standard Edition'     THEN 'Std'
WHEN 'Enterprise x64 Edition' THEN 'Ent x64'
ELSE 'Other' END AS 'Edition',
sp_level,
CONVERT(VARCHAR,DATEADD(mi,@tzbias,tstamp),111) AS 'Date Added',
cps_farm_name,i.cps_product_name, i.cps_product_version, i.cps_product_service_pack
FROM instance i,machine m, os_version o
WHERE m.machid=i.machid and i.osid = o.osid
ORDER BY dateadd(mi,@tzbias,last_sync) DESC

The Report

image

Click on the image to see a larger version..

With this dashboard (I created it based on the query above in SQL Reporting Services) you can quickly see which servers have updated  the database (Last Sync) and which have updated their local EdgeSight worker agents (Last Config Ck). Armed with this information you can review your EdgeSight Agent worker schedules or check the agent on the system in question to make sure it is communicating with the EdgeSight server.

As always I welcome all questions and comments.

Thanks,
Alain