In this article:

Contents Search
   

 

KDE Tables

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

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

 

 
 top

Main Table Schema

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 schema for the main table is described in the topics shown in the following list, each of which covers a category of columns:

 

 
 top  |  section

Time Columns

Used to specify time ranges.

Column Type Description
ctimestamp bigint Unix Epoch when flow was received by Kentik Detect, 1 second resolution.
i_start_time timestamp with time zone The time that flow was received, rounded to the nearest minute for display. Searching allows granularity to the nearest second (see Time Management in Queries).
i_end_time timestamp with time zone Deprecated. To determine time ranges, use i_start_time and ctimestamp instead.

 

 
 top  |  section

Metrics Columns

Used to specify the units in which traffic is measured, e.g. for ranking in a top-X list.

Column Type Description
sample_rate integer The rate at which traffic was sampled when flow was collected.
in_bytes bigint Number of bytes (not bits!) received on source/ingress interface for flow.
out_bytes bigint Number of bytes sent through the egress interface for this flow (typically host only; routers should only record bytes on ingress interface).
in_pkts bigint Number of packets received on source/ingress interface for flow.
out_pkts bigint Number of packets sent through the egress interface for this flow (typically host only).
ooorder_in_pkts bigint Out of order packets sent through the ingress interface (host devices only).
ooorder_out_pkts bigint Out of order packets sent through the egress interface (host devices only).
both_bytes bigint Sum of in_bytes and out_bytes.
both_pkts bigint Sum of in_pkts and out_pkts.
sampledpktsize integer Packet size of flow (bytes/packet).
sampledpktsize_100 integer Packet size of flow (bytes/packets) rounded down to the nearest multiple of 100.
tcp_retransmit integer Number of packets re-transmitted (host only).
retransmitted_in_pkts bigint Retransmitted packets sent through the ingress interface (host devices only).
retransmitted_out_pkts bigint Retransmitted packets sent through the egress interface (host devices only).

 

 
 top  |  section

IP Columns

IP addresses (Ipv4 or Ipv6) of flow, as well as protocol (e.g. TCP or UDP), TCP flags, and ToS.

Column Type Description
inet_family integer The address family of the flow, either 4 (IPv4) or 6 (IPv6).
inet_src_addr text The source IP address, either IPv4 or IPv6, of the flow.
inet_dst_addr text The destination IP address, either IPv4 or IPv6, of the flow.
ipv4_src_addr text Deprecated. The source IP of the flow, which must be an IPv4 address.
Note: This column has been superseded by inet_src_addr. For backward compatibility, IPv4 addresses are collected in both columns.
ipv4_dst_addr text Deprecated. The destination IP of the flow, which must be an IPv4 address.
Note: This column has been superseded by inet_dst_addr. For backward compatibility, IPv4 addresses are collected in both columns.
protocol integer The number of the protocol. See https://en.wikipedia.org/wiki/List_of_IP_protocol_numbers
i_protocol_name text The name of the protocol followed by the corresponding protocol number in parentheses, e.g. TCP (6). In SQL, supports case-insensitive equality and IN matching.
tos integer An 8-bit value, typically made up of a six-bit Differentiated Services Code Point (DSCP) field and a two-bit Explicit Congestion Notification (ECN) field.
tcp_flags integer TCP flags that were set on the flow using a flow mask (TCP Flag Filtering).
i_tcp_flag_names text One or more comma-separated names of the TCP flags, followed by the bitmap flags value in parentheses, e.g. PSH,ACK (24).
Note: Display only, not usable as SQL WHERE criteria.

Note: As indicated above, some IP-related columns exist in two versions:

  • IPv4-only columns (names starting with “ipv4_”), which are deprecated but have been retained for backwards compatibility, are populated only with values from the IPv4 address family.
  • IPv4/IPv6 columns (names starting with “inet_”) are populated with both address types.

 

 
 top  |  section

Device & Port Columns

Information related to devices including interface names and descriptions, port IDs, etc.

