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 JBoss.
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:
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: this screenshot only has a single JBoss server, but you would have many servers that you can analyze and overlay for anlytics/analysis.
Again, let's tackle reporting from a different perspective....
4 Quick and Easy Steps to Cool Reporting:
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.