Abiquo Documentation Cookies Policy

Our Documentation website uses cookies to improve your experience. Please visit our Cookie Policy page for more information about cookies and how we use them.


Abiquo 5.0

Skip to end of metadata
Go to start of metadata

This section provides reference material regarding the 'kinton_reporting' schema row-level security (RLS) procedures and tables. It is intended to be a reference guide for customers who are writing their own reports using Abiquo data.

Core RLS Stored procedures

InitialiseRLS

InitialiseRLS(rls_abiquo_user VARCHAR(128))

Used to initialise DBMS-based RLS. MUST be run before referencing any of the RLS views, otherwise the views will be empty or may contain 'out of date' information.
This stored procedure should be called as the first operation in any report stored procedure.
Note: The supplied 'rls_abiquo_user' is the abiquo user name, not a DBMS user name.
e.g.

CALL kinton_reporting.InitialiseRLS('admin');

CleanupRLS()

This procedure should be a corresponding call to InitialiseRLS(), to be made once all queries have been run. It prevents the underlying RLS tables from gathering orphaned data.
e.g.

CALL kinton_reporting.CleanupRLS();

TruncateRLSTables

This SP is provided to remove any orphaned RLS data form the RLS tables, however please note it it removes ALL data from the RLS tables, and should be called no more than periodically, to clean up any orphaned data. Additionally, this procedure should only be called when no RLS users are active on the system.
e.g.

CALL kinton_reporting.TruncateRLSTables();

Core RLS Views

These views provide the low-level 'core' RLS filtering, and are often used by the views in the following sections to perform RLS filtering.

RLS_ENTERPRISE_V1_0_VW

This view provides a filtered version of the kinton.enterprise table. Currently, a user can only see the Enterprise to which they belong, unless they have the 'ENTERPRISE_ENUMERATE' privilege, in which case they can see all Enterprises.

Column Name

SQL Type

Description

kinton.enterprise.*

*

All columns from the kinton.enterprise table

RLS_VDC_V1_0_VW

This view provides a filtered version of the kinton.virtualdatacenter table. Users can only see the virtual datacenters which they have been granted access, unless they have the 'USERS_PROHIBIT_VDC_RESTRICTION' privilege, in which case they can see all virtual datacenters in each Enterprise they are entitled to see.

Column Name

SQL Type

Description

kinton.virtualdatacenter.*

*

All columns from the kinton.virtualdatacenter table

RLS_DATACENTER_V1_0_VW

This view provides a filtered version of the kinton.datacenter table. Currently a user can see no datacenter unless they have the 'PHYS_DC_RETRIEVE_DETAILS' privilege, in which case they can see all datacenters.

Column Name

SQL Type

Description

kinton.datacenter.*

*

All columns from the kinton.datacenter table

RLS_ENTERPRISE_LIMITS_BY_DATACENTER_V1_0_VW

Not really a 'core' view, this provides filtered access to the kinton.enterprise_limits_by_datacenter table. Rows are filtered according to the datacenters and the enterprises the user can access.

Column Name

SQL Type

Description

kinton.enterprise_limits_by_datacenter.*

*

All columns from the kinton.enterprise_limits_by_datacenter table

RLS_USER_V1_0_VW

This view provides a filtered version of the kinton.user table. The users returned are restricted to those that are associated with the Enterprises that the user can access.

Column Name

SQL Type

Description

kinton.user.*

*

All columns from the kinton.user table

RLS_VAPP_V1_0_VW

This view provides a filtered version of the kinton.virtualapp table. The virtualapps returned are restricted to those that are associated with the Enterprises and VDCs that the user can access.

Column Name

SQL Type

Description

kinton.virtualapp*

*

All columns from the kinton.virtualapp table

VM-centric RLS Views

This group of views return data centered around virtual machines, and their resources.
Unless stated otherwise, each view applies RLS by VDC restriction.

RLS_VIRTUAL_MACHINE_V1_0_VW

This view returns a list of all VMs (and their detailed attributes, including VDC and VApp membership).

Column Name

SQL Type

Description

kinton.virtualmachine.*

*

All columns from the kinton.virtualmachine table

idVirtualApp

INTEGER

ID of the virtual Appliance the VM belongs to

ApplianceName

VARCHAR

Name of the virtual Appliance the VM belongs to

idVirtualDataCenter

INTEGER

ID of the VDC to which the VM belongs

RLS_SUM_VM_IN_USE_BY_VDC_V1_0_VW

A view that returns a sum of all hypervisor-deployed VM resource usage (CPU, Memory, Local Storage), totalled by VDC.

Column Name

SQL Type

Description

idVirtualDataCenter

INTEGER

ID of the VDC whose VM resources are being summed

idEnterprise

INTEGER

ID of Enterprise to which the VDC belongs

cpuused

DECIMAL

Total number of deployed CPU cores used by the VDC

ramused

DECIMAL

Total amount of memory used (in MB) by the VDC

hdUsed

DECIMAL

Total amount of local hypervisor disk used (in bytes) by the VDC

RLS_SUM_VM_RESOURCES_BY_STATE_BY_VDC_V1_0_VW

View that returns a breakdown of VM resources by their state, per VDC.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise to which the VDC belongs

idVirtualDataCenter

INTEGER

ID of the VDC whose VM resources are being summed

VMState

VARCHAR

Textual description of the VM state

TotalVMs

INTEGER

The total number of VMs in that state within the VDC

CPUUsed

DECIMAL

The number of CPU cores used by VMs in that state within the VDC

RAMUsed

DECIMAL

The amount of memory (in MB) used by VMs in that state within the VDC

HDUsed

DECIMAL

The amount of local hypervisor disk used (in bytes) by VMs in that state within the VDC

RLS_SUM_VM_DETAILS_V1_0_VW

View that returns physical & logical VM details, plus numerous other VM details (but not including network or storage tier VM usage). One row per VM.
RLS is by ENTERPRISE, VDC and DATACENTER restriction.

Column Name

SQL Type

Description

idDataCenter

INTEGER

ID of Datacenter hosting the VM

Datacenter

VARCHAR

Name of Datacenter hosting the VM

idRack

INTEGER

ID of the Rack hosting the VM

Rack

VARCHAR

Name of the Rack hosting the VM

idPhysicalMachine

INTEGER

ID of the Physical Machine hosting the VM

Host

VARCHAR

Name of the Physical Machine hosting the VM

HypervisorType

VARCHAR

The type of Hypervisor hosting the VM

idEnterprise

INTEGER

The ID of the Enterprise to which the VM belongs

Enterprise

VARCHAR

Name of the Enterprise to which the VM belongs

idVirtualDataCenter

INTEGER

ID of the VDC which the VM belongs to

VirtualDatacenter

VARCHAR

Name of the VDC which the VM belongs to

idVirtualApp

INTEGER

ID of the Virtual Appliance to which the VM belongs

VirtualAppliance

VARCHAR

Name of the Virtual Appliance to which the VM belongs

idVM

INTEGER

ID of the VM

VirtualMachine

VARCHAR

Name of the VM

idImage

INTEGER

ID of the virtual image used by the VM

VirtualImage

VARCHAR

Name of the virtual image used by the VM

VirtualMachineState

VARCHAR

The current state of the VM

idUser

INTEGER

ID of the user which owns the VM

Owner

VARCHAR

Name of the user which owns the VM

idCostCode

INTEGER

ID of the 'cost code' associated with the VM's virtual image

CostCode

VARCHAR

Name of the 'cost code' associated with the VM's virtual image

CPU

DECIMAL

The number of CPU cores used by the VM

MemoryMB

DECIMAL

The amount of memory (in MB) used by the VM

LocalStorageGB

DECIMAL

The amount of LOCAL STORAGE (in GB) used by the VM

Network-centric RLS Views

This group of views return data about VLAN usage (Internal, External, Public networks) and Public IP addresses.

RLS_NETWORK_USED_BY_VDC_V1_0_VW

View that returns a sum of all network resource usage (Total VLANs,Internal,External,Public), totalled by VDC.
RLS is applied by VDC restriction.

Column Name

SQL Type

Description

idVirtualDataCenter

INTEGER

ID of the VDC whose network resources are being summed

idEnterprise

INTEGER

ID of Enterprise to which the VDC belongs

totalVlansUsed

INTEGER

Total number of VLANs used by the VDC for all network types

totalIPsUsed

INTEGER

Total number of IP addresses allocated to VMs within the VDC for all network types

internalNetworksUsed

INTEGER

Total number of internal/private networks used by the VDC

internalIPsUsed

INTEGER

Total number of internal/private IP addresses allocated to VMs within the VDC

externalNetworksUsed

INTEGER

Total number of external networks used by the VDC

externalIPsUsed

INTEGER