Column Type Description
l4_src_port integer Layer 4 source port (e.g. 80, 443).
l4_dst_port integer Layer 4 destination port (egg 80, 443).
i_l4_src_port_name text An combination of three fields in the following format:
field1 (field2/field3).
The sources of field values are as follows:
- field1: if port < 2048, the name of the service assigned to the port; otherwise the port number.
- field2: the number of the port (same as l4_src_port).
- field3: the name of the protocol (as in i_protocol_name).
Note: Display only, not usable as SQL WHERE criteria.
i_l4_dst_port_name text Same as i_l4_src_port_name, but for destination.
input_port integer ID of the receiving host or router interface.
output_port integer ID of the sending host or router interface.
i_device_id integer See All Devices Table.
i_device_name text See All Devices Table.
i_device_site_name text See All Devices Table.
i_device_type text See All Devices Table.
i_input_snmp_alias text A user-provided description (e.g. “Connected to upstream ISP”) of the device interface (physical or logical) through which flow ingressed.
i_output_snmp_alias text A user-provided description (e.g. “Connected to upstream ISP”) of the device interface (physical or logical) through which flow egressed.
i_input_interface_description text The vendor-defined name (e.g. “GigabitEthernet0/1”) of the device interface (physical or logical) through which flow ingressed.
i_output_interface_description text The vendor-defined name (e.g. “GigabitEthernet0/1”) of the device interface (physical or logical) through which flow egressed.
i_input_interface_speed bigint The speed of the device interface through which flow ingressed.
i_output_interface_speed bigint The speed of the device interface through which flow egressed.
kt_src_connect_type text Reserved for internal use.
kt_dst_network_bndry text Reserved for internal use.
kt_dst_connect_type text Reserved for internal use.
kt_src_network_bndry text Reserved for internal use.

 

 
 top  |  section

Geo Columns

Country codes, region names, and city names for the flow’s source and destination.

Column Type Description
src_geo text Two-letter country code associated with the source IP of the flow.
dst_geo text Two-letter country code associated with the destination IP of the flow.
src_geo_region text Full-text English name of the region (state or province, e.g. “California”) associated with the source IP of the flow.
dst_geo_region text Full-text English name of the region (state or province, e.g. “California”) associated with the destination IP of the flow.
src_geo_city text Full-text English name of the city (e.g. “San Francisco”) associated with the source IP of the flow.
dst_geo_city text Full-text English name of the city (e.g. “San Francisco”) associated with the destination IP of the flow.

Note: The filtering of geo fields using a LIKE operator (whether applied in the portal UI or directly in SQL) is not supported.

 

 
 top  |  section

BGP Columns

Routing information including source and destination AS, AS path, AS names, community, prefixes, and hops.

Column Type Description
src_as bigint The origin ASN associated with the source IP of the flow.
dst_as bigint The origin ASN associated with the destination IP of the flow.
src_bgp_aspath text The BGP ASPATH for the flow’s source IP.
dst_bgp_aspath text The BGP ASPATH for the flow’s destination IP.
src_bgp_community text The set of BGP communities associated with the flow’s source IP.
dst_bgp_community text The set of BGP communities associated with the flow’s destination IP.
src_nexthop_as integer The ASN in the first position of the AS_PATH for the source IP of the flow.
dst_nexthop_as integer The ASN in the first position of the AS_PATH for the destination IP of the flow.
inet_src_next_hop text The BGP next-hop IP address, either IPv4 or IPv6, for the source IP of the flow.
inet_dst_next_hop text The BGP next-hop IP address, either IPv4 or IPv6, for the destination IP of the flow.
ipv4_src_next_hop text Deprecated. The BGP next-hop IP address, IPv4 only, for the source IP of the flow.
Note: This column has been superseded by inet_src_next_hop. For backward compatibility, IPv4 addresses are collected in both columns.
ipv4_dst_next_hop text Deprecated. The BGP next-hop IP address, IPv4 only, for the destination IP of the flow.
Note: This column has been superseded by inet_dst_next_hop. For backward compatibility, IPv4 addresses are collected in both columns.
inet_src_route_prefix text The BGP table prefix, either IPv4 or IPv6, that contains the source IP of the flow.
inet_dst_route_prefix text The BGP table prefix, either IPv4 or IPv6, that contains the destination IP of the flow.
ipv4_src_route_prefix text Deprecated. The BGP table prefix, IPv4 only, that contains the source IP of the flow.
Note: This column has been superseded by inet_src_route_prefix. For backward compatibility, IPv4 addresses are collected in both columns.
ipv4_dst_route_prefix text Deprecated. The BGP table prefix, IPv4 only, that contains the destination IP of the flow.
Note: This column has been superseded by inet_dst_route_prefix. For backward compatibility, IPv4 addresses are collected in both columns.
src_route_length integer The BGP prefix length for the source IP of the flow.
dst_route_length integer The BGP prefix length for the destination IP of the flow.
src_second_asn integer The ASN in the second position of the ASPATH for the source IP of the flow.
dst_second_asn integer The ASN in the second position of the ASPATH for the destination IP of the flow.
src_third_asn integer The ASN in the third position of the ASPATH for the source IP of the flow.
dst_third_asn integer The ASN in the third position of the ASPATH for the destination IP of the flow.
i_src_as_name text AS Name of src_as.
i_dst_as_name text AS Name of dst_as.
i_src_second_asn_name text AS Name of src_second_asn.
i_dst_second_asn_name text AS Name of dst_second_asn.
i_src_third_asn_name text AS Name of src_third_asn.
i_dst_third_asn_name text AS Name of dst_third_asn.
i_src_nexthop_as_name text AS Name of src_nexthop_as.
i_dst_nexthop_as_name text AS Name of dst_nexthop_as.

 

 
 top  |  section

