Simple Query
The Cisco Catalyst SD-WAN Manager NMS statistics database collects statistics from all vEdge routers periodically, starting from when they joined the overlay network. The Cisco Catalyst SD-WAN Manager API query language allows you to build queries that retrieve selected statistics from the Cisco Catalyst SD-WAN Manager NMS statistics database.
In a simple query, you specify the name or names of the fields whose values you want to retrieve, the time period over which to retrieve the value, and the order in which to sort the retrieved data. For example, you can retrieve statistics for an IP address for the last 6 hours, and you can sort them in descending order by hostname.
Each query consists of four sections:
- Size—Number of statistics records to retrieve.
- Query—Input values for the statistics database query.
- Sort—Output field to sort the results by and sorting order.
- Fields—Statistics data fields to include in the query output.
Query Format
A Cisco Catalyst SD-WAN Manager simple query has the following format:
{
"size": integer,
"query":
{
"condition": "AND | OR",
"rules":
[
{
"field": "field-name",
"type": "data-type",
"value":
[
"value"
],
"operator": "operator"
}
]
},
"sort":
[
{
"field": "field-name",
"order": "(asc | desc)"
}
],
"fields":
[
"field-name",
],
}
Query Components
Size Component
- Number of Records To Return
Size: integer
Number of statistics records to return in the simple query. This field is optional.
Values: Any integer.
Default: 10000
Query Component
Rules To Select Data
Query: One or more rules to use to select data from the statistics records. This field is mandatory.Filter Condition condition: "(AND | OR)"
Condition to use to filter statistics records if a query has two or more rules. It can be:
• AND—Record must match all rules.
• OR—Record must match at least one rule.Rules for Collecting Statistics Records
rules: [...]
Rules to use to collect statistics records. You can specify one or more rules.Property Component of a Rule
field: "field-name"
Name of the property for which to select statistics. The Response Body of the API calls whose URLs end in /fields lists the allowable values for field-name in the "property" field. For more information, see the last section of this article.
Values: Any string listed in the "property": line of an API call's Response Body.Property Type Component of a Rule
type: "data-type"
Data type of the property. The Response Body of the API calls whose URLs end in /fields, list the allowable values for data-type in the "dataType" field. For more information, see the last section of this article. data-type can be one of the following:- date—Date and time, in the format yyyy-MM-ddThh:mm:ss (for example, 2017-01-23T15:59:59).
- double—64-bit number that includes a decimal point.
- int—32-bit number that does not include a decimal point.
- long—64-bit number that does not include a decimal point.
- string—Text string. It can include any ASCII and extended ASCII characters, and it can include spaces.
Value Component of a Rule
value: [ "value" ]
Value or values to query for. Specify the value in the format required by the data-type. To specify more than one value, separate them with commas.Operator Component of a Rule
operator: "operator"
Operator to perform filtering of statistics records. You use date, numeric, and strings operators. The type of operator in a rule is a function of the field type.Date Operators
When the field type requires a date or time, specify it in one of the following formats:- between date1, date2—Time range, starting at date1 and ending at date2. Specify the date in the format yyyy-MM-dd.
- last_n_days—Last number of days from the current time. For example, for a current date and time of 2017-01-23T15:59:59, last_3_days returns data from 2017-01-20T15:59:59 through 2017-01-23T15:59:59.
- last_n_hours—Last number of hours from the current time. For example, for a current time of 15:13, last_2_hours returns data from 13:13 through 15:13.
- last_n_weeks—Last number of weeks from the current time. For example, for a current date and time of 2017-01-23T15:59:59, last_2_weeks returns data from 2017-01-09T15:59:59 through 2017-01-23T15:59:59.
Numeric Operators
When the field type requires a number, specify it in one of the following formats. You cannot include wildcards in these operators.- equal value—Match a value exactly.
- greater value—Match a value greater than the specified number.
- greater_or_equal value—Match a value greater than or equal to the specified number.
- in value[, value]—Match a value that contains the specified number. To specify more than one number, separate them with commas.
- less value—Match a value less than the specified number.
- less_or_equal value—Match a value less than or equal to the specified number.
- not_equal value—Match all values except the specified number.
- not_in value[, value]—Match all values except those that contain the specified number. To specify more than one number, separate them with commas.
String Operators
When the field type requires a text string, specify it in one of the following formats. You cannot include wildcards in these operators.- equal value—Match a value exactly.
- in value[, value]—Match a value in an array. To specify more than one number, separate them with commas. For example, {"field" : {"$in" : [value1, value2, ...]}}
- not_equal value—Match all values except the specified string.
- not_in value[, value]—Match all values except those that contain the specified string. To specify more than one string, separate them with commas.
Sort Component
To sort on multiple fields, include multiple "field"–"order" pairs in the query. Sorting is done in the order the fields are listed, starting with the first field.
Field To Sort By
field: "field-name"
Field to use to sort the data. The Response Body of the API calls whose URLs end in /fields, list the allowable values for field-name in the "property" field. For more information, see the last section of this article.Sort Order
order: "(asc | desc)"
Sort the field in ascending or descending order. This field is optional.
Default: Sort by data creation time.
Fields Component
- Fields To Return in Query
fields: [ "field-name" ]
List of fields to return in the response. To specify more than one field, separate them with commas. This field is optional.
Default: Return all fields.
Determine Field Names and Data Types
To determine the field names and data types to include in the field and type portions of a query, use the API GET calls whose names end in the string /fields. The Response Body for these calls shows two fields:
- "property"—Property name to include in a field or fields portion of a query.
- "dataType"—Type of data to include in the type portion of a query.
For example, to create a query regarding DPI statistics, use the /statistics/dpi/fields API call. You can issue this call from the following URL:
https://{vmanage-ip-address}/dataservice/statistics/dpi/fields
For dataservice/statistics/dpi/fields, the Response Body looks like this:
[
{
"property": "vip_idx",
"dataType": "number"
},
{
"property": "entry_time",
"dataType": "date"
},
{
"property": "vpn_id",
"dataType": "number"
},
{
"property": "source_ip",
"dataType": "string"
},
{
"property": "dest_ip",
"dataType": "string"
},
{
"property": "source_port",
"dataType": "number"
},
{
"property": "dest_port",
"dataType": "number"
},
{
"property": "octets",
"dataType": "number"
},
{
"property": "packets",
"dataType": "number"
},
{
"property": "application",
"dataType": "string"
},
{
"property": "family",
"dataType": "string"
},
{
"property": "create_time",
"dataType": "date"
},
{
"property": "expire_time",
"dataType": "date"
},
{
"property": "ip_proto",
"dataType": "number"
}
]
Example Simple Query
The following example shows a simple query for the dataservice/event POST call. This query returns, for the last 24 hours, the latest 100 security events whose severity is critical.
{
"size": 100, <== Return 100 records
"query": {
"condition": "AND", <== Records must match all rules; there are three:
"rules": [
{
"value": [ <== Rule #1: Records from the last 24 hours
"24"
],
"field": "entry_time",
"type": "date",
"operator": "last_n_hours"
},
{
"value": [ <== Rule #2: Severity level = critical
"critical"
],
"field": "severity_level",
"type": "string",
"operator": "in"
},
{
"value": [ <== Rule #3: Event type = security
"security"
],
"field": "component",
"type": "string",
"operator": "in"
}
]
}
}
Aggregated Query
Cisco Catalyst SD-WAN Manager aggregate queries retrieve aggregated data from the Cisco Catalyst SD-WAN Manager statistics database. With aggregation, you can to sum, count, and average data in retrieved records, display the minimum and maximum values in the retrieved records, and display a specific record. In addition, you can group and bucketize data.
In an aggregation query, you define the number of records to retrieve, the query conditions and rules, and the operation to aggregate the retrieved data in the output. Each query consists of three sections:
- Size—This field is not used in aggregation queries, and it is optional. If it is present, it must be set to 0.
- Query—Input values for the statistics database query.
- Aggregation—Statistics data fields to include in the query output and the aggregation operations to perform on them.
Query Format
A Cisco Catalyst SD-WAN Manager aggregation query has the following format:
{
"size": 0,
"query":
{
"condition": "AND | OR",
"rules":
[
{
"field": "field-name",
"type": "data-type",
"value":
[
"value"
],
"operator": "operator"
}
]
},
"aggregation":
{
"field":
[
{
"property": "field-name",
"order": "[asc | desc]",
"sequence" : number
},
{
"property": "field-name",
"size": number,
"order": "[asc | desc]",
"sequence" : number
}
],
"metrics":
[
{
"property": "field-name",
"type": "data-type",
"order": "[asc | desc]"
},
],
"histogram":
{
"property": "field-name",
"type": "data-type",
"interval": number,
"order": "[asc | desc]",
},
}
}
Query Components
Size Component
- Number of Records To Return size: 0 (Optional) This field is not used in aggregation queries. If it is present, it must be set to 0. Value: 0
Query Component
In aggregation queries, the Query component is optional. If it is not present, the query collects records for the last 24 hours.
Request for Statistics Records
query:
Define a request to select data from the statistics records.Filter Condition
condition: "(AND | OR)"
Condition to use to filter statistics records if a query has two or more rules. It can be:- AND—Record must match all rules.
- OR—Record must match at least one rule.
Rules for Collecting Statistics Records
rules: [...]
One or more rules to use to collect statistics records.Property Component of a Rule
field: "field-name"
Name of the property for which to select statistics. The property is a string listed in the "property": line of the API call's Response Body. Each rule can contain only one field.
Values: Any string listed in the "property": line of an API call's Response Body.Property Type Component of Rule type: "data-type" Type of property, expressed in the data type associated with the field. The field's data type as shown in the "dataType": line of an API call's Response Body. The following shows how to enter values for each of the dataTypes:
- date—Date and time, in the format yyyy-MM-ddThh:mm:ss (for example, 2017-01-23T15:59:59).
- double—64-bit number that includes a decimal point.
- int—32-bit number that does not include a decimal point.
- long—64-bit number that does not include a decimal point.
- string—Text string. It can include any ASCII and extended ASCII characters, and it can include spaces.
Value Component of a Rule
value: [ "value" ]
Value or values associated with the field property component of the rule. To specify more than one value, separate them with commas.Operator Component of a Rule
operator: "operator"
Operator to perform filtering of statistics records. You use date, numeric, and strings operators. The type of operator in a rule is a function of the field type.- Date Operators
When the field type requires a date or time, specify it in one of the following formats:- between date1, date2—Time range, starting at date1 and ending at date2. Specify the date in the format yyyy-MM-dd.
- last_n_days—Last number of days from the current time. For example, for a current date and time of 2017-01-23T15:59:59, last_3_days returns data from 2017-01-20T15:59:59 through 2017-01-23T15:59:59.
- last_n_hours—Last number of hours from the current time. For example, for a current time of 15:13, last_2_hours returns data from 13:13 through 15:13.
- last_n_weeks—Last number of weeks from the current time. For example, for a current date and time of 2017-01-23T15:59:59, last_2_weeks returns data from 2017-01-09T15:59:59 through 2017-01-23T15:59:59.
- Numeric Operators
When the field type requires a number, specify it in one of the following formats. You cannot include wildcards in these operators.- equal value—Match a value exactly.
- greater value—Match a value greater than the specified number.
- greater_or_equal value—Match a value greater than or equal to the specified number.
- in value[, value]—Match a value that contains the specified number. To specify more than one number, separate them with commas.
- less value—Match a value less than the specified number.
- less_or_equal value—Match a value less than or equal to the specified number.
- not_equal value—Match all values except the specified number.
- not_in value[, value]—Match all values except those that contain the specified number. To specify more than one number, separate them with commas.
- String Operators
When the field type requires a text string, specify it in one of the following formats. You cannot include wildcards in these operators.- equal value—Match a value exactly.
- in value[, value]—Match a value that contains the specified string. To specify more than one string, separate them with commas.
- not_equal value—Match all values except the specified string.
- not_in value[, value]—Match all values except those that contain the specified string. To specify more than one string, separate them with commas.
- Date Operators
Aggregation Component
Group of Fields to Aggregate By
"field" [ { "property": "field-name", "size": number, "order": "(asc | desc)", "sequence": number }Grouping of one of more property fields. This grouping is the same as an SQL Group By statement, in which identical data is arranged into groups. Each group of fields consists of:
- property: "field-name",—Name of a property included in the data statistics. The property is a string listed in the "property": line of the API call's Response Body.
- size: number—Number of statistics data instances to include in the aggregation.
- order: "(asc | desc)"—Define how the field is sorted, either in ascending or descending order.
- sequence: number—Order in which to group the fields. It can be any positive integer.
Aggregation for Properties with Numeric Values
"metrics" [ { "property": "field-name", "type": "aggregation-operation", "order": "(asc | desc)", }Metric aggregation for numeric property fields. Each metric consists of:
- property: "field-name"—Name of a numeric property included in the data statistics. The property is a string listed in the "property": line of the API call's Response Body.
- type: "aggregation-operation"—Type of metric aggregation operation.
It can be one of the following:
- avg—Average the values.
- cardinality—Unique identifier of the value. For example, if the retrieved data includes the same IP address multiple times, display it only once.
- max—Maximum value.
- min—Minimum value.
- sum—Sum of the values.
- order: "(asc | desc)"—Order of the response, either in ascending or descending order.
Aggregation for Properties with Date Fields
"histogram" [ { "property": "field-name", "type": "date-type", "interval": number, "order": "(asc | desc)", }Histogram aggregation for date property fields. Each histogram consists of:
- property: "field-name"—Name of a date property included in the data statistics.
The property is a string listed in the "property": line of the API call's Response Body. - type: "date-type"—Time period over which to display the aggregated data.
It can be one of the following:
- second—Display the data in second groupings.
- minute—Display the data in minute groupings.
- hour—Display the data in hourly groupings.
- day—Display the data in daily groupings.
- month—Display the data in monthly groupings.
- year—Display the data in annual groupings.
- quarter—Display the data in quarterly (3-month) groupings.
- interval: number—Interval to apply to the date-type. This field is optional. If you delete it, the default is 1.
- order: "(asc | desc)"—Order of the response, either in ascending or descending order.
- property: "field-name"—Name of a date property included in the data statistics.
Determine Field Names and Data Types
To determine the field names and data types to include in the field and type portions of a query, use the API GET calls whose names end in the string /fields. The Response Body for these calls shows two fields:
- "property"—Property name to include in a field or fields portion of a query.
- "dataType"—Type of data to include in the type portion of a query.
For example, to create a query regarding DPI statistics, use the /statistics/dpi/fields API call.
You can issue this call from the following URL:https://{vmanage-ip-address}/dataservice/statistics/dpi/fields
For dataservice/statistics/dpi/fields, the Response Body looks like this:
[
{
"property": "vip_idx",
"dataType": "number"
},
{
"property": "entry_time",
"dataType": "date"
},
{
"property": "vpn_id",
"dataType": "number"
},
{
"property": "source_ip",
"dataType": "string"
},
{
"property": "dest_ip",
"dataType": "string"
},
{
"property": "source_port",
"dataType": "number"
},
{
"property": "dest_port",
"dataType": "number"
},
{
"property": "octets",
"dataType": "number"
},
{
"property": "packets",
"dataType": "number"
},
{
"property": "application",
"dataType": "string"
},
{
"property": "family",
"dataType": "string"
},
{
"property": "create_time",
"dataType": "date"
},
{
"property": "expire_time",
"dataType": "date"
},
{
"property": "ip_proto",
"dataType": "number"
}
]
Example Aggregation Query
The following example shows an aggregation query for the /statistics/dpi POST call. The query collects statistics for the last 24 hours, for the IP address 1.1.12.1, and for application families Standard, Encrypted, Network Management, and Network Service. The aggregation portion of the query bucketizes the data. Here, the statistics are aggregated in 30-minute intervals, and for each application family, they contain the total number (sum) of data octets. The output is returned in a json array. The comments in the aggregation portion of the example indicate the order in which the bucketization occurs.
{
"query": {
"condition": "AND", <== Aggregated data must mach all rules; there are three:
"rules": [
{
"value": [ <== Rule #1: Statistics from the last 24 hours
"24"
],
"field": "entry_time",
"type": "date",
"operator": "last_n_hours"
},
{
"value": [ <== Rule #2: IP address = 1.1.12.1
"1.1.12.1"
],
"field": "vdevice_name",
"type": "string",
"operator": "in"
},
{
"value": [ <== Rule #3: Application families
"Standard",
"Encrypted",
"Network Management",
"Network Service"
],
"field": "family",
"type": "string",
"operator": "in"
}
]
},
"aggregation": { <== Define how to bucketize statistics:
"field": [ <== #2: ... group by application family...
{
"property": "family",
"sequence": 1,
"size": 4 <== Return the top 4 families after grouping
}
],
"metrics": [ <== #3: ...and display the total amount of data, in octets.
{
"property": "octets",
"type": "sum"
}
],
"histogram": { <== #1: For every 30 minutes...
"property": "entry_time",
"type": "minute",
"interval": 30,
"order": "asc"
}
}
}