Retrieve accounting data

This page describes some queries that you can use to view aggregate resource usage.

You can use them as templates for your own queries to retrieve data from the replicated accounting database.

It is important to avoid running queries on the accounting_event_detail table. Queries on this table may negatively affect the Abiquo Server.

You should replicate the accounting data to allow user access through accounting queries. See Kinton Database replication for further information.

Previously the accounting system used views, which have been maintained for legacy applications only. See Abiquo accounting architecture.

Template for query to view HOURLY USAGE SUM

SELECT
  v.startTime AS startTime,
  v.endTime AS endTime,
  v.idAccountingResourceType AS idAccountingResourceType,
  v.resourceType AS resourceType,
  SUM(v.resourceUnits) AS resourceUnits,
  v.idEnterprise AS idEnterprise,
  v.idVirtualDataCenter AS idVirtualDataCenter,
  v.enterpriseName AS enterpriseName,
  v.virtualDataCenter AS virtualDataCenter
FROM
  kinton_accounting.HOURLY_USAGE_MAX_VW v
WHERE
  # APPLY CUSTOM QUERY FILTERS HERE, E.G. BY DATE RANGE AS BELOW
  v.startTime >= '2012-04-16' AND v.endTime < '2012-04-17'
GROUP BY
  v.startTime,
  v.idAccountingResourceType,
  v.idEnterprise, v.idVirtualDataCenter;

Template for Query to view DAILY USAGE SUM

SELECT
  CAST(a.startTime AS DATE) AS startTime,
  CAST(a.startTime AS DATE) AS endTime,
  a.idAccountingResourceType AS idAccountingResourceType,
  a.resourceType AS resourceType,
  SUM(a.resourceUnits) AS resourceUnits,
  a.idEnterprise AS idEnterprise,
  a.idVirtualDataCenter AS idVirtualDataCenter,
  a.enterpriseName AS enterpriseName,
  a.virtualDataCenter AS virtualDataCenter
FROM
  kinton_accounting.HOURLY_USAGE_MAX_VW a
WHERE
  # APPLY CUSTOM QUERY FILTERS HERE, E.G. BY DATE RANGE AS BELOW
  a.startTime >= '2012-04-16' AND a.endTime < '2012-04-17'
GROUP BY
  CAST(a.startTime AS DATE),
  a.idAccountingResourceType,
  a.idEnterprise, a.idVirtualDataCenter;

Template for query to view MONTHLY USAGE SUM

SELECT
  CAST((a.startTime - INTERVAL (DAYOFMONTH(a.startTime) - 1) day) AS DATE) AS startTime,
  LAST_DAY(a.startTime) AS endTime,
  a.idAccountingResourceType AS idAccountingResourceType,
  a.resourceType AS resourceType,
  SUM(a.resourceUnits) AS resourceUnits,
  a.idEnterprise AS idEnterprise,
  a.idVirtualDataCenter AS idVirtualDataCenter,
  a.enterpriseName AS enterpriseName,
  a.virtualDataCenter AS virtualDataCenter
FROM
  kinton_accounting.HOURLY_USAGE_MAX_VW a
WHERE
  # APPLY CUSTOM QUERY FILTERS HERE, E.G. BY DATE RANGE AS BELOW
  a.startTime >= '2012-04-01' AND a.endTime < '2012-05-01'
GROUP BY
  CAST((a.startTime - INTERVAL (dayofmonth(a.startTime) - 1) day) AS DATE),
  a.idAccountingResourceType,
  a.idEnterprise, a.idVirtualDataCenter;