Extracting data from the data mart by using the API

This use case describes how to use the Data mart API to extract data from the data mart.

Related topics

Access-and-authentication-for-the-REST-API

Datamart-API-endpoints

Scenario

Paul is a capacity planner in an IT organization. He manages and monitors the capacity views in his organization to ensure that the existing infrastructure capacity has sufficient resources to meet the current and future demands. Paul wants to access the data of the VMWARE_VF_CL_CAPACITY data mart with an external client using the API. He particularly wants to view the list of clusters where the CPU utilization exceeds 80%.

Implementation workflow

Paul has to make sure that he has access to the APIs. He requests Alan, the administrator to provide him access to use the APIs. After Paul has access to the APIs, he can use the APIs to extract the data from the data mart.

Task 1: Find the ID of the data mart

Paul has to first find the ID of the VMWARE_VF_CL_CAPACITY data mart so that he can proceed with the API calls. To find the data mart ID, Paul logs on to TrueSight Capacity Optimization and does the following:

  1. Navigate to Administration > Data marts.
  2. In the Name column, search for the VMWARE_VF_CL_CAPACITY data mart.
  3. The Id column displays the ID of the data mart.

Datamart_Id.jpg

Information for Paul

Paul finds out that the data mart ID is 1688. He makes a note of the ID to use in the API calls.

Task 2: Extract the data from the data mart

Using the data mart ID, Paul can extract the data from the data mart. He uses this API call: 

POST https://<BMC Helix Portal URL>/opt/api/v1/datamartservice/datamarts/1688/data

Example: POST https://host1.bmc.com/opt/api/v1/datamartservice/datamarts/1688/data

Parameters

Example

Request body

Click to view the request body
Request body
{
"options": {
"pagenum": 0,
"pagesize": 2
}
}

Response

Click to view the response
Response
{
 "data.format" : "EXTENDED",
 "href" : "https://host1.bmc.com/opt/api/v1/datamartservice/datamarts/1688/data",
 "metadata" : {
   "percentage.upperbound" : 1
 },
 "pagination" : {
   "has.more.data" : true,
   "pageNumber" : 0,
   "pageSize" : 2
 },
 "filters" : [],
 "data" : [ {
   "sysid" : "5919",
   "cluster_name" : "bcoqa01",
   "virtual_center" : "vm1.bmc.com",
   "datacenter_name" : "BCOQA-DC",
   "cpu_total_mhz" : "70368",
   "total_real_mem_gb" : "103030980608",
   "total_dstore_size" : "2392833654784",
   "total_dstore_util_pct" : "0.26",
   "total_dstore_used_slope" : "0",
   "spare_gm" : "82",
   "gm_used" : "TYPICAL",
   "bottleneck_resource" : "STORAGE",
   "min_days_to_saturation" : "99999",
   "dstore_days_to_saturation" : "99999",
   "host_num" : "1",
   "cpu_threshold" : "0.85",
   "cpu_threshold_good" : "0.75",
   "mem_threshold" : "0.95",
   "mem_threshold_good" : "0.9",
   "dstore_threshold" : "0.85",
   "dstore_threshold_good" : "0.75",
   "mem_com_util_threshold" : "1",
   "mem_com_util_threshold_good" : "0.6",
   "benchmark_value" : "2299.3782",
   "use_memutil_sparevm_flag" : "true"
 }, {
   "sysid" : "6139",
   "cluster_name" : "bcoqa02",
   "virtual_center" : "vm1.bmc.com",
   "datacenter_name" : "BCOQA-DC",
   "cpu_total_mhz" : "35184",
   "total_real_mem_gb" : "51491373056",
   "total_dstore_size" : "2392833654784",
   "total_dstore_util_pct" : "0.71",
   "total_dstore_used_slope" : "0",
   "spare_gm" : "19",
   "gm_used" : "TYPICAL",
   "bottleneck_resource" : "STORAGE",
   "min_days_to_saturation" : "99999",
   "dstore_days_to_saturation" : "99999",
   "host_num" : "1",
   "cpu_threshold" : "0.85",
   "cpu_threshold_good" : "0.75",
   "mem_threshold" : "0.95",
   "mem_threshold_good" : "0.9",
   "dstore_threshold" : "0.85",
   "dstore_threshold_good" : "0.75",
   "mem_com_util_threshold" : "1",
   "mem_com_util_threshold_good" : "0.6",
   "benchmark_value" : "1153.3829",
   "use_memutil_sparevm_flag" : "true"
 }]
}

Explanation of the response

The response includes the following information:

  • Details of the metadata. 
  • In the Pagination field:
    • has.more.data - Indicates whether there are more rows in the data mart to extract. A value false indicates that there are no more rows to retrieve.
    • pageNumber - Page number of the data extracted.
    • pagesize - Number of rows retrieved. In this example, it shows only two rows as requested in the body. 
  • There are no filters added in the above example.
  • Details of the two rows that are retrieved from the data mart. The columns in the data mart correspond to a row of data in the table.

To view 5 rows of data, update the options parameter in the same API request as follows:

POST https://<BMC Helix Portal URL>/opt/api/v1/datamartservice/datamarts/1688/data