Total number of external IP addresses allocated to VMs within the VDC

publicNetworksUsed

INTEGER

Total number of public networks used by the VDC

publicIPsUsed

INTEGER

Total number of public IP addresses allocated to VMs within the VDC

RLS_NETWORK_USED_BY_ENTERPRISE_V1_0_VW

View that returns a sum of all network resource usage (Total VLANs, Internal,External,Public), totalled by Enterprise.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose network resources are being totalled

totalVlansUsed

INTEGER

Total number of VLANs used by the Enterprise for all network types

totalIPsUsed

INTEGER

Total number of IP addresses allocated to VMs within the Enterprise for all network types

internalNetworksUsed

INTEGER

Total number of internal/private networks used by the Enterprise

internalIPsUsed

INTEGER

Total number of internal/private IP addresses allocated to VMs within the Enterprise

externalNetworksUsed

INTEGER

Total number of external networks used by the Enterprise

externalIPsUsed

INTEGER

Total number of external IP addresses allocated to VMs within the Enterprise

publicNetworksUsed

INTEGER

Total number of public networks used by the Enterprise

publicIPsUsed

INTEGER

Total number of public IP addresses allocated to VMs within the Enterprise

Storage-centric RLS Views

This group of views returns information about the use of external storage. There are two types of views, those which return information groups by storage tier, and those which return information independent of storage tier.

RLS_STORAGE_USED_BY_VM_V1_0_VW

View that returns details of external storage usage, summed at VM level.
Note: This view is independent of storage tiers - please use RLS_STORAGE_TIER_USED_BY_VM_V1_0_VW for a tier-based breakdown by VDC
RLS is applied by VDC restriction.

Column Name

SQL Type

Description

idVM

INTEGER

ID of the VM whose external storage is being summed

idEnterprise

INTEGER

ID of Enterprise to which the VM belongs

idVirtualDataCenter

INTEGER

ID of the VDC which the VM belongs to

extStorageMBAllocated

DECIMAL

Amount of external storage (in MB) allocated (across all tiers) to the VM

RLS_STORAGE_USED_BY_VDC_V1_0_VW

View that returns details of external storage usage, summed at VDC level. Note: This view is independent of storage tiers - please use RLS_STORAGE_TIER_USED_BY_VDC_V1_0_VW
for a tier-based breakdown by VDC.
RLS is applied by VDC restriction.

Column Name

SQL Type

Description

idDataCenter

INTEGER

ID of the phyical datacenter whose external storage is being summed for the VDC

idEnterprise

INTEGER

ID of Enterprise to which the VDC belongs

idVirtualDataCenter

INTEGER

ID of the VDC whose external storage is being totalled

extStorageMBAllocated

DECIMAL

Amount of external storage (in MB) allocated (across all tiers) to the VDC. Note that this includes storage not assigned to VMs

extStorageMBAssignedToVMs

DECIMAL

Amount of external storage (in MB) across all tiers which is assigned to VMs within the VDC.

RLS_STORAGE_USED_BY_ENTERPRISE_V1_0_VW

View that returns details of external storage usage, summed at ENTERPRISE level. Note: This view is independent of storage tiers - please use RLS_STORAGE_TIER_USED_BY_ENTERPRISE_V1_0_VW for a tier-based breakdown by Enterprise.
RLS is applied by ENTERPRISE restriction.

Column Name

SQL Type

Description

idDataCenter

INTEGER

ID of the phyical datacenter whose external storage is being summed for the VDC

idEnterprise

INTEGER

ID of Enterprise whose external storage is being summed

extStorageMBAllocated

DECIMAL

Amount of external storage (in MB) allocated (across all tiers) to the Enterprise. Note that this includes storage not assigned to VMs

extStorageMBAssignedToVMs

DECIMAL

Amount of external storage (in MB) across all tiers which is assigned to VMs within the Enterprise.

RLS_STORAGE_TIER_USED_BY_VM_V1_0_VW

View that returns the details of external storage usage by storage tier, summed at VM level.
RLS is applied by VDC restriction.

Column Name

SQL Type

Description

idVM

INTEGER

ID of the VM whose external storage is being summed

idEnterprise

INTEGER

ID of Enterprise to which the VM belongs

idVirtualDataCenter

INTEGER

ID of the VDC which the VM belongs to

idTier

INTEGER

ID of the storage tier being summed for the VM

extStorageMBAllocated

DECIMAL