LAN Columns

Source and destination local area network IDs and MAC addresses.

Column Type Description
vlan_in integer ID of receiving vlan.
vlan_out integer ID of sending vlan.
src_eth_mac text Ethernet (L2) address of source. Usage described in MAC Address Columns.
dst_eth_mac text Ethernet (L2) address of destination. Usage described in MAC Address Columns.

 

 
 top  |  section

Tags Columns

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

Column Type Description
src_flow_tags text Tags that match on source flow.
dst_flow_tags text Tags that match on destination flow.
i_device_tags text Deprecated. This column has been superseded by src_flow_tags and dst_flow_tags.

 

 
 top  |  section

Query Columns

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

Column Type Description
i_duration integer The aggregation duration, expressed in seconds, used by KDE for the most recent query involving this device (see Aggregation Duration).
i_sub_limit integer Passes a limit value that applies to all subqueries used in the current KDE query. For example, if querying for top IPs with i_sub_limit = 2, then for every main table queried by a subquery only the top two IPs are contributed to the main query results.
i_fast_dataset boolean Input only. Determines the dataseries on which a query is run:
- if not specified, dataseries selection is automatic;
- if false, use Full dataseries;
- if true, use Fast dataseries.

 

 
 top  |  section

NPM Columns

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

Column Type Description
retransmitted_in_pkts bigint Packets re-sent from source to destination.
Note: Valid only with a reliable transport protocol such as TCP.
retransmitted_out_pkts bigint Packets re-sent from source to destination.
Note: Valid only with a reliable transport protocol such as TCP.
ooorder_in_pkts bigint Packets that arrived out of sequence.
ooorder_out_pkts bigint Packets that were sent out of sequence.
fragments bigint Packets that have been split into smaller packets for delivery across the network.
client_nw_latency_ms bigint One-way network latency as measured from the client perspective.
server_nw_latency_ms bigint One-way network latency as measured from the server perspective.
appl_latency_ms bigint One-way network latency that is derived by examining request/response pairs at the application layer.
Note: Use only for application protocols that have clear request/response pairings.

 

 
 top  |  section

DNS/WWW Columns

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