Example: POST https://host1.bmc.com/opt/api/v1/datamartservice/datamarts/1688/data

Parameters

Example

Request body

Click to view the request body
{
    "options": {
        "pagenum": 1,
        "pagesize": 5
    }
}

Response

Click to view the response
{
  "data.format" : "EXTENDED",
  "href" : "https://host1.bmc.com/opt/api/v1/datamartservice/datamarts/1688/data",
  "metadata" : {
    "percentage.upperbound" : 1
  },
  "pagination" : {
    "has.more.data" : false,
    "pageNumber" : 1,
    "pageSize" : 5
  },
  "filters" : [],
  "data" : [ {
    "sysid" : "5919",
    "cluster_name" : "bcoqa01",
    "virtual_center" : "vm1.bmc.com",
    "datacenter_name" : "BCOQA-DC",
    "cpu_total_mhz" : "70368",
    "total_real_mem_gb" : "103030980608",
    "total_dstore_size" : "2392833654784",
    "total_dstore_util_pct" : "0.26",
    "total_dstore_used_slope" : "0",
    "spare_gm" : "82",
    "gm_used" : "TYPICAL",
    "bottleneck_resource" : "STORAGE",
    "min_days_to_saturation" : "99999",
    "dstore_days_to_saturation" : "99999",
    "host_num" : "1",
    "cpu_threshold" : "0.85",
    "cpu_threshold_good" : "0.75",
    "mem_threshold" : "0.95",
    "mem_threshold_good" : "0.9",
    "dstore_threshold" : "0.85",
    "dstore_threshold_good" : "0.75",
    "mem_com_util_threshold" : "1",
    "mem_com_util_threshold_good" : "0.6",
    "benchmark_value" : "2299.3782",
    "use_memutil_sparevm_flag" : "true"
  }, {
    "sysid" : "6139",
    "cluster_name" : "bcoqa02",
    "virtual_center" : "vm1.bmc.com",
    "datacenter_name" : "BCOQA-DC",
    "cpu_total_mhz" : "35184",
    "total_real_mem_gb" : "51491373056",
    "total_dstore_size" : "2392833654784",
    "total_dstore_util_pct" : "0.71",
    "total_dstore_used_slope" : "0",
    "spare_gm" : "19",
    "gm_used" : "TYPICAL",
    "bottleneck_resource" : "STORAGE",
    "min_days_to_saturation" : "99999",
    "dstore_days_to_saturation" : "99999",
    "host_num" : "1",
    "cpu_threshold" : "0.85",
    "cpu_threshold_good" : "0.75",
    "mem_threshold" : "0.95",
    "mem_threshold_good" : "0.9",
    "dstore_threshold" : "0.85",
    "dstore_threshold_good" : "0.75",
    "mem_com_util_threshold" : "1",
    "mem_com_util_threshold_good" : "0.6",
    "benchmark_value" : "1153.3829",
    "use_memutil_sparevm_flag" : "true"
  },
  {
    "sysid" : "4034",
    "cluster_name" : "BCM-VC - Tel-Aviv Cluster",
    "virtual_center" : "bcm-vcenter.bmc.com",
    "datacenter_name" : "BCM-VC",
    "cpu_total_mhz" : "0",
    "total_real_mem_gb" : "0",
    "gm_used" : "TYPICAL",
    "host_num" : "0",
    "cpu_threshold" : "0.85",
    "cpu_threshold_good" : "0.75",
    "mem_threshold" : "0.95",
    "mem_threshold_good" : "0.9",
    "dstore_threshold" : "0.85",
    "dstore_threshold_good" : "0.75",
    "mem_com_util_threshold" : "1",
    "mem_com_util_threshold_good" : "0.6",
    "use_memutil_sparevm_flag" : "true"
  }]
}

Explanation of the response

The request was to retrieve 5 rows, but the response has retrieved only 3 rows. This is because there are only 3 rows to retrieve as indicated by the value false in the has.more.data field.

Task 3: Add a filter condition to limit the results displayed

After viewing the default response results, Paul can add filters to view only the required data. In this case, since Paul wants to view only the details of the clusters where the CPU utilization is more than 80%. He can use this request:

POST https://<BMC Helix Portal URL>/opt/api/v1/datamartservice/datamarts/1688/data

Example: POST https://host1.bmc.com/opt/api/v1/datamartservice/datamarts/1688/data

Parameters

Example

Request body

Click here to view the request body
{
"filters": [
{
"name": "p_cpu_used_cluster",
"condition": "GREATER_THAN_OR_EQUAL",
"value": 0.8
}
],
"options": {
"pagenum": 0,
"pagesize": 20
}
}

Response

Click here to view the response
{
 "data.format" : "EXTENDED",
 "href" : "https://host1.bmc.com/opt/api/v1/datamartservice/datamarts/1688/data",
 "metadata" : {
   "percentage.upperbound" : 1
 },
 "pagination" : {
   "has.more.data" : false,
   "pageNumber" : 0,
   "pageSize" : 20
 },
 "filters" : [ {
   "name" : "p_cpu_used_cluster",
   "condition" : "GREATER_THAN_OR_EQUAL",
   "value" : 0.8,
   "note" : "This filter will be applied on column p_cpu_used_cluster"
 }],
 "data" : [ {
   "sysid" : "4036",
   "cluster_name" : "CLM-Cluster",
   "virtual_center" : "bcm-vcenter.bmc.com",
   "datacenter_name" : "TSCO-Datastore",
   "cpu_total_mhz" : "18392",
   "p_cpu_used_cluster" : "1.25",
   "cpu_utilmhz_slope" : "-1",
   "total_real_mem_gb" : "68671242240",
   "p_mem_consumed_cluster" : "0.99",
   "mem_consumed_slope" : "-4273295.48",
   "total_dstore_size" : "2466921840640",
   "total_dstore_util_pct" : "0.81",
   "total_dstore_used_slope" : "996569.17",
   "gm_on_num" : "13",
   "gm_on_num_slope" : "0",
   "spare_gm" : "0",
   "gm_used" : "TYPICAL",
   "bottleneck_resource" : "MANY",
   "min_days_to_saturation" : "-1",
   "cpu_days_to_saturation" : "-1",
   "mem_days_to_saturation" : "99999",
   "dstore_days_to_saturation" : "99999",
   "saturation_resource" : "CPU",
   "mem_consumed" : "63203900416",
   "composite_memory_util" : "0.55925",
   "composite_memory_consumption" : "36189051564.18581",
   "cpu_pressure_in" : "125.85",
   "typycal_gm_cpu_utilmhz" : "1579",
   "typycal_gm_mem_consumed" : "4861838494",
   "typycal_gm_dstore_used" : "153218731717",
   "host_num" : "1",
   "cpu_threshold" : "0.85",
   "cpu_threshold_good" : "0.75",
   "mem_threshold" : "0.95",
   "mem_threshold_good" : "0.9",
   "dstore_threshold" : "0.85",
   "dstore_threshold_good" : "0.75",
   "mem_com_util_threshold" : "1",
   "mem_com_util_threshold_good" : "0.6",
   "benchmark_value" : "582.23035",
   "use_memutil_sparevm_flag" : "true",
   "benchmark_value_unused" : "-67.62",
   "net_bit_rate" : "20772864",
   "disk_transfer_rate" : "1304576"
 } ]
}


Explanation of the response

There is only one cluster where the CPU utilization exceeds 80%. 

Time filters

In addition to generic filters, the data mart API also supports time filters. The following table explains the time filters and their syntax.

Time filter

Syntax

Last X days

Click here to view the syntax
"filters":[

     {
        "name":"timefilterlastxdays",

        "condition":"EQUALS",

        "value":"D30"
     }
   ]

A value can be D1, D7, and D30, where D1 indicates the last 1 day, D7 indicates the last 7 days, and D30 indicates the last 30 days.

Predefined

Click here to view the syntax
"filters" : [

   {
       "name"      : "timefilterid",

       "condition" : "EQUAL",

       "value"     : "5"
   }
]

Date range

Click here to view the syntax
"filters" : [

   {
       "name"      : "timefilterfrom",

       "condition" : "EQUAL",

       "value"     : "05/05/2022"
   },

   {
       "name"      : "timefilterto",

       "condition" : "EQUAL",

       "value"     : "08/05/2022"
   }
]

Additional information about the filters

  • You can apply a filter on any column of your data mart.
  • You can add one or more filters to your request. All filters are evaluated in AND condition.

Let's say that in addition to the clusters that are exceeding CPU utilization of 80%, Paul also wants to see the clusters where the memory utilization is less than 50%. Paul can add an additional filter in the body of the request as follows:

Example request body: Filter all clusters with CPU utilization >80 and memory utilization <50
{
    "filters": [
        {
            "name": "p_cpu_used_cluster",
            "condition": "GREATER_THAN",
            "value": 0.8
        },
        {
            "name": "p_mem_consumed_cluster",
            "condition": "LESS_THAN",
            "value": 0.5
        }
    ],
    "options": {
        "pagenum": 0,
        "pagesize": 20
    }
}

List of filter conditions

List of filter conditions that you can use in the filter body

NULL

NOT_NULL

EQUALS

EQUALS_CASE_SENSITIVE

NOT_EQUALS

NOT_EQUALS_CASE_SENSITIVE

CONTAINS

CONTAINS_CASE_SENSITIVE

DOES_NOT_CONTAIN

DOES_NOT_CONTAIN_CASE_SENSITIVE

STARTS_WITH

STARTS_WITH_CASE_SENSITIVE

ENDS_WITH

ENDS_WITH_CASE_SENSITIVE

GREATER_THAN

GREATER_THAN_OR_EQUAL

LESS_THAN

LESS_THAN_OR_EQUAL

BETWEEN

BETWEEN_CASE_SENSITIVE

NOT_BETWEEN

NOT_BETWEEN_CASE_SENSITIVE

EQUAL

EQUAL_CASE_SENSITIVE

NOT_EQUAL

NOT_EQUAL_CASE_SENSITIVE

Results

Paul is able to view the list of clusters where the CPU utilization exceeds 80% using the Data mart API. 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*