SQM API

Service Quality Management Interface

Once you have deployed ACS and solved the basic tasks of device management, software upgrades, etc., the need arises to solve the tasks of proactive and reactive monitoring of all subscriber devices in the network. Regular collection of various parameters from subscriber devices allows the operator to get a more detailed picture of what's going on directly on the subscriber’s side, and to react to emerging problems in a timely manner, as well as to plan the development of the network and the necessary requirements for subscriber devices.

TR-069 defines a wide range of parameters whose purpose is to help carriers monitor the performance or service status on the CPE.

SQM is designed to collect diagnostic information and monitor terminal equipment controlled in accordance with the requirements of the TR-069/TR-369 protocol and allows you to solve the following tasks:

• Receive information on network devices in a real time mode
• Proactively resolve customer equipment performance issues before the customer calls technical support team
• Localize emergency events in the network
• Reduce time for troubleshooting on terminal equipment
• Reduce operational costs, spent time and resources for technical support of existing and new subscriber devices
• Improve the quality of customer service, allowing to:
• • carry out preventive CPE replacement
• • sell CPEs with higher performance to subscriber
• • recommend the subscriber to change the tariff plan

SQM capabilities

• Support of TR-069 (CWMP) / TR-369 (USP)
• Monitoring and collecting data from the network devices in a real time mode
• Data analysis and visualization based on the obtained results
• Integration with external systems*
• Service quality control (QoS) for compliance with the declared level of service (SLA)
• Generation of data for marketing research (subscribers activity, number of connected devices in the home network, etc.)
• Configurable notification system

SQM API (Integration interface)

Overview

The user interface provides access to the SQM REST API for independent execution of queries, retrieving data and building reports, which extends the use of SQM on the client side.

Swagger GUI URL: https://your_ACS/sqmapi/v1/ui

OpenAPI URL: https://your_ACS/sqmapi/v1/openapi.json

General information about using Swagger is presented in NBI sections Accessing Swagger and Viewing API responses.

The need to insert DMS credentials to use cURL commands also applies to SQM API.

Important note

Parameters are collected from CPEs with "2 PERIODIC" event based on the defined time interval. For first collected data to appear you must wait until KPI collecting period had passed.

Common arguments

Most endpoints have similar arguments:

Parameter	Type	Meaning
"cpeid"	string	Required argument; CPE Serial number.
"offset"	int	Skip the specified number of rows from the beginning of the query result. 0 by default.
"limit"	int	The maximum number of requested rows. 10000 by default.
"start_ts"	int	Timestamp for the starting point for the requested data.
"end_ts"	int	Timestamp for the ending point for the requested data.
"sort_direction"	string	Ascending or descending sort direction; available values : "asc", "desc".

Here are the available SQM methods:

get-available-metrics

The method for obtaining a list of available metrics. To run the query, click "Try it out", as shown in the figure below.

As a result of query execution, a list of metrics available for query will be obtained :

get-network-map

This method uses common arguments.

A method for obtaining a network map by CPEID and classification of connected devices by interface type:

The CPEID parameter is required in the method arguments. The result of this query is a list of devices on the local network connected to the router, sorted by the requested CPEID and their classification according to the type of interface used for connection:

get-associated-devices

This method uses common arguments.

A method for getting a list of devices connected via Wi-Fi by CPEID and their classification by signal quality:

In the method arguments, the CPEID parameter is required. The result of this query is a list of devices connected via Wi-Fi to the requested device (the list of associated devices) and their classification according to the received signal strength indicator (RSSI).

get-power-up-count

This method uses common arguments.

This method allows to get number of power off events of specific device by CPEID for selected time interval:

Since the response is based on the device’s ID the field “CPEID” is required.

get-downtime-of-devices

This method uses common arguments.

This method checks the downtime of a specific device on the basis of time interval when CPE device didn't come to the ACS according to periodic inform interval

