In this article:

Contents Search
   

 

Query API

The Kentik V5 Query API enables programmatic querying of network data (flow records, BGP, selected SNMP OIDs) from your company’s devices (routers, hosts, etc.) stored in Kentik Detect. The V5 Query API is covered in the following topics:

Notes:
- For an overview of all Kentik APIs, see APIs Overview.
- For documentation of the V5 Admin APIs, see V5 Admin APIs.
- For information on using the Kentik V5 Query API with cURL, see API Access Via cURL.
- For assistance using any API version please contact support@Kentik.com.

 

 
 top

About V5 Query API

The Kentik V5 Query API includes methods for returning information from the main tables of the Kentik Data Engine (see KDE Tables), either as data in JSON or as image file data representing charts like those displayed in Data Explorer.

Kentik V5 Query APIs may be accessed as follows:

  • V5 API Tester: Use the V5 API tester (ioDocs), which allows you to try the API calls on real data from your Kentik Detect service.
  • Command line: Use cURL (https://curl.haxx.se/) in an environment like Terminal. See API Access Via cURL.
  • Programmatically: Pass a request body to a V5 API endpoint using any application language that supports the HTTP protocol.

 

 
 top

V5 Query Methods

The following table shows the methods that are available in the V5 Query API (click on the topic link for more information about a specific method):

Method Endpoint Description Topic
Query API
POST /topXchart Returns a graph as image file data. Query Chart Method
POST /url Returns a URL that will open Data Explorer in the Kentik Detect portal with the sidebar set to query parameters defined in the request JSON. Query URL Method
POST /topXdata Returns JSON data that can be used to generate tables and time-series graphs like those displayed in Data Explorer. Query Data Method
POST /sql Retrieve data (in a JSON array) from the KDE using a SQL query. Query SQL Method

Note: The Query SQL method requires detailed knowledge of PostgreSQL and of Kentik’s implementation of subqueries (see Querying KDE). To return KDE data as JSON, most API users will achieve more accurate results with less development effort using the Query Data call.

 

 
 top

Query API JSON

The JSON used for requests and responses by the V5 Query API methods is covered in the following topics:

 

 
 top  |  section

Query API Request JSON

The Query Chart, Query Data, and Query URL methods of the V5 Query API both use JSON in the request body (the Query SQL Method uses SQL in the request body instead). The JSON array passed into these calls tells Kentik Detect what data to return and how to return it. This JSON is mostly the same for all three calls.

You can create the JSON array for a Query Chart, Query Data, or Query URL call in either of the following ways:

  • Build the JSON from scratch based on the information in the tables below.
  • Get the JSON from the Kentik Detect portal:
    1. In the portal’s Data Explorer (click Data Explorer on the portal’s main navbar), define a query by specifying the fields in the panes of the sidebar, including Query, Time, and Devices as well as, optionally, Filters. Click the Apply Changes button.
    2. When the results of the query are rendered, click the drop-down options menu at the upper right of the graph (see Explorer Title Pane), choose Show API Call, then choose Data or Chart. The Query API example dialog will open.
    3. Click the JSON button at top. The query JSON will appear in the code field.
    4. Click the Copy button at bottom. The JSON will be copied to the clipboard.
    5. Paste the JSON to use it in a Query Data call in cURL (see below) or other setting that supports programmatically generated HTTP calls.

The following example illustrates the objects used by the Query Chart, Query Data, and Query URL methods (with placeholder values highlighted), which are described in the sections below.

{
  "queries": [
    {
      "query": {
        "viz_type": "matrix",
        "show_overlay": false,
        "overlay_day": -7,
        "sync_axes": false,
        "metric": "bytes",
        "matrixBy": [
          "dst_geo_city"
        ],
        "cidr": 32,
        "cidr6": 128,
        "pps_threshold": 500,
        "topx": 8,
        "depth": 100,
        "fastData": "Auto",
        "outsort": "max_bits_per_sec",
        "lookback_seconds": 3600,
        "time_format": "UTC",
        "hostname_lookup": "false",
        "starting_time": null,
        "ending_time": null,
        "device_name": "device_name_1, device_name_2",
        "all_selected": true,
        "filters_obj": {
          "connector": "All",
          "filterGroups": [
            {
              "connector": "All",
              "filters": [
                {
                  "filterField": "protocol",
                  "operator": "=",
                  "filterValue": "17",
                }
              ],
              "not": false,
            }
          ],
        },
        "saved_filters": [
          {
            "filter_id": 363
            "is_not": true
          }
        ],
        "descriptor": "Total",
        "aggregates": [
          {
            "name": "avg_bits_per_sec",
            "column": "f_sum_both_bytes",
            "fn": "average",
            "sample_rate": 1
          },
          {
            "name": "p95th_bits_per_sec",
            "column": "f_sum_both_bytes",
            "fn": "percentile",
            "rank": 95,
            "sample_rate": 1
          },
          {
            "name": "max_bits_per_sec",
            "column": "f_sum_both_bytes",
            "fn": "max",
            "raw": true,
            "sample_rate": 1
          }
        ],
        "query_title": "Top Source City vs Top Dest City by Max Bits/s",
        "dimension": [
          "src_geo_city"
        ]
      },
      "bucket": "Matrix",
      "bucketIndex": 0,
      "isOverlay": false
    }
  ],
  "imageType": "image_file_format"
}

 

Query JSON Object

The highest-level object of the V5 Query JSON contains the following two members:

Field Type Description
queries array One or more query objects, each element of which includes a query object and additional associated members. See Queries Array.
imageType string Required: The format of the image file in which to return a chart: pdf, png, jpg, or svg
- Used only in Query Chart method.

 

Queries Array

The queries array typically contains one element consisting of a query object and the associated objects shown in the following table. To return data or a chart for multiple data-series (see Compound Queries), include multiple elements, one for each desired axis of the multi-series chart.

Field Type Description
query object An object whose members define a query to be run on Kentik Detect. See Query Object.
bucket string A name for a set of queries that are to be run simultaneously (multi-series graphing). Queries can inherit certain values from queries at a lower bucketIndex.
bucketIndex number The index of a query within its bucket.
- Valid values: integer greater than -1
isOverlay boolean Sets whether or not query should be treated like an overlay.
- Used only in these methods: Query Chart, Query URL.

 

Query Object

The query object contains the objects and arrays that define a query to be run on Kentik Detect:

Field Type Description
viz_type string Type of chart (see Chart View Types).
- Used only in these methods: Query Chart, Query URL.
- Valid values: stackedArea (default), line, stackedBar, bar, pie, sankey, table, matrix
show_overlay boolean Sets whether or not to show historical overlay (see Query Advanced Options).
- Ignored unless overlay is supported by specified viz_type.
- Used only in these methods: Query Chart, Query URL.
overlay_day number Number of days to look back for historical overlay.
- Ignored unless show_overlay is true and overlay is supported by specified viz_type.
- Used only in these methods: Query Chart, Query URL.
sync_axes boolean Sets whether or not you want left and right axes scales to be synced.
- Used only for Compound Queries.
- Used only in these methods: Query Chart, Query URL.
metric string Required: Unit of measure, e.g. bytes, packets, etc. See About Metrics.
- Valid value: bytes, in_bytes, out_bytes, packets, in_packets, out_packets, tcp_retransmit, perc_retransmit, retransmits_in, perc_retransmits_in, out_of_order_in, perc_out_of_order_in, fragments, perc_fragments, client_latency, server_latency, appl_latency, fps, unique_src_ip, or unique_dst_ip
matrixBy array One or more matrix-with dimensions (see Matrix View) specified in a JSON array.
- Valid values: any combination of standard dimensions (see Main Table Schema) and/or custom dimensions.
cidr number
A CIDR filter to use on IP queries, such as when grouping by dest_ip/24. See CIDR-related Subqueries.
- Valid values: digit between 0 and 32.
cidr6 number A CIDR6 filter to use on IP queries.
- Valid values: digit between 0 and 128.
pps_threshold number The minimum packets per second required to match flow (used in conjunction with metrics like retransmits, out of order, etc.).
- Valid values: digit greater than 0.
topx number The number of topX rows from the pool of returned results (size of which is determined by the depth), that will be returned as time-series data and (for topXchart) included in the chart.
- Valid values: digit between 1 and 40.
depth number The number of results in the pool from which topX will be determined.
- Valid values: digit between 25 and 250.
fastData string Forces selection of the dataset on which to run the query, overriding the default. See API Resolution Selection.
- Valid values: Auto, Fast, Full
outsort string The name of an aggregate object by which to sort results for depth and topx.
- Required if the query includes more than one user-specified aggregate (see aggregates below).
- If the query contains no user-specified aggregates, this field may optionally be used to specify the Kentik-derived aggregate (see Kentik Sort-by Options) by which results will be sorted.
lookback_seconds number The time period to look back from present, e.g. for time span of last 2 hours, set to 7200.
- Required unless starting_time and ending_time are set.
- Unless set to 0, this overrides the starting_time and ending_time settings.
time_format string The time zone in which times are expressed, either UTC or Local.
- Used only in the Query URL method.
hostname_lookup boolean When true (default), allow DNS lookup.
Note: Set to false to speed queries against IP dimensions.
starting_time string The fixed start time of a query time-span, e.g. ‘YYYY-MM-DD HH:mm:00’.
- Ignored unless lookback_seconds is zero.
ending_time string The fixed end time of a query time-span, e.g. ‘YYYY-MM-DD HH:mm:00’.
- Ignored unless lookback_seconds is zero.
device_name string List of names of the devices to query.
- To set multiple devices, use a comma delimited string.
all_selected boolean Query against all devices.
- If true, device_name is ignored.
filters_obj array See Filters_obj Object.
saved_filters array See Saved_filters Array.
descriptor string The name with which results from this query will be labeled when the queries array includes multiple query elements (Multi-series Graphing mode).
- Ignored unless dimension is “traffic.”
aggregates array See Aggregates Array.
query_title string The title of a query (appears in the returned chart).
dimension string Required: One or more group-by dimensions, specified in a JSON array.
- Valid values: any combination of standard dimensions (see Main Table Schema) and/or custom dimensions.
- Default: AS_dst
- Valid values: AS_src, Geography_src, InterfaceID_src, Port_src, src_eth_mac, VLAN_src, IP_src, AS_dst, Geography_dst, InterfaceID_dst, Port_dst, dst_eth_mac, VLAN_dst, IP_dst, TopFlow, Proto, Traffic, ASTopTalkers, InterfaceTopTalkers, PortPortTalkers, TopFlowsIP, src_geo_region, src_geo_city, dst_geo_region, dst_geo_city, RegionTopTalkers, i_device_id, i_device_site_name, src_route_prefix_len, src_route_length, src_bgp_community, src_bgp_aspath, src_nexthop_ip, src_nexthop_asn, src_second_asn, src_third_asn, src_proto_port, dst_route_prefix_len, dst_route_length, dst_bgp_community, dst_bgp_aspath, dst_nexthop_ip, dst_nexthop_asn, dst_second_asn, dst_third_asn, dst_proto_port, inet_family, TOS, tcp_flags

 

Filters_obj Object

The filters_obj object is a container for a set of one of more filter groups for a Query Chart or Query Data query. For more information on filters in a query see Filtering Pane Settings.

Field Type Description
connector string Sets the conjunctive operator (“and” or “or”) that will join all filter groups in this filter set.
filterGroups array See FilterGroups Array.

 

FilterGroups Array

The filterGroups array defines a group of one or more filters for a Query Chart or Query Data query. For more information on filters in a query see Filtering Pane Settings. Each member of the array contains the following objects:

Field Type Description
connector string Sets the conjunctive operator (“and” or “or”) that will join all filters in this filter group.
filters string See Filters Array.
not boolean Sets whether traffic matching the filter is included (false) or excluded (true).

 

Filters Array

The filters array is used to define one or more individual filters of a filter group in a Query Chart or Query Data query. For more information on filters in a query see Filtering Pane Settings. Each member of the array corresponds to an individual filter and contains the following objects:

Field Type Description
filterField string The dimension to filter on.
- Valid values: src_geo, src_geo_region, src_geo_city, src_as, src_flow_tags, l4_src_port, vlan_in, src_eth_mac, inet_src_addr, input_port, i_input_snmp_alias, i_input_interface_description, ipv4_src_route_prefix, src_route_length, src_bgp_aspath, src_bgp_community, ipv4_src_next_hop, src_nexthop_as, src_second_asn, src_third_asn, dst_geo, dst_geo_region, dst_geo_city, dst_as, dst_flow_tags, l4_dst_port, vlan_out, dst_eth_mac, inet_dst_addr, output_port, i_output_snmp_alias, i_output_interface_description, ipv4_dst_route_prefix, dst_route_length, dst_bgp_aspath, dst_bgp_community, ipv4_dst_next_hop, dst_nexthop_as, dst_second_asn, dst_third_asn, tcp_flags, tcp_flags_raw, protocol, i_device_name, both_pkts, tcp_retransmit, or tos
operator string The operator to apply in the filter.
- Valid value: “=“, “<>“, “ILIKE”, “NOT ILIKE”, “˜”, “!˜”, “>“, “<“, “&”
filterValue string The filterField value to match.

 

Saved_filters Array

The saved_filters array is a list of IDs referencing saved filters (custom or preset; see Saved Filters) that you wish to apply to the query.

Field Type Description
filter_id number The ID of a saved filter.
is_not boolean Sets whether traffic matching the entire saved filter set is included (false) or excluded (true).
- If not specified, defaults to false.

 

Aggregates Array

The aggregates array defines an aggregate for a column in a table that is returned from a Query Chart query when the display type (viz_type; see Query Object) is Table. Simply put, an aggregate is the result of simple statistical analyses across a dataset.

Field Type Description
name string The name of the aggregate.
column string The raw SQL column (see Main Table Schema) to use as the source for this aggregation.
fn string The aggregation function to run against the column: sum, average, percentile, max, composite, exponent, modulus, greaterThan, greaterThanEquals, lessThan, lessThanEquals, equals, or notEquals
rank number The percentile to apply in an aggregate function.
- Ignored unless fn is “percentile”
- Valid value: integer from 5 to 99.
sample_rate number Set a rate at which the values will be sampled, e.g. multiply by -1 (-1), or divide by 1,000,000 (1/1,000,000).

 

Kentik Sort-by Options

If no aggregates are included in the Query Object then Kentik will automatically generate a set of aggregates for the query based on the value of the metric field (query object). Like user-specified aggregates, the name of one of these Kentik-generated aggregates may be specified in the outsort field (query object) to determine the aggregate object by which results will be sorted for depth and topx. The following table shows the names of the Kentik-generated aggregates that are valid in the outsort field for each metric.

Metric Aggregate name
bytes avg_bits_per_sec
p95th_bits_per_sec
max_bits_per_sec
in_bytes avg_in_bits_per_sec
p95th_in_bits_per_sec
max_in_bits_per_sec
out_bytes avg_out_bits_per_sec
p95th_out_bits_per_sec
max_out_bits_per_sec
packets avg_pkts_per_sec
p95th_pkts_per_sec
max_pkts_per_sec
in_packets avg_in_pkts_per_sec
p95th_in_pkts_per_sec
max_in_pkts_per_sec
out_packets avg_out_pkts_per_sec
p95th_out_pkts_per_sec
max_out_pkts_per_sec
tcp_retransmit sum_rxmits
avg_rxmits_per_sec
p98th_rxmits_per_sec
max_rxmits_per_sec
p98th_perc_rxmits
max_perc_rxmits
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
perc_retransmit sum_rxmits
avg_rxmits_per_sec
p98th_rxmits_per_sec
max_rxmits_per_sec
p98th_perc_rxmits
max_perc_rxmits
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
retransmits_in sum_rxmits
avg_rxmits_per_sec
p98th_rxmits_per_sec
max_rxmits_per_sec
rxmits_to_pkts_ratio
perc_rxmits
p98th_perc_rxmits
max_perc_rxmits
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
perc_retransmits_in sum_rxmits
avg_rxmits_per_sec
p98th_rxmits_per_sec
max_rxmits_per_sec
rxmits_to_pkts_ratio
perc_rxmits
p98th_perc_rxmits
max_perc_rxmits
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
out_of_order_in sum_ooorder
avg_ooorder_per_sec
p98th_ooorder_per_sec
max_ooorder_per_sec
ooorder_to_pkts_ratio
perc_ooorder*p98th_perc_ooorder
max_perc_ooorder
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
perc_out_of_order_in sum_ooorder
avg_ooorder_per_sec
p98th_ooorder_per_sec
max_ooorder_per_sec
ooorder_to_pkts_ratio
perc_ooorder
p98th_perc_ooorder
max_perc_ooorder
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
fragments sum_fragments
avg_fragments_per_sec
p98th_fragments_per_sec
max_fragments_per_sec
fragments_to_pkts_ratio
perc_fragments
p98th_perc_fragments
max_perc_fragments
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
perc_fragments sum_fragments
avg_fragments_per_sec
p98th_fragments_per_sec
max_fragments_per_sec
fragments_to_pkts_ratio
perc_fragments
p98th_perc_fragments
max_perc_fragments
avg_bits_per_sec
sum_pkts
avg_pkts_per_sec
p98th_pkts_per_sec
client_latency sum_client_latency_ms
avg_client_latency_ms
max_client_latency_ms
sum_flows
p98th_bits_per_sec
p98th_pkts_per_sec
server_latency sum_server_latency_ms
avg_server_latency_ms
max_server_latency_ms
sum_flows
p98th_bits_per_sec
p98th_pkts_per_sec
appl_latency sum_appl_latency_ms
avg_appl_latency_ms
max_appl_latency_ms
sum_flows
p98th_bits_per_sec
p98th_pkts_per_sec
fps avg_flows_per_sec
p95th_flows_per_sec
max_flows_per_sec
p95th_bits_per_sec
p95th_pkts_per_sec
unique_src_ip max_ips
p95th_bits_per_sec
p95th_pkts_per_sec
unique_dst_ip max_ips
max
p95th_bits_per_sec
p95th_pkts_per_sec

 

 
 top  |  section

Query JSON as cURL

The following code sample shows the JSON for a Query Chart call within the data (-d) parameter of a cURL HTTP request (with placeholders highlighted):

curl -X POST \
-H 'X-CH-Auth-Email: user@domain.suffix' \
-H 'X-CH-Auth-API-Token: user_api_token' \
-H 'Content-Type: application/json' \
-d '{ "queries": [ { "query": { "viz_type": "stackedArea", "show_overlay": true, "overlay_day": -7, "sync_axes": false, "metric": "bytes", "matrixBy": [], "cidr": 32, "cidr6": 128, "pps_threshold": 500, "topx": 8, "depth": 25, "fastData": "Auto", "outsort": "max_bits_per_sec", "lookback_seconds": 300, "time_format": "UTC", "starting_time": null, "ending_time": null, "device_name": "device_names", "all_selected": false, "filters_obj": {}, "saved_filters": [], "descriptor": "Total", "query_title": "Top Source Country by Max Bits/s", "time_type": "relative", "device_id": "2951", "num_selected": 0, "sql_string": "", "queryChanged": false, "granularity": "total", "ppsThreshold": "500", "last_run": null, "aggregates": [ { "name": "avg_bits_per_sec", "column": "f_sum_both_bytes", "fn": "average", "sample_rate": 1 }, { "name": "p95th_bits_per_sec", "column": "f_sum_both_bytes", "fn": "percentile", "rank": 95, "sample_rate": 1 }, { "name": "max_bits_per_sec", "column": "f_sum_both_bytes", "fn": "max", "raw": true, "sample_rate": 1 } ], "dimension": [ "Geography_src" ] }, "bucket": "Left +Y Axis", "bucketIndex": 0, "isOverlay": false } ], "imageType": "pdf"}' \
https://api.kentik.com/api/v5/query/topXchart | python -m json.tool

Notes:
- The above example includes placeholders (in italics) that must be replaced with actual values, including your email address (user@domain.suffix), your API token (user_api_token), and the names of the devices (comma-delimited list) on which you wish to run the query (device_names).
- The JSON (value of -d parameter) for a Query Data call would be similar but omit certain fields; see the individual field descriptions in Query API Request JSON.
- For more information on using Kentik APIs with cURL, see API Access Via cURL.

 

 
 top  |  section

Query API Response JSON

The Query Data and Query SQL methods both return an array of JSON data in the HTTP response body (the Query Chart method returns an image file instead). In both cases this data is drawn from the main tables of the Kentik Data Engine (see KDE Tables), which store flow records and associated network data (BGP, GeoIP, SNMP). The specific objects included in the arrays returned from the two methods will differ, however, even when the query that was passed into each call is based on the same underlying settings (e.g. set a query in Data Explorer, then use Show API Call to grab the query for a Query Data call vs. View SQL for a Query SQL call). That’s because the results returned from the Query Data method are processed for improved accuracy (e.g. in queries involving averaging).

Note: An example of response JSON is provided in the topic for each method.

 

 
 top

Query Chart Method

This POST method queries Kentik Detect’s KDE and returns image data for a graph similar to what is seen in the data display area of the Data Explorer in the Kentik Detect portal (see Data Explorer Chart). The binary data for the image is returned in the HTTP response body as a JSON object with one dataURI member, the value of which is a base64 encoded string that represents a file.

Supported types for the returned image data are SVG, PDF, PNG, or JPG. The default and recommended image type is SVG, which serves the fastest, scales losslessly, and is the simplest to integrate as it can be rendered by simply injecting it directly into the DOM.

Note: To try this call, go to the V5 API Tester.

HTTP Request

The following table shows the path and HTTP request for this call (with placeholders in italics):

URL https://api.kentik.com/api/v5/query/topXchart
Request POST /api/v5/query/topXchart HTTP/1.1
Host: api.kentik.com
X-CH-Auth-Email: user@domain.suffix
X-CH-Auth-API-Token: user_api_token
Content-Type: application/json

The request body includes a JSON array whose elements contain the information needed by Kentik Detect to perform the query. For information about creating this array and definitions of its component parts, see Query API Request JSON.

HTTP Response

A successful response to this call includes the following elements:

  • The response headers.
  • The HTTP response code.
  • A response body containing a JSON object with one dataURI member.

The following example shows the structure of the returned JSON (placeholder highlighted):

{
  "dataUri": "base64_encoded_string"
}


 

 
 top

Query URL Method

This POST method returns a URL that will open Data Explorer in the Kentik Detect portal with the sidebar set to correspond to the query parameters defined in the fields of the request JSON, and the query result visualized (graphed) in the display area.

Note: To try this call, go to queryURL under Query methods in the V5 API Tester.

HTTP Request

The following table shows the path and HTTP request for this call (with placeholders in italics):

URL https://api.kentik.com/api/v5/query/url
Request POST /api/v5/query/url HTTP/1.1
Host: api.kentik.com
X-CH-Auth-Email: user@domain.suffix
X-CH-Auth-API-Token: user_api_token
Content-Type: application/json

The request body includes a JSON array whose elements contain the information needed by Kentik Detect to specify the settings for the query that will run when Data Explorer is opened. For information about creating this array and definitions of its component parts, see Query API Request JSON.

Note: The request body for this call is identical in every respect to a request for Query Chart Method, with the sole exception being that this call does not use the imageType param.

HTTP Response

A successful response to this call includes the following elements:

  • The response headers.
  • The HTTP response code.
  • A response body containing a short URL (in quotes).

The following example shows an example of a URL returned from this call:

"https://portal.kentik.com/portal/#Charts/shortUrl/d3f24034b386ebf9e8ad6de8f0f715a1"


 

 
 top

Query Data Method

This POST method runs a query on data in Kentik Detect’s KDE (see KDE Tables) and returns results in a JSON results array.

Notes:
- Most API users will achieve more accurate results with less development effort using this method than with the Query SQL Method, which requires detailed knowledge of PostgreSQL and of Kentik’s implementation of subqueries (see Querying KDE).
- To try this call, go to the V5 API Tester.

HTTP Request

The following table shows the path and HTTP request for this call (with placeholders in italics):

URL https://api.kentik.com/api/v5/query/topXdata
Request POST /api/v5/query/topXdata HTTP/1.1
Host: api.kentik.com
X-CH-Auth-Email: user@domain.suffix
X-CH-Auth-API-Token: user_api_token
Content-Type: application/json

The request body includes a JSON array whose elements contain the information needed by Kentik Detect to perform the query. For information about creating this array and definitions of its component parts, see Query API Request JSON.

HTTP Response

A successful response to this call includes the following elements:

  • The response headers.
  • The HTTP response code.
  • A response body containing a results array in JSON. The elements included in the array depend on the query passed into the call.

The following example shows the results array from a simple query on top source country by maximum bits per second, abbreviated to show just two results (normally the actual number of results corresponds to the value of the depth field in the request JSON, with a minimum of 25):

  • The first result (US), which includes time-series data for a timespan of five minutes (lookback_seconds set to 300), is an example of what returns for the first number of results, where number corresponds to the value of topx field in the request JSON (see Query API Request JSON).
  • The second result (AU) is an example of non-topx results.

{
  "results": [
    {
      "bucket": "Left +Y Axis",
      "data": [
        {
          "Geography_src": "US",
          "avg_bits_per_sec": 130548781.51111111,
          "key": "US",
          "max_bits_per_sec": 153616657.06666666,
          "p95th_bits_per_sec": 150816290.13333333,
          "timeSeries": {
            "both_bits_per_sec": {
              "flow": [
                [
                  1482186900000,
                  128568524.8,
                  60
                ],
                [
                  1482186960000,
                  133069482.66666667,
                  60
                ],
                [
                  1482187020000,
                  115999675.73333333,
                  60
                ],
                [
                  1482187080000,
                  153616657.06666666,
                  60
                ],
                [
                  1482187140000,
                  104022425.6,
                  60
                ],
                [
                  1482187258438,
                  148015923.2,
                  60
                ]
              ]
            }
          }
        },
        {
          "Geography_src": "AU",
          "avg_bits_per_sec": 732114.4888888889,
          "key": "AU",
          "max_bits_per_sec": 1145924.2666666666,
          "p95th_bits_per_sec": 1003315.2
        }
      ]
    }
  ]
}


 

 
 top

Query SQL Method

This POST method runs a SQL query on data in Kentik Detect’s KDE. The query’s results are returned in a JSON rows array whose elements each correspond to column in the KDE Tables.

Notes:
- This method requires detailed knowledge of PostgreSQL and of Kentik’s implementation of subqueries (see Querying KDE). To return KDE data as JSON, most API users will achieve more accurate results with less development effort using the Query Data Method.
- To try this call, go to the V5 API Tester.

HTTP Request

The following table shows the path and HTTP request for this call (with placeholders in italics):

URL https://api.kentik.com/api/v5/query/sql
Request POST /api/v5/query/sql HTTP/1.1
Host: api.kentik.com
X-CH-Auth-Email: user@domain.suffix
X-CH-Auth-API-Token: user_api_token
Content-Type: application/json

The request body includes a single parameter, which is a SQL query. You can create this query in either of the following ways:

  • Write the query from scratch. For guidance on writing a Kentik-compliant query, see Querying KDE.
  • Create the query in the Kentik Detect portal:
    1. In the portal’s Data Explorer (click Data Explorer on the portal’s main navbar), define a query by specifying the fields in the panes of the sidebar, including Query, Time, and Devices as well as, optionally, Filters. Click the Apply Changes button.
    2. When the results of the query are rendered, click the drop-down Options menu at the upper right of the graph (see Explorer Title Pane) and choose View SQL. From the resulting menu options choose the type of view (e.g. “Total”) and then the type of SQL (e.g. “Chart SQL”). The Query Editor will open, with the text of the SQL query displayed in the Enter SQL Query field.
    3. Copy the SQL to use it in a Query call in cURL (see below) or other setting that supports programmatically generated HTTP calls.

Note: Queries generated in the Query Editor may contain single quotes. Depending on the programmatic context within which copied queries are used you may need to escape these quotes. In cURL, for example, replace single quotes with the unicode equivalent \u0027 as shown in the following example WHERE clause.

-- original
WHERE i_sub_limit = 200
  AND i_start_time >= '2016-12-01 21:54'
-- escaped
WHERE i_sub_limit = 200
  AND i_start_time >= \u00272016-12-01 21:54\u0027

Request as cURL

The following code sample shows a Query SQL call as a cURL HTTP request (with placeholders highlighted):

curl -X POST \
-H 'X-CH-Auth-Email: user@domain.suffix' \
-H 'X-CH-Auth-API-Token: user_api_token' \
-H 'Content-Type: application/json' \
-d '{
  "query": "SELECT * FROM (SELECT i_start_time, dst_geo, CASE dst_geo WHEN \u0027\u0027 THEN \u0027---\u0027 ELSE dst_geo END AS Geography_dst, sum(both_bytes) AS f_sum_both_bytes, max(i_duration) as i_duration, row_number() OVER (PARTITION BY i_start_time ORDER BY sum(both_bytes) DESC) FROM all_devices WHERE ( ( ( (src_geo = \u0027RU\u0027) ) ) AND ( ( (i_dst_as_name ILIKE \u0027%kentik%\u0027) ) )) AND i_sub_limit = 200 AND i_start_time >= \u00272016-12-01 21:28\u0027 GROUP BY i_start_time, dst_geo ORDER BY f_sum_both_bytes DESC) a WHERE row_number <= 200 ORDER BY i_start_time, f_sum_both_bytes DESC"
  }' \
https://api.kentik.com/api/v5/query/sql | python -m json.tool

Note: For more information on using Kentik APIs with cURL, see API Access Via cURL.

HTTP Response

A successful response to this call includes the following elements:

  • The response headers.
  • The HTTP response code.
  • A response body containing a rows array in JSON. The elements included in the array depend on the query passed into the call.

The following example shows the JSON rows array that would be returned from a query like that shown in the cURL example above:

{
  "rows": [
    {
      "dst_geo": "US",
      "f_sum_both_bytes": 9856,
      "geography_dst": "US",
      "i_duration": 300,
      "i_start_time": "2016-12-01T21:25:00Z",
      "row_number": 1
    },
    {
      "dst_geo": "US",
      "f_sum_both_bytes": 16000,
      "geography_dst": "US",
      "i_duration": 300,
      "i_start_time": "2016-12-01T21:30:00Z",
      "row_number": 1
    },
    {
      "dst_geo": "US",
      "f_sum_both_bytes": 26400,
      "geography_dst": "US",
      "i_duration": 300,
      "i_start_time": "2016-12-01T21:35:00Z",
      "row_number": 1
    }
  ]
}