CH logo® Knowledge Base
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:

Note: Some columns fit into more than one category. If you don’t find a column where you think it would be, check other categories.

 

 
 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_end_time timestamp with time zone Deprecated. To determine time ranges, use i_start_time and ctimestamp instead.
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).

 

 
 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
both_bytes bigint Sum of in_bytes and out_bytes.
both_pkts bigint Sum of in_pkts and out_pkts.
in_bytes bigint Number of bytes (not bits!) received on source/ingress interface for flow.
in_pkts bigint Number of packets received on source/ingress interface for flow.
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).
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).
out_pkts bigint Number of packets sent through the egress interface for this flow (typically 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).
sample_rate integer The rate at which traffic was sampled when flow was collected.
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).

 

 
 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
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.
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.
inet_dst_addr text The destination IP address, either IPv4 or IPv6, of the flow.
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.
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.
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.
protocol integer The number of the protocol. See https://en.wikipedia.org/wiki/List_of_IP_protocol_numbers
tcp_flags integer TCP flags that were set on the flow using a flow mask (TCP Flag Filtering).
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.

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
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_device_site_country text A country in which your organization has sites; enables the grouping, with a single dimension, of traffic from all sites in a given country.
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_input_interface_speed bigint The speed of the device interface through which flow ingressed.
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_l4_dst_port_name text Same as i_l4_src_port_name, but for destination.
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_output_interface_description text The vendor-defined name (e.g. “GigabitEthernet0/1”) of the device interface (physical or logical) through which flow egressed.
i_output_interface_speed bigint The speed of the device interface through which flow egressed.
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.
input_port integer ID of the receiving host or router interface.
l4_dst_port integer Layer 4 destination port (egg 80, 443).
l4_src_port integer Layer 4 source port (e.g. 80, 443).
output_port integer ID of the sending host or router interface.

 

 
 top  |  section

Geo Columns

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

Column Type Description
dst_geo text Two-letter country code associated with the destination 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.
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 text Two-letter country code associated with the source 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.
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.

Note: The filtering of geo fields using a LIKE operator 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
dst_as bigint The origin ASN associated with the destination IP of the flow.
dst_bgp_aspath text The BGP ASPATH for the flow’s destination IP.
dst_bgp_community text The set of BGP communities associated with the flow’s destination IP.
dst_nexthop_as integer The ASN in the first position of the AS_PATH for the destination IP of the flow.
dst_route_length integer The BGP prefix length for the destination IP of the flow.
dst_second_asn integer The ASN in the second position of the ASPATH for the destination 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_dst_as_name text AS Name of dst_as.
i_dst_nexthop_as_name text AS Name of dst_nexthop_as.
i_dst_second_asn_name text AS Name of dst_second_asn.
i_dst_third_asn_name text AS Name of dst_third_asn.
i_src_as_name text AS Name of src_as.
i_src_nexthop_as_name text AS Name of src_nexthop_as.
i_src_second_asn_name text AS Name of src_second_asn.
i_src_third_asn_name text AS Name of src_third_asn.
inet_dst_next_hop text The BGP next-hop IP address, either IPv4 or IPv6, for the destination 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.
inet_src_next_hop text The BGP next-hop IP address, either IPv4 or IPv6, for the source IP of the flow.
inet_src_route_prefix text The BGP table prefix, either IPv4 or IPv6, that contains the source IP of the flow.
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.
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.
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_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.
src_as bigint The origin ASN associated with the source IP of the flow.
src_bgp_aspath text The BGP ASPATH for the flow’s source IP.
src_bgp_community text The set of BGP communities associated with the flow’s source IP.
src_nexthop_as integer The ASN in the first position of the AS_PATH for the source IP of the flow.
src_route_length integer The BGP prefix length for the source IP of the flow.
src_second_asn integer The ASN in the second position of the ASPATH for the source IP of the flow.
src_third_asn integer The ASN in the third position of the ASPATH for the source IP of the flow.

 

 
 top  |  section

LAN Columns

Source and destination local area network IDs and MAC addresses.

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

 

 
 top  |  section

Tags Columns

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

Column Type Description
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.
src_flow_tags text Tags that match on source flow.

 

 
 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_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.
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.

 

 
 top  |  section

NPM Columns

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

Column Type Description
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.
client_nw_latency_ms bigint One-way network latency as measured from the client perspective.
connection_id bigint TCP connection ID.
fpex_latency_ms bigint First Payload Exchange Latency, which measures application response time, particularly when the protocol isn’t understood or can’t be decoded (e.g. HTTPS, SQL, etc.). The time, which excludes TCP setup, starts with the first packet sent (typically the request) and stops with the first packet returned (typically the response).
fragments bigint Packets that have been split into smaller packets for delivery across the network.
ooorder_in_pkts bigint Packets that arrived out of sequence.
ooorder_out_pkts bigint Packets that were sent out of sequence.
receive_window bigint Size of TCP receive window.
repeated_retransmits bigint Number of times a given packet was retransmitted 3 or more times.
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.
server_nw_latency_ms bigint One-way network latency as measured from the server perspective.
zero_windows bigint Count of TCP receive windows with value of zero (indicating full buffer).

 

 
 top  |  section

