TSIM/BPPM Create Technical Cockpit Reports in MS Excel using REST API - Example using JBoss Metrics

Version 1

    Watch Me ~6 min video: TSIM/BPPM Create Technical Cockpit Reports in MS Excel using REST API - Example using JBoss Metrics on Vimeo


    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:

    Reporting Flow Metrics.png


    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:

    Watch this video:  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

    Step 2 – Download Pentaho PDI and Extract

    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:

        • TSIM_Monitors_Reporting_REST_API_List_Monitors.kjb


        • TSIM_Monitors_Reporting.kjb


    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)

    Integration LoE:

    This base integration will take about 20 minutes to setup.

    Integration Feedback:

    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.