Use the OpenAPI QueryBuilder

The OpenAPI QueryBuilder creates query URLs that export configuration and polled data.
capm350
HID_OpenAPI_QueryBuilder
The OpenAPI QueryBuilder creates query URLs that export configuration and polled data.
To access the QueryBuilder, go to the following URL:
http://
DA_host
:8581/odataquery
In a fault tolerant environment, specify the proxy server as the
DA_host
.
2
Create an OpenAPI Query
To extract customized data sets from the data source, use the QueryBuilder to generate OpenAPI queries.
Queries that return large sets of results can negatively affect your system. Refine your queries to return only the results that are relevant to your needs.
The address bar of the Web browser updates to show the selected tokens in the Query Expression field. To continue editing the query, copy and save this URL
Follow these steps:
  1. Click the Query Expression field to start a query.
  2. Select the
    for
    option that represents what you want in the data set.
  3. Add the
    select
    ,
    expand metrics
    , and
    expand
    tokens to define the output data.
  4. Add more tokens to refine the results.
    QueryBuilder creates the OData URL.
    • To run the query in the QueryBuilder browser window, click
      Run
      .
    • To run the query from a Web browser, REST tool, or custom application, copy the OData URL.
OpenAPI Controls
The OpenAPI QueryBuilder uses tokens that represent logical elements in the OpenAPI query syntax. Tokens appear when you click the Query Expression field. A token lets you select the type of attribute, filter, metric family, metric, or time range for the query. Selecting a token updates the Query URL.
Use tokens to create and export Query URLs:
  • for
    This token determines the type of item that the query retrieves data about. This token appears only once in each query.
    Most options for this token include selections that set up an automatic filter. For example, when you select
    interface
    in the token, you can select
    within Groups that...
    This option adds a filter token with the group filter selected as the first criterion.
    If a newly added metric family does not appear in the QueryBuilder, refresh the Web browser.
  • select
    This token determines the item properties to include in the data set.
    Property order is not supported in the results.
  • expand metrics
    This token determines the performance metrics to include in the data set.
    Property order is not supported in the results.
  • filter
    This token adds custom filters that are based on logical functions using the AND and OR operations. Select whether the filter is case-sensitive for all logical functions.
    When OData evaluates a filter expression, the 'Any' operator is used to determine whether the Boolean expression is True or False for a collection of items. The following examples return True:
    • odata/api/devices?$select=ID,Name&$filter=((startswith(Name, 'cisco') eq true))
    • odata/api/cpus?$top=10&$filter=cpumfs/im_Utilization gt 80 where cpumfs/im_Utilization is the collection [60,65,81,68,61]
    In large systems, some queries might time out if the filters are applied inefficiently. OpenAPI evaluates filter expressions in the order they appear in the query. Use the filter that limits the data more first. For example, if you want all groups where interface utilization is great than 50 percent in North America, place the North America filter first.
  • filter metrics
    This token adds custom filters that are based on the selected metrics.
  • time range
    This token limits the time range of the results for performance metrics and sets the granularity of the data.
    If the time granularity is set to Day and the Time Zone is set to any value other than the default, the Start Time and End Time fields are disabled.
  • expand
    This token adds data that is related to the base item to the returned data set. For example, if you selected data for interface, use expend to get data about the related devices.
    In the expand token, select which columns to add to the data set. For example, for device, you might select name, primary IP address, and location.
    Each expanded attribute appears in the HTML table in the same format as a metric column. For example, if to display interface information for a specific device, you can use the following query:
    select=device/Name&$expand=device
  • group/aggregate
    This token defines grouping and aggregation for the returned data set. For example, aggregate CPU utilization to the device level, and average the utilization for the last hour.
    The OpenAPI QueryBuilder currently supports only the '
    groupby
    ' and '
    aggregate
    ' OData transformations. If the starting entity is metric family, then the
    groupby
    transformation can be applied to
    ID
    ,
    DeviceItemID
    , and
    Timestamp
    . If the starting entity if a config entity and aggregation is for metric family data, the
    groupby
    transformation can be applied to
    ID
    and
    DeviceItemID
    . The
    groupby
    transformation can now be done on the group level using group ID. The
    aggregate
    transformation can now include up to 5 expressions.
  • sort
    This token controls the sorting of the query output.
    The OpenAPI does not support sorting on properties of a related entity. For example, the following query is supported:
    /cpus?$orderby=Name
    The following query is
    not
    supported:
    /cpus?$orderby=device/Name
    The OpenAPI supports sorting only on the properties of the target entity of your query.
    http://
    hostname
    :8581/odata/api/
    TargetEntity
    For example, sorting of metric columns works best when the target entity of your query is the metric family.
  • limit (top)
    This token specifies the maximum number of rows or expanded rows in the query output.
    • Maximum number of rows
      The number of rows in the Results table
    • Maximum number of expanded rows
      The number of rows in the Expand window
      This number only applies to the number of rows when the Expand token is used.
      To modify the default values or the maximum number of rows, see Configure OpenAPI Defaults and Limits.
    • Skip
      The number of leading rows to omit from the query output
  • format
    This token determines the format of the returned data set. The OpenAPI supports the following formats:
    • HTML Table
      If the query does not include this token, HTML Table is the default format.
      The HTML table format is supported only in the OpenAPI QueryBuilder. Direct OpenAPI queries support JSON, XML, Atom, and CSV.
    • JSON
      The JSON format removes the metadata from the resulting data set. Doing so reduces the network transfer time, the payload size up to 50-75 percent, and the client processing time. To add metadata to JSON output manually, use the following HTTP header:
      Accept: application/json;odata=verbose
      You can also add metadata to JSON output. To do so, add the $format query option as the following text to the OData URL:
      $format=application/json;odata=verbose
    • XML
    • Atom
    • CSV
      To specify CSV as a format manually, append the following text to the end of the OData URL:
      $format=text/csv
  • custom parameter
    This token adds custom parameters in OData syntax to the OpenAPI query.
