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.	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.	entity_type

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.