DNS/WWW Columns

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

Column Type Description
i_kflow_dns_query_type_name text The name of the resource record type requested by a DNS query.
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).

 

 
 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
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_interface_description text The SNMP description (portal name) of the interface through which the flow leaves.
i_ult_exit_site text The name of the site through which the flow leaves.
i_ult_exit_site_country text The name of the country containing the site through which flow leaves.
i_ult_exit_site_id bigint The ID of the site (see About Sites) through which the flow leaves.
i_ult_exit_snmp_alias text The SNMP alias (portal description) of the interface through which the flow leaves.
ult_exit_device_id bigint ID of device through which the flow leaves.
ult_exit_port bigint Number of port 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
dst_connect_type bigint A numeric code representing a connectivity type value (destination interface).
dst_network_bndry bigint A numeric code representing a network boundary value (destination interface).
i_dst_connect_type_name text The connectivity type, such as transit, IX peering, etc., of the destination interface of this flow.
i_dst_network_bndry_name text The network boundary value of the destination interface of this flow.
i_dst_provider_classification text A string representing the provider via which traffic over a given interface reaches the Internet.
i_host_direction text If flow record is from host, indicates whether the direction of traffic is into or out of that host (see Network Classification Dimensions).
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).
i_src_network_bndry_name text The network boundary value (internal or external) of the source interface of this flow (see Network Boundary Attribute).
i_src_provider_classification text A string representing the provider via which traffic over a given interface reaches the Internet (see About Provider Classification).
i_trf_origination text Traffic origination, which indicates whether the source for a given flow is inside or outside of your network.
i_trf_termination text Traffic termination, which indicates whether the destination for a given flow record is inside or outside of the network.
i_ult_exit_connect_type_name text The connectivity type value of the interface through which traffic left the network for another AS.
i_ult_exit_network_bndry_name text The network boundary value of the interface through which traffic left the network for another AS.
i_ult_provider_classification text A string representing the ultimate exit provider (see Why Ultimate Exit).
src_connect_type bigint A numeric code representing a connectivity type value (source interface).
src_network_bndry bigint A numeric code representing a network boundary value (source interface).
ult_exit_connect_type bigint A numeric code representing a connectivity type value (ultimate exit interface).
ult_exit_network_bndry bigint A numeric code representing a network boundary value (ultimate exit interface).

 

 
 top  |  section

CDN Attribution Columns

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

Column Type Description
dst_cdn text Deprecated version of i_dst_cdn.
dst_cdn_int bigint Destination CDN (content delivery network).
i_dst_cdn text The commercial name of the CDN derived from the destination IP (inet_dst_addr) of an ingested flow.
i_src_cdn text The commercial name of the CDN derived from the source IP (inet_src_addr) of an ingested flow.
src_cdn text Deprecated version of i_src_cdn.
src_cdn_int bigint Source CDN (content delivery network).

 

 
 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
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.
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.
dst_threat_type text Destination IP type of threat (as defined by Spamhaus).
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.
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.
src_threat_type text Source IP type of threat (as defined by Spamhaus).

 

 
 top  |  section

Reserved Columns

Reserved for internal use:

Column Type Description
agg_int0 integer Reserved for internal use.
agg_int1 integer Reserved for internal use.
agg_int2 integer Reserved for internal use.
agg_int3 integer Reserved for internal use.
agg0 text Reserved for internal use.
agg1 text Reserved for internal use.
agg2 text Reserved for internal use.
agg3 text Reserved for internal use.
flow_flags bigint Reserved for internal use.
host_direction bigint Reserved for internal use.
i_count bigint Reserved for internal use.
i_debug_info text Reserved for internal use.
i_num_subquery integer Reserved for internal use.
i_request_id text A unique value, dynamically updated, that identifies a query involving the table. For internal use only.
i_sq_end bigint Reserved for internal use; not for use in queries.
i_sq_start bigint Reserved for internal use; not for use in queries.
i_trf_profile text Reserved for internal use.
kt_dst_connect_type text Reserved for internal use.
kt_dst_network_bndry text Reserved for internal use.
kt_src_connect_type text Reserved for internal use.
kt_src_network_bndry text Reserved for internal use.
service_name text OTT Service. Reserved for future use.
trf_origination bigint Reserved for internal use.
trf_termination 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.

 

In this article: