TSOM Smart Reporting - Filtering or Replacing data from the Smart Reporting Database

Version 1
    Share This:

    One question that is often asked is "Are there ways that I can modify the data coming from the Report Engine Database when creating my Smart Reporting reports?"

     

    Example:

     

    In TSOM Smart Reporting I would like to create a report that has a Monitor Instance Full Name detail that is very long… and want to only include a small portion of the name in the report. For example, the Monitored Instance Full Name returned may be a long string like:

     

    Local:cloudw6022.my.com.cloud6022.cloud.my.com\Node_N1.cloudCP1235_AOAG1.Replicas… etc.

     

    I only want a portion of the name “cloudCP1235_AOAG1” to show as the name.

     

    How can I filter (regex?) the names of returned values in any field of Smart Reporting? Or more specifically the Monitored Instance Full Name for instance?

     

     

    Answer:

     

    While creating the report if you want to do any changes on any dimension, you can create a calculated field at report level.

    There are 3 types of calculated fields.

    1. Pre-defined
    2. Simple
    3. Freehand SQL --- In freehand SQL we can use the database function on any column of the database table.

     

    For example here, Monitor Instance name is displayed form the table VW_CONFIGDATA and column INSTNAME.

    So to change the value, we can use below database function as freehand SQL dimension.

     

    REGEXP_SUBSTR(VW_CONFIGDATA.INSTNAME, 'cloudCP[^,]+1')

     

    // Regular expression can be changed as per requirement

    // Using oracle database function considering reporting database is Oracle . We can also look for SQL server if it’s SQL Server (see below for more).

     

    Steps to create the calculated field and then use that object in report.

    pic2.jpgpic1.jpg

    Drag-n-drop this dimension is report.

     

    pic3.jpg

     

    Regex used:

    REGEXP_SUBSTR(VW_CONFIGDATA.INSTNAME,',[^,]+,')

     

    More examples of regex and use of REGEXP_SUBSTR can be found here:

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm

     

     

    MS SQL Note in this example we used the REGEXP_SUBSTR call to replace a substring in Freehand SQL because Oracle supports regex out-of-the-box.

     

    MS SQL does not allow regex commands directly but does use Transact-SQL  (T-SQL) in a similar way. Please see more here:

    Transact-SQL Reference (Database Engine) - SQL Server | Microsoft Docs

    T-SQL Tutorial: Create & query database objects - SQL Server | Microsoft Docs 

     

     

    More on Smart Reporting Freehand SQL can be found here (note this references Server Automation):

    Creating reports - Documentation for TrueSight Smart Reporting for Server Automation 19.01 - BMC Documentation