Since the response is based on the device's ID the field “CPEID” is required.

get-cpes-metrics

This method allows building a query in the most flexible way. We can get data according to specified criteria, to get aggregated and grouped data, to apply various functions for data processing and to download the resulting data set in JSON, CSV and YAML format.

For this method, there is a scheme at the bottom of the SWAGGER UI that defines the input data, their types, possible values and a description of the arguments:

Below is a table describing the query arguments:

Parameter	Type	Meaning
"table"	string	Required argument; The name of the requested table. The list of tables can be obtained in the get-available-metrics method.
"limit"	int	The maximum number of requested rows. 10000 by default.
"offset"	int	Skip the specified number of rows from the beginning of the query result. 0 by default.
"group_by_ts"	string	Option for grouping data by time. Possible values: minute, hour, day, month, quarter, year.
"group_by_cpeid"	boolean	Option for grouping data by cpeid.
"group_by_tag"	boolean	Option for grouping data by the tag field. Valid if the requested table has this column.
"response_content_type"	string	Option for specifying the desired response format. Possible values: json, csv, yaml. Json format is used by default. If response_content_type is set to csv, the query result can be downloaded as a csv file and opened in Excel, Google Sheets or Numbers utils.
"time_column"	string	Name of the column used for sorting by time; "ts" by default.
"params"	[, ,...]	Required argument; List of requested parameters with different processing options.

Available options for requested parameters:

Parameter	Type	Meaning
"param"	string	Required argument; Name of the requested parameter.
"filter_value"	string, int, list[string,...]	If you want to filter by multiple values, specify all filter values in the array.If you want to set a filter condition > or < for integer type of field, then pass the filter criterion as a string with the symbol > or < at the beginning of the value you are looking for. If you want to filter by matching the beginning or the end of a string, add the * character at the beginning or the end of the string, respectively. Add a symbol ! at the beginning of a string to use the “not equal” operator.
"aggregate"	string	Used in conjunction with data grouping options. Specify one of the available aggregation options. If a grouping option is passed, then for all requested parameters, except for the grouping parameters, the aggregation option must be specified. Possible values: max, min, avg, any, anyLast, anyHeavy, count, countDistinct. anyHeavy - selects the most frequently occurring value in the group, anyLast - selects the last detected value in the group, any - selects the first detected value in the group, count - counts the number of strings in the group, countDistinct - counts the number of unique strings in the group, max - returns the maximum value in the group, min - returns the minimum value in the group, avg - returns the average value in the group.
"convertFunction"	string	Initially, all metrics that store bytes are in bytes. To convert the value to the desired dimensions, pass one of the available conversion functions. Available functions: toKB, toMB, toGB, toBit, toKbit, toMbit, toGbit.
"function"	string	To apply a function to the requested parameter, pass one of the available functions. Available functions: derivative - calculate the derivative for this parameter.
"roundTo"	int	Rounds the requested parameter to the specified digit.
"functionParams"		Additional options when applying the function. Available values: min_derivative_value - lower threshold for the derivative value. If the result of the derivative calculation is less than the specified value, it will be equated to this value. Used to filter negative derivative values max_derivative_value - upper threshold for the derivative value.
"sortDirection"	string	Available values: asc, desc. asc - sorting the query results in the ascending order of the given parameter. desc - sorting in the descending order.
"filterOnly"	boolean	Option that defines whether parameter is used only for WHERE command (true) or it needs to be returned with SELECT command (false).

Examples of using get-cpes-metrics method

1. Get the number of records, grouped by month

Request:

{
  "response_content_type": "json",
  "offset": 0,
  "limit": 100,
  "group_by_ts": "month",
  "params": [
    {
      "param": "cpeid", "aggregate": "count"
    },
    {
      "param": "ts", "sort_direction": "asc"
    }
  ],
  "table": "main"
}

Response:

