KDE Tables

This article covers the following topics related to the tables of the Kentik Data Engine (KDE):

• About KDE Tables
• Universal Data Records
• Main Table Columns
• Using Main Tables
• All Devices Table

Notes:
- For general information about KDE, see KDE Overview.
- For information about querying Kentik Data Engine see Querying KDE.

KDE keeps a discrete set of databases for the flow records of each customer. These databases are made up of “main tables” for the flow records and associated data received for each device (router or host) as well as for supplemental data that is derived from the flow data. The data for all devices of a given customer are also merged into that customer's All Devices Table.

The columns in these tables form the basis for the dimensions (see Dimensions Reference) that are used for filtering and group-by in Kentik queries. Most of these dimensions are exposed in the portal UI's dimension selectors for filtering (see Dimension Selector Dialog) and group-by (see Dimension Selectors). A small number of additional dimensions, covered in the topics below, are available for use only when querying via the Query SQL Method of the Query API.

Universal Data Records is Kentik's technique for populating the fields of KDE tables. As described in About KDE Tables, many of the columns of the main tables in which flow data is stored correspond to fields of the flow records ingested from individual devices. Rather than reserving columns for fields that are not used by a given device type (router, switch, firewall, etc.), Universal Data Records varies the mapping of fields to columns depending on the device type. This flexible schema enables Kentik to rapidly extend KDE's support for storing and querying data from new and disparate sources, which allows visibility into a much wider range of data points about customer networks and infrastructure. Among the many advantages of this approach are the ability to store vendor-specific flow fields (see Device-specific Dimensions), increased capacity for Custom Dimensions, and the ability to store data from flow records with non-standard fields.

The topics below cover the columns of the main table that are not exposed as filter and/or group-by dimensions in the portal UI:

• Query Columns
• Time Columns
• Metrics Columns
• Additional Columns

Note:
- The columns in the topics below are used only when querying with SQL via the Query SQL Method of the Query API.
- For information on columns that are exposed in the portal as filter and/or group-by dimension, see Dimensions Reference.
- Some columns fit into more than one category. If you don’t find a column where you think it would be, check other categories.

Used to specify dataseries (Full or Fast; see Resolution Overview), and other query-related data.

Used to specify time ranges.

Metrics are used to specify the units in which traffic is measured, e.g. for ranking in a top-X list. The metrics listed here are not exposed as dimensions in the portal, but may be used in API calls made with the Query SQL Method.

Note: For metrics that are exposed as dimensions in the portal, see Dimensions Reference.

While the dimensions needed for almost any flow-based query are exposed in the portal UI (see Dimensions Reference), the columns listed below are available as additional dimensions that can be used in queries made with the Query SQL Method of the Query API.

Matches to user-specified tags (see About Flow Tags).

Network performance monitoring data, including retransmits, out-of-order packets, and latency (client, server, and application).

Data related to DNS lookup and HTTP, including domain name, referrer, status, etc.).

Information about the Ultimate Exit of the traffic, which is where it leaves the network (device, interface, site, etc.; see Using Ultimate Exit).

Information about the interface though which a flow enters and leaves a device; see Interface Classification).

Information about content delivery networks associated with source and destination traffic (see About CDN Attribution):

Information about source and destination hosts and IPs that have been identified as a security threat by Spamhaus (updated daily):

The following topics cover special considerations affecting the use of KDE Main Table columns:

• Aggregation Duration
• Required Select Columns
• MAC Address Columns

In queries covering a timespan of more than a few minutes, KDE performs aggregation to standardize both the interval between reported data points and the start and end times of the covered data. The duration used for this aggregation is determined internally by KDE depending on the query's specified timespan. An aggregation duration of, for example, 60 seconds results in a data point interval of one minute with the start and end of the timespan adjusted to the closest minute.

To represent the aggregation duration in the query itself, KDE supports a variable named i_duration. The i_duration value is expressed in seconds. It will be the same for all rows of all tables queried, and can be used to normalize bytes/packets to bps or pps. When a query includes a GROUP clause covering other fields (e.g. src_as), i_duration is used in an aggregate function in the SELECT clause, e.g. SELECT max(i_duration).

Note: i_duration cannot be used in a WHERE clause.

The inclusion in a KDE query of any of the main table columns described above is for the most part optional. But unless the SELECT statement in a given query includes one or more of the following columns, that query's results will be empty (no rows returned):

• in_bytes
• in_pkts
• out_bytes
• out_pkts
• both_bytes
• both_pkts
• tcp_retransmit

The following information applies to the Main Table columns src_eth_mac and dst_eth_mac, which store MAC address data collected from flow data of type sFlow, IPFIX (depending on configuration), and pcap:

• MAC address entry in the portal, psql client, or API may be any of the following formats:
  - 11.11.11.11.11.11
  - 11:11:11:11:11:11
  Note: Addresses may be entered with or without leading zeros (e.g. 1.1.1.1.1.1 or 01.01.01.01.01.01).
• MAC address display in the portal and in query results is always period-separated (e.g. 11.11.11.11.11.11).
• Only the following operators are supported in queries for use with these columns:
  =, <>, !=.

In addition to the individual table for each device, each customer’s database includes a special table named all_devices, which is a merge into one table of the data from all of that customer’s devices. The All Devices table includes all the fields of an individual device table, but also includes the following additional fields:

The additional fields of the All Devices table can be used to filter returned data by individual routers, or to further group by device using i_device_id.

Note: The All Devices fields above are exposed as filtering and group-by dimensions in the portal (see Dimensions Reference).