Amount of external storage (in MB) allocated in the tier to the VM

RLS_STORAGE_TIER_USED_BY_VDC_V1_0_VW

View that returns details of external storage usage by storage tier, summed at VDC level.
RLS is applied by VDC restriction.

Column Name

SQL Type

Description

idDataCenter

INTEGER

ID of the phyical datacenter whose external storage is being summed for the VDC

idEnterprise

INTEGER

ID of Enterprise to which the VDC belongs

idVirtualDataCenter

INTEGER

ID of the VDC whose external storage is being totalled

idTier

INTEGER

ID of the storage tier being summed for the VDC

extStorageMBAllocated

DECIMAL

Amount of external storage (in MB) allocated for the tier to the VDC. Note that this includes storage not assigned to VMs

extStorageMBAssignedToVMs

DECIMAL

Amount of external storage (in MB) of the tier which is assigned to VMs within the VDC.

RLS_STORAGE_TIER_USED_BY_ENTERPRISE_V1_0_VW

View that returns details of external storage usage by storage tier, summed at ENTERPRISE level.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idDataCenter

INTEGER

ID of the phyical datacenter whose external storage is being summed for the VDC

idEnterprise

INTEGER

ID of Enterprise whose external storage is being summed

idTier

INTEGER

ID of the storage tier being summed for the Enterprise

extStorageMBAllocated

DECIMAL

Amount of external storage (in MB) allocated to the tier for the Enterprise. Note that this includes storage not assigned to VMs

extStorageMBAssignedToVMs

DECIMAL

Amount of external storage (in MB) of the tier which is assigned to VMs within the Enterprise.

Repository-centric RLS Views

This group of views return information about Enterprise usage of repository space within Appliance Libraries.
Note that repository usage includes Virtual Machine Templates and VM Instances; it DOES NOT include 'Persistent Images' which are stored outside of the repository using external storage.

RLS_REPOSITORY_USED_BY_ENTERPRISE_V1_0_VW

View that returns details of repository usage, summed at Enterprise level.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose repository usage is being summed

repositoryUsageGB

DECIMAL

The amount of repository usage (in GB) for the enterprise.

RLS_VIRTUAL_IMAGE_V1_0_VW

View that returns details of virtual images from the kinton.virtualimage table.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

kinton.virtualimage.*

*

All columns from the kinton.virtualimage table

RLS_VIRTUAL_IMAGE_USE_BY_ENTERPRISE_V1_0_VW

View that returns IDs of virtual images (and the Enterprises which own them) along with counts of the VMs using the images.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose repository usage is being summed

idImage

INTEGER

ID of the virtual image owned by the Enterprise

vmsUsingImage

INTEGER

The total number of VMs which are using the image. This includes VMs in the this Enterprise, plus any other Enterprises the user is entitled to see.

vmsInOtherEnterpiseUsingImage

INTEGER

The total number of VMs in other Enterprises that are using the virtual image. Note, this total only includes the Enterprises which the user is entitled to see.

Pricing-centric RLS Views

RLS_PRICING_TEMPLATE_V1_0_VW

View that returns pricing template and its associated currency details.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose pricing details are contained in the row

kinton.pricingTemplate.*

*

All rows from the kinton.pricingTemplate table

symbol

VARCHAR

The Currency Symbol associated with the pricing template

currencyName

VARCHAR

The Currency Name

digits

INTEGER

The number of scale digits for the currency

RLS_PRICING_TIERS_V1_0_VW

View that returns pricing information for the storage tiers.
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose pricing details are contained in the row

idTier

INTEGER

ID of the storage tier whose pricing details are included

name

VARCHAR

Name of the storage tier

idPricingTemplate

INTEGER

ID of the Enterprise's pricing template

price

DECIMAL

Price (per GB) for the Storage Tier

symbol

VARCHAR

The Currency Symbol associated with the pricing template

currencyName

VARCHAR

The Currency Name

digits

INTEGER

The number of scale digits for the currency

RLS_PRICING_COST_CODES_V1_0_VW

View that returns pricing information for cost codes
RLS is applied by Enterprise restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose pricing details are contained in the row

idCostCode

INTEGER

ID of the cost code whose pricing details are included

costCodeName

VARCHAR

Name of the cost code

idPricingTemplate

INTEGER

ID of the Enterprise's pricing template

price

DECIMAL

Price for the Cost Code

symbol

VARCHAR

The Currency Symbol associated with the pricing template

