Building Custom EdgeSight Reports Part 2 - The DatabasePublished on 16 Jul 2010
Tags #EdgeSight#SQL Server
Understanding the database schema is the key to building a custom report for EdgeSight. In this article you will learn about important tables and views as well as the relationship between them. This article explains the purpose and layout key tables in the EdgeSight database schema as well as the relationships between them.
As you may know, EdgeSight manages devices in companies and departments to represent an organizational structure. The corresponding tables are called company and dept. Every company is represented (among others) by a unique id (
compid) and a name (
name) contained in the corresponding fields. Departments are stored in the table dept where a unique id (
compid), the name (
name), the parent department (
path) and the id if the corresponding company (
compid) are the most important fields.
EdgeSight differentiates between the instance of an agent and the machine which the agent is installed on. The former is stored in the table called instance and the latter is found in the table called
machine. The machine is mainly represented by a unique id (
machid), the name (
name) and the physical memory (
mem_physical). Every agent instance is referenced by a unique id (
instid) and lists the corresponding department (
deptid), the hosting machine (
machid) and the timezone (
tzid). The table
timezone contains all timezones expressed by a unique id (
tzid) and an offset relative to the universal time convention (UTC). The handling of timezones will become a very important piece in the creation of queries as timestamps are stored in UTC and need to be converted to the local timezone.
The following image displays the relationship between the tables introduced above:
Details about the organizational tables called
vendor will be explained in the next section as the relationship will become more obvious.
Performance data is stored in several tables depending on the type of data:
system_perfstores performance data about the machine which the agent is installed on.
image_perfcontains process performance data.
ctrx_system_perfadds several important performance values about the remote desktop services.
ctrx_session_perfstores performance data about user sessions.
Unfortunately, these tables are not suited for analysis as fields have cryptic names. Therefore, the corresponding views should be used instead (prefixed with “vw_”).
The views for retrieving performance data have a very similar structure. They all contain a timestamp (field
dtperiod) expressed in UTC, a reference to the corresponding agent instance (field
instid) and a set of data items expressed by several fields:
- *_sum – the sum of all individual values aggregated by the specific value.
- *_cnt – the number of values aggregated by the sum above.
- *_peak – the peak value aggregated in the sum above.
Storing this information separately is necessary for proper statistical analysis. Don’t calculate the mean of several mean values but rather add the sums and divide it by the added number of values.
The following views follow the general schema described above:
vw_system_perf- This table stores general performance data about the machine which the agent is installed on like processor usage and available memory.
vw_image_perf– This table contains process performance data.
vw_ctrx_system_perf– This table adds several important performance values about the remote desktop services.
vw_ctrx_session_perf– This table stores performance data about user sessions.
The following image displays the relationship between the tables containing performance data.
Process performance data from
vw_image_perf requires two additional tables to resolve the data contained therein:
vendor. Detailed information about processes and vendors are separated from the performance data to prevent redundant data to be stored. A process is represented by an entry in the table
image encompassing a unique id (
imid), the executable name (
filename), the version of the file (
file_version) and a reference to the vendor (
vendid). A vendor is stored in the table
vendor by a unique id (
vendid) and a name (
name). The following image displays the relationship between the tables
Apart from relating performance data through the agent instance (table
instance), tables like
vw_ctrx_session_perf contain information about individual user sessions. The following image displays the relationship between tables based on user sessions.
principal are used to resolve the session if (
sessid) found in
vw_ctrx_session_perf. The table
session contains a list of all sessions perceived by the agents. The account information for a session is stored in the table
principal being referenced by
prid from the table
Process performance data can also be accredited to user sessions by resolving the field
Date and Time Conversion
As all data items stored in the EdgeSight database are expressed in UTC, the table
rs_calendar supports handling date and time values. It contains a list of all years, months, days and hours nearly since the beginning of time ;-) Although all data found in this table can be calculated on-the-fly, it is way faster to read it from a table.
Wrapping It Up
In the next article, I will demonstrate building a query based on these tables.