Metric table
This table contains metric values for entities that appeared in the topology. Most metrics are values that are associated with commodities bought or sold by the entity. Bought and sold commodities appear in separate records - one for the buyer and one for the seller. In addition to topology metrics, this table contains headroom properties, which are generated daily by headroom plans.
Data Object |
Type |
Nullable |
Primary |
Description |
Reference |
---|---|---|---|---|---|
time |
timestamp with time zone |
False |
False |
When analysis discovered thIS metric. For headroom metrics, when the headroom plan was created. |
|
entity_oid |
bigint |
False |
False |
The ID of the entity this metric pertains to. |
|
type |
enum |
False |
False |
The metric type. |
|
provider_oid |
bigint |
True |
False |
The ID of the entity that provides this metric. For example, for VMem, the ID of the host that provides the memory. |
|
key |
text |
True |
False |
The commodity key. This value can be null if no key is present. |
|
current |
double precision |
True |
False |
Current utilization of commodity in provider entity. |
|
capacity |
double precision |
True |
False |
The capacity of the commodity in the provider entity. |
|
utilization |
double precision |
True |
False |
Overall utilization of the commodity in the provider entity. |
|
consumed |
double precision |
True |
False |
Amount of commodity currently used by consumer entity. |
|
peak_current |
double precision |
True |
False |
Peak amount of current used commodity in the provider entity. |
|
peak_consumed |
double precision |
True |
False |
Peak amount of commodity currently used by the consumer entity. |
|
entity_type |
enum |
False |
False |
The type of the consumer entity. |
Sample use cases
VMem utilization for a Virtual Machine over time:
This query returns all the VM memory metrics that are collected for a specified virtual machine for a time interval. The time interval is truncated to the hour.
SELECT m.time, avg(m.utilization) AS avg_util
FROM metric m,
scope_to($__timeFrom()::timestamptz - INTERVAL '24 hour', date_trunc('hour', $__timeTo()::timestamptz),
'VIRTUAL_MACHINE', ARRAY[0]::bigint[], '0' = '0') s
WHERE m.entity_oid = s.oid
and m.time BETWEEN s.from_time AND s.to_time
AND m.type = 'VMEM'
AND m.provider_oid IS NULL
AND m.time BETWEEN $__timeFrom()::timestamptz - interval '24 hour' AND date_trunc('hour', $__timeTo()::timestamptz)
GROUP BY 1
Storage Amount of Physical Machines Running On a Cluster:
This query returns the max values of storage amounts for all the physical machines running in
a cluster. The only parameter that is given to the query other than the time range is the
cluster_id
. An internal function in Postgres can convert the
cluster_id
to all the OIDs of the physical machines.
SELECT
date_trunc('day', m.time) as time,
m.entity_oid,
MAX(current) FILTER (WHERE m.type = 'STORAGE_AMOUNT') as stor_used,
MAX(capacity) FILTER (WHERE m.type = 'STORAGE_AMOUNT') as stor_cap,
MAX(current) FILTER (WHERE m.type = 'STORAGE_PROVISIONED') as stor_prov
FROM
metric m,
scope_to($__timeFrom(), $__timeTo(), 'STORAGE', ARRAY[$CluserId+0]::bigint[], $CluserId = '0') s
WHERE
m.entity_oid = s.oid
AND m.time BETWEEN s.from_time AND s.to_time
AND m.type in ('STORAGE_AMOUNT', 'STORAGE_PROVISIONED')
AND m.time between $__timeFrom() and $__timeTo()
GROUP BY 1, 2
Physical Machine Average Memory Utilization - Week Over Week By Day:
Returns the average daily memory utilization for virtual machines both for the current week and the past week.
WITH metrics AS (
SELECT m.time, m.utilization
FROM metric m,
scope_to($__timeFrom()::timestamptz - INTERVAL '7 day', $__timeTo(), 'PHYSICAL_MACHINE', ARRAY[0]::bigint[], '0' = '0') s
WHERE
m.entity_oid = s.oid
AND m.time BETWEEN s.from_time AND s.to_time
AND m.type = 'MEM'
AND m.time BETWEEN ($__timeFrom()::TIMESTAMPTZ - interval '7 day') AND date_trunc('day', $__timeTo()::TIMESTAMPTZ)
GROUP BY m.time, m.entity_oid, m.utilization
),
DATA AS (
SELECT m.time, avg(m.utilization) AS avg_util
FROM metrics m
GROUP BY m.time
)
SELECT
INTERVAL '1 DAY' + CASE
WHEN time BETWEEN date_trunc('day', $__timeFrom()::TIMESTAMPTZ - interval '7 day')
AND date_trunc('day', $__timeTo()::TIMESTAMPTZ - interval '7 day')
THEN date_trunc('day', time + interval '7 day')
ELSE date_trunc('day', time)
END as "time",
CASE
WHEN time BETWEEN date_trunc('day', $__timeFrom()::TIMESTAMPTZ - interval '7 day')
AND date_trunc('day', $__timeTo()::TIMESTAMPTZ - interval '7 day')
THEN 'Previous Week'
ELSE 'Week Ending ' || DATE($__timeTo()::TIMESTAMPTZ)::TEXT
END as metric,
avg(avg_util)
FROM data
GROUP BY 1, 2
ORDER BY 1, 2
Representation of Powered Off Virtual Machines:
Ideally, metrics of a turned off virtual machine are still written on the table, assuming that the probe sends them in the entity dto. Most used values for commodities have NULL values, instead of having 0’s. This configuration is needed to not affect potential averages over time, while the capacity has the same value, independent of the state of the machine. Still, some used values for commodities aren’t NULL, such as storage, since the machine consumes those commodities even in a powered off state.