TSIM/BPPM - Create vBlock Reports in MS Excel using REST API - Example

Version 2
    Share This:

    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

    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.

    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:

    Sub ChangeLineType()

    '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




    End Sub