Column Type Description
kflow_dns_query text A query from a DNS resolver to a DNS name server that translates a domain name into a numeric IP address.
kflow_dns_query_type bigint The resource record type requested by a DNS query.
kflow_dns_response text The response from a DNS server to a DNS query.
kflow_dns_ret_code bigint DNS return code (see https://www.iana.org/assignments/dns-parameters/dns-parameters.xhtml#dns-parameters-6).
kflow_http_host text The domain name of the server.
kflow_http_referer text The address from which a destination webpage is requested.
kflow_http_status bigint HTTP status code (see https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html).
kflow_http_ua text User agent information identifying a client.
kflow_http_url text The file name of a web resource, plus query string (if any).
i_kflow_dns_query_type_name text The name of the resource record type requested by a DNS query.

 

 
 top  |  section

Ultimate Exit Columns

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

Column Type Description
ult_exit_port bigint Number of port through which the flow leaves.
ult_exit_device_id bigint ID of device through which the flow leaves.
i_inet_ult_exit text The IP address, either IPv4 or IPv6, of the interface through which the flow leaves.
i_ult_exit_device_name text The name of the device through which the flow leaves.
i_ult_exit_snmp_alias text The SNMP alias (portal description) of the interface through which the flow leaves.
i_ult_exit_interface_description text The SNMP description (portal name) of the interface through which the flow leaves.
i_ult_exit_site_id bigint The ID of the site (see About Sites) through which the flow leaves.
i_ult_exit_site text The name of the site through which the flow leaves.

 

 
 top  |  section

Interface Classification Columns

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

Column Type Description
i_src_network_bndry_name text The network boundary value (internal or external) of the source interface of this flow (see Network Boundary Attribute).
src_network_bndry bigint A numeric code representing a network boundary value (source interface).
i_dst_network_bndry_name text The network boundary value of the destination interface of this flow.
dst_network_bndry bigint A numeric code representing a network boundary value (destination interface).
i_src_connect_type_name    text The connectivity type, such as transit, IX peering, etc., of the source interface of this flow (see Connectivity Type Attribute).
src_connect_type bigint A numeric code representing a connectivity type value (source interface).
i_dst_connect_type_name text The connectivity type, such as transit, IX peering, etc., of the destination interface of this flow.
dst_connect_type bigint A numeric code representing a connectivity type value (destination interface).
i_ult_exit_network_bndry_name text The network boundary value of the interface through which traffic left the network for another AS.
ult_exit_network_bndry bigint A numeric code representing a network boundary value (ultimate exit interface).
i_ult_exit_connect_type_name text The connectivity type value of the interface through which traffic left the network for another AS.
ult_exit_connect_type bigint A numeric code representing a connectivity type value (ultimate exit interface).

 

 
 top  |  section

Threat Feed Columns

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

Column Type Description
src_threat_host text Traffic from IPs that are identified as having an infection. The value in the field is the name of the infection with which the host was initially identified.
dst_threat_host text Traffic to IPs that are identified as having an infection. The value in the field is the name of the infection with which the host was initially identified.
src_threat_bnetcc text Traffic from IPs that are identified as command and control servers. The value in the field is the name of the botnet.
dst_threat_bnetcc text Traffic to IPs that are identified as command and control servers. The value in the field is the name of the botnet.

 

 
 top  |  section

Reserved Columns

Reserved for internal use:

Column Type Description
i_sq_start bigint Reserved for internal use; not for use in queries.
i_sq_end bigint Reserved for internal use; not for use in queries.
i_request_id text A unique value, dynamically updated, that identifies a query involving the table. For internal use only.
i_num_subquery integer Reserved for internal use.
i_count bigint Reserved for internal use.

 

 
 top

Using Main Tables

Considerations affecting the use of KDE Main Tables are discussed in the following topics:

 

 
 top  |  section

Aggregation Duration

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.

 

 
 top  |  section

Required Select Columns

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

 

 
 top  |  section

MAC Address Columns

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:
    =, <>, !=.

 

 
 top

All Devices Table

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 as shown in the schema above, but also includes the following additional fields:

Column Type Description
i_device_id text Kentik-assigned unique numerical ID of the device.
Used for all functions.
i_device_name text User-defined name for the device.
i_device_site_name text Name of the site to which the device has been assigned (see About Sites). If the device hasn’t been assigned to a site, returns an empty string.
Notes:
- Supported operators for WHERE clause: case-insensitive equality, LIKE, IN, and regex matching.
- Site assignments in the table may lag Admin settings by up to 10 minutes.
i_device_type text Router or host (e.g. kprobe), as specified by the user when creating the device.
Note: Used only for selection (filtering with WHERE clause), not for display or GROUP_BY.

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.