Introduction
A common question that arises when talking to customers is to have visibility on the quantity of compute resource (CPU, Memory and Storage) that is being used. This information is fairly easy to extract from the oracle-compute command line but it also is surfaced in Enterprise Manager. As an exercise I wanted to try and produce a report from Enterprise Manager which gives the detail on the compute resource used. This blog posting is just a capture of my experiences and not necessarily a best practice approach to reporting using EM12c against an OCM.EM12c Reporting Overview
Enterprise Manager, as a monitoring tool, captures a great deal of information about anything it is monitoring. Both configuration details and of course some historical information on the usage. Provided you are logged in to the tool as a user with permissions to access reports and use the BI Publisher to create custom reports you will be able to find the reports under the Enterprise menu which typically is in the top left corner of the screen.
There are two options under reports, Information Publisher Reports which is a list of pre-defined reports that can be run to pull out commonly used reports and the BI Publisher Enterprise Reports. The BI Publisher approach is the preferred route to use as this is now the report generator of choice for Enterprise Manager, others are deprecated and may eventually be dropped. Like the Information Publisher Reports there are a series of out-the-box reports you can utilise but for many cases a custom report is the way to go.
Creating a BI Publisher Report
BI Publisher is an incredibly powerful reporting tool that can query any database (or indeed even other sources of data) and push that data into an on-line report that can then be run on a regular basis, converted into PDF e-mailed out or run ad-hoc as needed. With Enterprise Manager the main source of data is the underlying database of EM12c.
To create a report the process is essentially a two step process. Firstly you must create a datamodel where you specify which tables to query, what the associations are between the tables, add filters and conditions to extract the specific data of interest. Once the model has been defined you can optionally add in additional query parameters which can tune the report at run-time. Once done you build a report up based on the data in the model, the report is built using simple wizards to produce tables of data, total up columns, display details on various graph types etc.
Building the DataModel for OCM
The Oracle Cloud Machine makes use of an EM12c Virtual Infrastructure plugin for most of the monitoring and management functionality. This plugin stores much of its data in the tables that are prefixed with "MGMT$VI" and using BI Publisher we can create a new data model that will pick the data we are interested. When creating a new data model the default data source is called EMREPOS which is the database used by Enterprise Manager. We can then simply type in the SQL if we know it in advance or alternatively use a "Query Builder" which allows us to dynamically build up the query using a fairly intuitive web based GUI.
The query builder allows us to drag and drop the tables onto a palate and select the fields we are interested in. We can add conditions to the query, define linkage between tables etc.
In our specific use case we are looking to understand the resource used by the virtual machines, specifically allocated CPU, Memory and the storage volumes that have been added to the VMs. This information is available in two tables, MGMT$VI_NM_OSV_CFG_DETAILS and MGMT$VI_NM_STORAGE_CFG.
BI Publisher has a mechanism to allow the report user to specify "parameters" which can be used to filter the data returned by the model. It seems sensible to be able to query the model by tenancy I have added into the data model a parameter which will allow the user to specify one or more tenancies to report on. For these tables the tenancy is effectively defined in the Quota. (an alternative breakdown might be per-orchestration) To build up a parameter we have to create a "list of values" which the user can select from, as with the main data set this is defined via SQL queries against the database. To show all tenancies I used the following SQL query:-
| select "MGMT$VI_NM_OSV_CFG_DETAILS"."QUOTA" as "QUOTA"
from "MGMT_VIEW"."MGMT$VI_NM_OSV_CFG_DETAILS" "MGMT$VI_NM_OSV_CFG_DETAILS" where "MGMT$VI_NM_OSV_CFG_DETAILS"."QUOTA" !='RANDOMTEXT' AND VNC_URL=(SELECT MAX(VNC_URL) from MGMT_VIEW.MGMT$VI_NM_OSV_CFG_DETAILS "B" WHERE b.QUOTA=MGMT$VI_NM_OSV_CFG_DETAILS.QUOTA) |
This allows me to build up a list of tenancies (quota) which has been de-duplicated via the where clause. (Could not get select distinct to work....) This value list (the tenancies) is used as the selection for the Parameter which will be presented on the report to allow the user to narrow the report down to specific tenancies.
As shown in the screnshot I have selected to allow the user to chose multiple tenancies to report on or all the tenancies. If all then a comma separated list of all tenancies on the rack is passed in as the parameter to the report.
Building the report
Once done we can turn attention to the report. The first thing to do is to click on the data tab in the data model and press View to have a look at what data is actually returned by your datamodel. If it looks like the correct information is being returned then click on "Save as Sample Data" and the data you returned is saved and used as the basis for data shown as the report is developed.The easiest way to create the report from here is to click the "Create Report" button on the top right of the screen, this will open up a wizard to allow you to create the report and add charts and data tables to the report.
Having completed the report design we can then run the report. In the screenshot below I have picked out three of the tenancies I am interested in and we can see at a moments glance that the JCS Demo tenancy is using most memory and CPU while the DBCS demo account is using most storage space. Exactly as we would expect for a relatively small application.
Conclusion
Even although the OCM is managed by Oracle as a tenant user of the OCM rack it is fairly easy to use Enterprise Manager to gain insight into the usage of the OCM and BI Publisher provides a way to extract data to put into useful management reports.