Find Metric Family Names for QueryBuilder
The metric family names in the OpenAPI are shortened versions of the internal metric family names. The OpenAPI metric family names remove the prefix 'Normalized' and the suffix 'Info'. The capitalization is removed and the suffix 'mf' is added to the string. For example, the internal name of the Interface metric family is 'NormalizedPortInfo'. The OpenAPI metric family name is 'portmf'.
To see the internal metric family name, look up the name in
CA Performance Center
.
Follow these steps:
  1. Select
    Administration
    , and click the Data Aggregator data source.
  2. Expand
    Monitoring Configuration
    , and click
    Metric Families
    .
  3. Hover over one of the column headings, and click the down arrow.
  4. Expand the Columns menu, and select
    Internal Name
    .
    The
    Internal Name
    column appears on the page.
  5. Search for the metric family, and record the internal name.
Best Practices for OpenAPI Performance
The OpenAPI enables quick and flexible extraction of data from the database. For queries that produce large results, use the following best practices to improve performance:
  • When you apply multiple filters to a query, ensure that all filters rules against the same object are adjacent. Splitting these filters reduces the efficiency of the query.
  • Avoid filtering by strings. For example, to filter on a group, look up the group ID instead of filtering on the group name.
  • Combine group expressions. Using an OR operator between a group expression and another expression is unsupported. For example, the following expression returns an error:
    odata/api/interfaces?&$select=ID,Description&$filter=(groups/Name eq 'Manageable Devices')  or (substringof('Gigabit',Description) eq true)
  • Aggregation functions against large data sets take a long time.
  • OpenAPI is not a bulk data export tool. Use OpenAPI to extract only the data that you need.
  • Use the top and skip values to apply paging for large data sets.
  • Use the largest granularity that is relevant for your data set. For example, if you want data that is aggregated by sum, the hourly or daily values provide the same information as rate data.