currencyName

VARCHAR

The Currency Name

digits

INTEGER

The number of scale digits for the currency

RLS_PRICING_PER_HOUR_V1_0_VW

View that returns pricing information 'per hour' (i.e. for a given Enterprise's pricing template, works out how one hour's resource usage costs for each resource).
RLS applied by ENTERPRISE restriction.

Column Name

SQL Type

Description

idEnterprise

INTEGER

ID of Enterprise whose pricing details are contained in the row

cpu

DECIMAL

Cost of a CPU Core

memoryGB

DECIMAL

Cost of 1 GB of RAM

hdGB

DECIMAL

Cost of 1GB of Local (Hypervisor) Storage

ip

DECIMAL

Cost of 1 Public IP address

vlan

DECIMAL

Cost of 1 VLAN

standingCharge

DECIMAL

Fixed standing charge value assoicated with the Enterprise's pricing template

minimumCharge

DECIMAL

The minimum charge (per charging period) that should be billed against this Enterprise

chargingPeriod

INTEGER

Integer value identifying the pricing template's charging period

chargingPeriodName

VARCHAR

Name of the charging period

priceToHourConversion

DECIMAL

The divisor value by which the above resource costs should be divided by, in order to determine the resource cost 'per hour'
(e.g. cpu/priceToHourConversion=cpuCostPerHour)

currency

VARCHAR

String containing the currency symbol for the above prices

 

Event Centric RLS Views

RLS_EVENTS_V1_0_VW

View that returns events that have occurred in the system from the kinton.metering table. The events returned are restricted to those associated with Enterprises that are accessible  to the user.

Column Name

SQL Type

Description

EventTime

TIMESTAMP

The date and time of the event

Severity

VARCHAR

The severity of the event

idEnterprise

DECIMAL

The id of the Enterprise

Enterprise

VARCHAR

The name of the Enterprise

idVirtualDataCenter

DECIMAL

The id of the VDC

VirtualDataCenter

VARCHAR

The name of the VDC

VirtualAppliance

VARCHAR

The name of the Virtual Appliance

idUser

DECIMAL

The id of the User

User

VARCHAR

The name of the User

Component

VARCHAR

The component associated with the event e.g USER,NETWORK

Action

VARCHAR

The action that the event performed e.g USER_CREATE,USER_MODFIY,VLAN_DELETED

EventMessage

VARCHAR

Textual information that is specific to the event

RLS_ADD_REMOVE_EVENTS_V1_0_VW

View that performs the very specific task of identifying from the metering table those events that specifically correspond to the creation and deletion of various components,
specifically it analyses the following components types:- USER,ENTERPRISE,VIRTUAL_MACHINE,VIRTUAL_APPLIANCE,VIRTUAL_DATACENTER.
The events returned are  restricted to those associated with Enterprises that are  accessible to the User.

Column Name

SQL Type

Description

EventTime

TIMESTAMP

The date and time of the event

idEnterprise

DECIMAL

The id of the Enterprise

Enterprise

VARCHAR

The name of the Enterprise

Component

VARCHAR

The component associated with the event e.g USER,NETWORK

Add_Event

INT

Returns 1 if the event was creation event else 0

Remove_Event

INT

Returns 1 if the event was deletion event else 0

Add_Remove_DeltaINTReturns 1 if the event was creation event, -1 if the event was a deletion event or 0 if its undefined (it should never be 0)

RLS_ADD_REMOVE_EVENTS_V2_0_VW

Semantically identical to RLS_ADD_REMOVE_EVENTS_V1_0_VW , but has additional functionality that it is able to interpret the USER_MODIFY event when
a user moves form one enterprise to another and returns two rows one for a Remove_Event for the old Enterprise and one for an Add_Event for the new Enterprise.
This functionality requires abiquo version 2.6.1 (and data created by that version) to operate which is the reason a separate version 2 exists.

Column Name

SQL Type

Description

EventTime

TIMESTAMP

The date and time of the event

idEnterprise

DECIMAL

The id of the Enterprise

Enterprise

VARCHAR

The name of the Enterprise

Component

VARCHAR

The component associated with the event e.g USER,NETWORK

Add_Event

INT

Returns 1 if the event was creation event else 0

Remove_Event

INT

Returns 1 if the event was deletion event else 0

Add_Remove_DeltaINTReturns 1 if the event was creation event, -1 if the event was a deletion event or 0 if its undefined (it should never be 0)