{
  "status": "success",
  "data": [
    {
      "ts": 1638306000,
      "cpeid": "343333"
    },
    {
      "ts": 1640984400,
      "cpeid": "992737"
    },
    {
      "ts": 1643662800,
      "cpeid": "58335"
    }
  ]
}

2. Get the number of unique CPEID, grouped by month.

Request:

{
  "response_content_type": "csv",
  "offset": 0,
  "limit": 100,
  "group_by_ts": "month",
  "params": [
    {
      "param": "cpeid", "aggregate": "countDistinct"
    },
    {
      "param": "ts", "sort_direction": "asc"
    }
  ],
  "table": "main"
}

Response:

Query result:

If you open the file on a work computer or in Google Sheets, will be displayed the following table:

3. Get the average value of the optical signal power of the receiver and transmitter (optical_rxpower/optical_txpower)

Request:

{
  "response_content_type": "json",
  "offset": 0,
  "limit": 100,
  "group_by_cpeid": true,
  "group_by_ts": "month",
  "params": [
    {
      "param": "optical_rxpower", "aggregate": "avg", "filter_value": "!0"
    },
    {
      "param": "optical_txpower", "aggregate": "avg", "filter_value": "!0"
    },
     {
      "param": "cpeid"
    },
    {
      "param": "ts", "sort_direction": "asc"
    }
  ],
  "table": "main"
}

Response:

Part of the query result shown in JSON format:

{
  "status": "success",
  "data": [
    {
      "ts": 1638306000,
      "optical_rxpower": -23.017131147540997,
      "optical_txpower": 2.851721311475411,
      "cpeid": "testcpe1"
    },
    {
      "ts": 1638306000,
      "optical_rxpower": -23.362314049586786,
      "optical_txpower": 2.66809917355372,
      "cpeid": "testcpe2"
    },
    {
      "ts": 1638306000,
      "optical_rxpower": -22.03745762711866,
      "optical_txpower": 2.815423728813558,
      "cpeid": "testcpe3"
    },
     …
  ]
}

Part of the query result in CSV format:

4. Get devices that have an average power value of the optical signal receiver and transmitter less than -30.

This condition means that the signal strength is too weak to be sent/received. Grouping by day and by CPEID was performed. The query results are sorted by time and by increasing values of optical_rxpower, optical_txpower parameters. The filtering value for ts parameter is transmitted in unixtimestamp format, in this query requests data for the last 5 days.

Request:

{
  "response_content_type": "json",
  "offset": 0,
  "limit": 100,
  "group_by_cpeid": true,
  "group_by_ts": "day",
  "params": [
     {
      "param": "ts", "sort_direction": "asc", "filter_value": ">1643317229"
    },
    {
      "param": "optical_rxpower", "aggregate": "avg", "filter_value": "<-30", "sort_direction": "asc"
    },
    {
      "param": "optical_txpower", "aggregate": "avg", "filter_value": "<-30", "sort_direction": "asc"
    },
     {
      "param": "cpeid"
    }
  ],
  "table": "main"
}

Response:

Part of the query result :

{
  "status": "success",
  "data": [
    {
      "ts": 1643317200,
      "optical_rxpower": -40,
      "optical_txpower": -40,
      "cpeid": "testcpe1"
    },
    {
      "ts": 1643317200,
      "optical_rxpower": -40,
      "optical_txpower": -40,
      "cpeid": "testcpe2"
    },
    {
      "ts": 1643403600,
      "optical_rxpower": -40,
      "optical_txpower": -40,
      "cpeid": "testcpe3"
    },
     …
  ]
}

The SQM service collects statistics on LAN, WAN ports, WLAN interface and other metrics from devices, which are counters. In its raw form, this information is not of great interest, since it is difficult to assess the rate of change in the readings of these counters. To do this, you can use the function of calculating the derivative.

5. Calculation of the derivative with respect to the metric

This query provides an example of derivative calculation. That is, as a result of calculating the derivative we shall get the rate of increase, for example packets/sec, errors/sec, bytes/sec etc. To convert bytes to MB, Kb, Mbit etc. for parameters the option convertFunction will be passed, where we specify the desired dimension. Initially, the parameter that contains the numbers of bytes has the dimension of bytes. The shorter the data collection period, the more accurate the result. Get the derivative by the number of bytes sent/received in Mbit/sec, sent/received packets - packets/sec, errors - errors/sec for all lan ports and for all devices for the last 5 days, where the number of bytes sent/received is greater than 0.

Request:

{
  "response_content_type": "json",
  "offset": 0,
  "limit": 100,
  "params": [
 	{
  	"param": "ts", "filter_value": ">1643317229"
	},
 	{
  	"param": "cpeid"
	},
 	{
  	"param": "tag"
	},
	{
 "param": "bytesreceived", "function": "derivative", "functionParams": {"min_derivative_value": 0}, "convertFunction": "toMbit", "filter_value": ">0"
	},
	{
  	"param": "bytessent", "function": "derivative","functionParams": {"min_derivative_value": 0},"convertFunction": "toMbit", "filter_value": ">0"
	},
	{
  	"param": "errorsreceived", "function": "derivative","functionParams": {"min_derivative_value": 0}
	},
	{
  	"param": "errorssent", "function": "derivative","functionParams": {"min_derivative_value": 0}
	},
	{
  	"param": "packetsreceived", "function": "derivative","functionParams": {"min_derivative_value": 0}
	},
	{
  	"param": "packetssent", "function": "derivative","functionParams": {"min_derivative_value": 0}
	}
  ],
  "table": "lan_port"
}

Response:

Part of the query result :

{
  "status": "success",
  "data": [
    {
      "ts": 1643778138,
      "cpeid": "testcpe1",
      "tag": "eth0",
      "bytesreceived": 0.00035,
      "bytessent": 0.00069,
      "errorsreceived": 0,
      "errorssent": 0,
      "packetsreceived": 0.27811,
      "packetssent": 0.18693
    },
    {
      "ts": 1643792539,
      "cpeid": "testcpe2",
      "tag": "eth0",
      "bytesreceived": 0.00028,
      "bytessent": 0.00014,
      "errorsreceived": 0,
      "errorssent": 0,
      "packetsreceived": 0.24005,
      "packetssent": 0.13075
    },

     …
  ]
}

Parameters “cpeid” and “tag” must be passed, otherwise a respective error will be raised.

6. To get devices with the average response time for a sent icmp packet during ping diagnostics greater than 200 ms.

The data is grouped by days and by CPEID.

Request:

{
  "response_content_type": "json",
  "offset": 0,
  "limit": 100,
  "group_by_ts": "day",
  "group_by_cpeid": true,
  "params": [
     {
      "param": "ts"
    },
    {  
       "param": "cpeid"
    },
    {
      "param": "averageresponsetime", "aggregate": "avg", "filter_value": ">200"
    }
  ],
  "table": "ping"
}

Response:

Part of the query result:

7. Get devices connected via WIFI - RSSI with the average signal strength value less than -70.

The signal level (RSSI) less than -70 is considered weak, and the stability of the Internet on devices connected to the router via Wi-Fi deteriorates significantly. The data is grouped by day, by CPEID and associated devices and sorted by increasing RSSI.

Request:

{
  "response_content_type": "csv",
  "offset": 0,
  "limit": 100,
  "group_by_ts": "day",
  "group_by_cpeid": true,
  "group_by_tag": true,
  "params": [
     {
      "param": "ts"
    },
    {  
       "param": "cpeid"
    },
    {
       "param": "tag"
    },
    {
      "param": "signalstrength", "aggregate": "avg", "filter_value": "<-70", "sort_direction": "asc"
    }
  ],
  "table": "associated_device_2"
}

Response:

Part of the query result: