Test Queries

The following are example test queries you can use to test your Hyperic Metric Data replication.

Query 1: Show all the disk stats (replace your.platform.name)

Select p.fqdn,
	svc.name,
	s.name,
	t.name,
	d.value,
	d.timestamp
from	EAM_MEASUREMENT_TEMPL t,
	EAM_MEASUREMENT m,
	EAM_MEASUREMENT_DATA d,
	EAM_SERVICE svc,
	EAM_SERVER s,
	EAM_PLATFORM p
where	t.id = m.template_id
	and m.id = d.measurement_id
	and p.id = s.platform_id
	and s.id = svc.server_id
	and svc.id = m.instance_id
	and lower(p.fqdn) = 'your.platform.name'
	and lower(t.name) like '%disk%' /* lower(t.alias) works here as well */
order by d.timestamp desc;

This query shows all metrics for any metrics with the word "disk" in the name.

Query 2: Availability/CPU/Memory/Network information per Platform

SELECT platform.fqdn as platform
,template.name as template
,data.value
,data.timestamp
FROM EAM_MEASUREMENT_TEMPL template
,EAM_MEASUREMENT measurement
,EAM_MEASUREMENT_DATA data
,EAM_PLATFORM platform
WHERE 1=1
AND template.id = measurement.template_id
AND measurement.id = data.measurement_id
AND platform.id = measurement.instance_id
AND lower(platform.fqdn) = 'your.platform.name'
AND lower(template.name) = 'availability'
ORDER BY data.timestamp desc;

In this use case, the above query returns the Availability metrics for a named Platform(EAM_PLATFORM.FQDN). The relationship between Platform and detailed metric (EAM_MEASUREMENT_DATA) is made via the measurement header table (EAM_MEASUREMENT). The INSTANCE_ID column of EAM_MEASUREMENT matches multiple object types (e.g. Platform, Server, Service), so joining on a given objects ID will allow any detailed metric data available for that object to be selectable. The metric template table (EAM_MEASUREMENT_TEMPL) acts as a filter for specific metric types. This table contains all the metric types in the system, so full or partial matching on the name of the template will filter results for that type of metric. There is no specific "metric type" column that allows for true categorical filtering, so matching on name is currently the best method of filtering.

Query 3: Disk usage per FileServer Service

SELECT platform.fqdn as platform
,service.name as service
,server.name as server
,template.name as template
,data.value
,data.timestamp
FROM EAM_MEASUREMENT_TEMPL template
,EAM_MEASUREMENT measurement
,EAM_MEASUREMENT_DATA data
,EAM_SERVICE service
,EAM_SERVER server
,EAM_PLATFORM platform
WHERE 1=1
AND template.id = measurement.template_id
AND measurement.id = data.measurement_id
AND platform.id = server.platform_id
AND server.id = service.server_id
AND service.id = measurement.instance_id
AND lower(platform.fqdn) = 'your.platform.name'
AND lower(template.name) like '%disk%'
ORDER BY data.timestamp desc;

In the above query, the import join is Platform -> Server -> Service (highlighted). This
relationship allows you to "go up the stack" to select metrics from Servers or Services of interest. In most cases, Platform metrics are very specific to the hardware attributes of a given system running the HQ agent (e.g. CPU, Memory, Network) but disk metrics are a bit different. Disks are seen as Services of the FileServer Server type. Therefore disk metrics are accessible from the Services layer of a given Platform. As you can see, the join from Service to metric is the same as Platform to metric (on object ID and measurement INSTANCE_ID).

Query 4: Application/Web Component Service usage information for Server/Service

SELECT platform.fqdn as platform
,service.name as service
,server.name as server
,template.name as template
,data.value
,data.timestamp
FROM EAM_MEASUREMENT_TEMPL template
,EAM_MEASUREMENT measurement
,EAM_MEASUREMENT_DATA data
,EAM_SERVICE service
,EAM_SERVER server
,EAM_PLATFORM platform
WHERE 1=1
AND template.id = measurement.template_id
AND measurement.id = data.measurement_id
AND platform.id = server.platform_id
AND server.id = service.server_id
AND service.id = measurement.instance_id
AND lower(platform.fqdn) = 'your.platform.name'
AND lower(server.name) like '%jboss%'
AND lower(template.name) like '%transaction count%'
ORDER BY data.timestamp desc;

Once the appropriate join has been made to access the Server or Service layer, filtering by Server/Service name or metric template is the easiest way to select specific metrics of interest per Server/Service. For example, JBoss is a Server while the individual Web Apps running within the container are Services. Metrics specific to the JBoss container are available from the Server layer while the internal Web Apps are available via the Service layer.