Summary of REST API Reporting:
This is a BMC Communities provided and supported example of configuring an ETL which will query BPPM or TSIM via REST API and collect aggregrate monitoring metric values for any number of days up to 1 month (maximum) and dump to a Pivot Table within an MS Excel workbook. This is a follow-up to the existing post TSIM/BPPM - Using REST API to Extract Metrics to MS Excel - Simplified and Very Cool Reporting! If you have not looked at that posting, please start there and then return to this post. The previous post will step through setting up the environment which is dependent on using Pentaho Communities Edition (PDI). This is an easier setup since no Service Models are required. This is intended to report on specific technology stacks such as such as Hadoop, Docker, Oracle, WebLogic, WebSphere, SAN monitoring .. the example we use here is vBlock.
The value for simplified XLSX reporting would be the following:
- Analytics - ability to take monitoring data offline into a XLSX for analysis and triage. You can easily overlay similiar type servers (web, middleware, DB) and compare performance metrics over a specific timeframe.
- Visibility - provide offline reports easily for customers or users who do not have access to BPPM or TrueSight Operations Manager
- Open Integration - Digital Enterprise Management (DEM) is partially focused on open integration. This posting is a good example of how to extract monitoring metrics from BPPM/TSIM using REST API
- Ease of Use ..
Important Note for BPPM 9.5 or 9.6 envs: if you are using this with BPPM 9.5/9.6 - you will need to manually edit the REST Client Step within 2 transformations and change the authentication from default admin/admin12345 to admin/admin. The ETLs have been fully tested with TSIM (TrueSight Operations Manager v10) but should work fine with BPPM 9.5/9.6. Easy peasy.
Here is the data flow which is broken into 2 separate ETLs that can run in just a few minutes:
Recommendation for buiding out Virtual Topology: If you are monitoring vBlock, I would recommend looking at the following post which can build out the Virtual Topology Dynamically using the VSM KM and Direct Publishing: Automatically Create Virtual Topology using VSM KM and Direct Publishing with Simple Service Model Support - WINDOWS version
In this posting, we show an example of how to easily extract data into XLSX and use the Pivot Charts within MS Excel to create an offline Dashboard. This requires an ETL (Extract, Transform and Load) engine using Pentaho's Communities Edition (PDI - OpenSource ETL). Here is a screenshot (click to enlarge image) of a sample MS Excel Dashboard that is fully dynamic with Pivot Charts and Data slicers:
*Note the number of metrics depends on the time interval (how many days back are you collecting) and the number of servers/hosts.
Again, let's tackle reporting from a different perspective....
Here is an animation of the flexibility and ease of viewing metrics from Pivot Charts and changing slicers of data (filtering based on Host, Date, and Operating System):
4 Quick and Easy Steps to Cool Reporting:
Watch this video (these steps are the same for vBlock): TSIM/BPPM Create Technical Cockpit Reports in MS Excel using REST API - Example using JBoss Metrics on Vimeo
Step 1 – Watch the previous POST video
- Goto Post: TSIM/BPPM - Using REST API to Extract Metrics to MS Excel - Simplified and Very Cool Reporting!
- Watch this step by step video (though Dynamic Data Table or Service Models not required for this reporting solution):
Step 2 – Download Pentaho PDI and Extract
- Make sure to have a JRE installed (any version) on the server you are running ETL.
- Once downloaded, extract ETL to a path with no spaces: e.g. D:\bmc_pentaho (this will be fixed in later version)
- The ETL does not need to run on the BPPM or TSIM server. Any Windows server with minimal resources is needed.
- Place jconn4.jar file in the lib directory. See FAQ Section - question #3 for details.
- Download PDI (latest version) from SourceForge: http://sourceforge.net/projects/pentaho/files/Data%20Integration
- For more information on PDI, please goto Community Edition Downloads - Pentaho Community - Pentaho Wik
Step 3 – Add Global Variables to ETL
- In this posting, included are PDI (Pentaho Communities Edition) Job and Transfromation files. You just need to use Spoon to open 2 Jobs (.kjb files) and edit/modify parameters (output_dir, hostname ...) for the ETL to run correctly in your enviornment:
Step 4 – Execute ETL and voila ....
- You can execute Jobs from a Command line, schedule it or run it from Spoon. The ETL does create a template XLSX file and .CSV file named after the silo_metric_name value (you typed in the Parameters above - e.g. JBOSS). The only manual step is to copy the aggregrate values (all rows/columns) to the Blank Pivot table (which is a tab named "AggregateData"). The Pivot Charts will refresh.
Reporting ETL Requirements:
The following are required for this integration:
- Pentaho PDI - Kettle (Open Source ETL)
- Java SE (JRE) – no specific version required
- BPPM +9.5, 10 (TSIM)
- Embedded Sybase Database (*Oracle is not supported yet)
This base integration will take about 20 minutes to setup.
What do you think? Please provide any feedback on the comments section below. Also, if you have any enhancements or ideas we would love to hear them.
Extra Stuff - VB Macros in MS Excel:
You might want to consider creating a VB Macro within MS Excel. The Template XLSX has a macro that you can run on the Dashboard tab.
The following will globally change all the line weights to be 1.25 for all active charts in the workbook. Here is the VB code which you can re-use or alter:
'This macro will print all embedded charts in the active workbook
Application.ScreenUpdating = False
Dim Sht As Object
Dim Cht As ChartObject
For Each Sht In ActiveWorkbook.Sheets
For Each Cht In Sht.ChartObjects
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
srs.Format.Line.Weight = 1.25
.MarkerStyle = xlMarkerStyleNone