metric Table

This table contains metric values for entities that appeared in the topology. Most metrics are values 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.

metric

metric table

Column Name

Type

Nullable

Primary

Description

time

timestamp with time zone

false

false

Timestamp on the topology from which this metric was obtained. For headroom metrics this is the time at which the headroom plan was created.

entity_oid

bigint

false

false

Oid of the entity to which the metric applies.

type

metric_type

false

false

The type of the metric.

provider_oid

bigint

true

false

Oid of the seller of this commodity.

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 the commodity on the selling entity

capacity

double precision

true

false

Capacity of the commodity on the selling entity

utilization

double precision

true

false

Utilization of the commodity on the selling entity.

consumed

double precision

true

false

Amount of the commodity currently used by the buying entity

peak_current

double precision

true

false

Peak amount of the commodity of the selling entity

peak_consumed

double precision

true

false

Peak amount of the commodity currently used by the buying entity

entity_type

entity_type

false

false

The type of the entity in the entity_oid column.

Sample Use Cases

VMem utilization for a Virtual Machine over time:

This query returns all the VM memory metrics collected for a specified virtual machine for a given 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. Note that the only parameter given to the query other than the time range, is the cluster_id. We then have an internal function in Postgres that is able to translate 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 will still be written on the table, assuming that the probe will send them in the entity dto. Most used values for commodities will have NULL values, we do this, instead of having 0’s, in order not to affect potential averages over time, while the capacity should have the same value, independent of the state of the machine. Still, some used values for commodities won’t be NULL, such as storage, since those are consumed by the machine even in a powered off state.