ClickHouse DB Schema

Here the full list schema available for ClickHouse; Three columns are shown in the below table: - Field: field used when doing queries on the DB - Readable Name: Which information the column contains in a human readable format - Type: Value data type - Description: Description of the data

The list of protocols and alert categories can be found below the table.

Flows table description:

Field Readable Name Type Description
FLOW_ID Flow ID UInt64 Unique Identifier of the flow, each flow has a different Flow ID
IP_PROTOCOL_VERSION IP Version UInt8 IP protocol version, 0 if the protocol is IPv6, 1 if it’s IPv4
FIRST_SEEN First Seen DateTime The date (down to milliseconds) of the first time the flow was seen on the network
LAST_SEEN Last Seen DateTime The date (down to milliseconds) of the last time the flow was seen on the network
VLAN_ID VLAN ID UInt16 The VLAN on which the flow was seen
PACKETS Total Packets UInt32 The number of Packets exchanged during the communication
TOTAL_BYTES Total Bytes UInt64 The number of Bytes exchanged during the communication
SRC2DST_BYTES Source to Dest. Bytes UInt64 The number of Bytes sent from the source towards the destination during the communication
DST2SRC_BYTES Dest. to Source Bytes UInt64 The number of Bytes sent from the destination towards the source during the communication
SRC2DST_DSCP Source to Dest. DSCP UInt8 The DSCP from the source towards the destination
DST2SRC_DSCP Dest. to Source DSCP UInt8 The DSCP from the destination towards the source
PROTOCOL Layer 4 Protocol UInt8 The Layer 4 protocol used during the communication
IPV4_SRC_ADDR IPv4 Source Address UInt32 The IPv4 address of the source
IPV6_SRC_ADDR IPv6 Source Address UInt32 The IPv6 address of the source
IP_SRC_PORT Source Port UInt16 The port used by the source
IPV4_DST_ADDR IPv4 Destination Address UInt32 The IPv4 address of the destination
IPV6_DST_ADDR IPv6 Destination Address UInt32 The IPv6 address of the destination
IP_DST_PORT Destination Port UInt16 The port used by the destination
L7_PROTO Layer 7 Application Proto UInt16 The Application Protocol of the communication
L7_PROTO_MASTER Layer 7 Master Proto UInt16 The Master Protocol of the communication
L7_CATEGORY Layer 7 Category UInt16 The Category of the communication
FLOW_RISK Flow Risk UInt64 A bitmap containing the Flow Risk set during communication
INFO Flow Info String Contains extra informations regarding the communication (e.g. the URL requested)
PROFILE Profile Name String Contains the profile name
NTOPNG_INSTANCE_NAME Instance Name String ntopng instance name
INTERFACE_ID Interface ID UInt16 The Interface identifier where the flow was seen
STATUS Predominant Alert ID UInt8 The ID of the most important alert (based on Score) the flow triggered
SRC_COUNTRY_CODE Source Country Code UInt16 The country code of the Source
DST_COUNTRY_CODE Destination Country Code UInt16 The country code of the Destination
SRC_LABEL Source Host Name String The Host Name of the Source
DST_LABEL Destination Host Name String The Host Name of the Destination
SRC_MAC Source MAC Address UInt64 The MAC Address of the Source
DST_MAC Destination MAC Address UInt64 The MAC Address of the Destination
COMMUNITY_ID Community ID String The community ID of the communication
SRC_ASN Source AS UInt32 Autonomous system of the Source
DST_ASN Destination AS UInt32 Autonomous system of the Destination
PROBE_IP Probe IP UInt32 IP address of the nProbe instance that sent the flow to ntopng
OBSERVATION_POINT_ID Observation Point ID UInt16 The Observation Point ID where the flow was seen
SRC2DST_TCP_FLAGS Source TCP Flags UInt8 A bitmap containing the TCP Flags sent from the Source to the Destination
DST2SRC_TCP_FLAGS Destination TCP Flags UInt8 A bitmap containing the TCP Flags sent from the Destination to the Source
SCORE Score UInt16 The score indicator of the flow
CLIENT_NW_LATENCY_US Source Network Latency UInt32 The Source Network Latency
SERVER_NW_LATENCY_US Dest. Network Latency UInt32 The Dest. Network Latency
CLIENT_LOCATION Source Location UInt8 0 stands for Remote Host; 1 stands for Local Host; 2 stands for Multicast Host
SERVER_LOCATION Destination Location UInt8 0 stands for Remote Host; 1 stands for Local Host; 2 stands for Multicast Host
SRC_NETWORK_ID Source Network ID UInt32 The Network ID of the Source address
DST_NETWORK_ID Destination Network ID UInt32 The Network ID of the Destination address
INPUT_SNMP Input SNMP Interface UInt32 If SNMP is enabled, contains the Input Interface ID of the device where the flow was seen
OUTPUT_SNMP Output SNMP Interface UInt32 If SNMP is enabled, contains the Output Interface ID of the device where the flow was seen
SRC_HOST_POOL_ID Source Host Pool ID UInt16 The Host Pool ID of the Source
DST_HOST_POOL_ID Dest. Host Pool ID UInt16 The Host Pool ID of the Destination
SRC_PROC_NAME Source Process Name String If enabled by using nProbe Agent, contains the Source process that generate the flow
DST_PROC_NAME Destination Process Name String If enabled by using nProbe Agent, contains the Destination process of the flow
SRC_PROC_USER_NAME Source Process Username String If enabled by using nProbe Agent, contains the Username of the Source that executed the Process
DST_PROC_USER_NAME Dest. Process Username String If enabled by using nProbe Agent, contains the Username of the Destination that executed the Process
ALERTS_MAP Alerts Bitmap String A bitmap containing all the alerts triggered by the flow
SEVERITY Alert Severity String The severity of the communication
IS_CLI_ATTACKER Source Attacker UInt8 A Boolean representing if ntopng flagged the Source as an Attacker (1) or not (0)
IS_CLI_VICTIM Source Victim UInt8 A Boolean representing if ntopng flagged the Source as a Victim (1) or not (0) of an attack
IS_CLI_BLACKLISTED Blacklisted Source UInt8 1 if the Source was blacklisted, 0 otherwise
IS_SRV_ATTACKER Destination Attacker UInt8 A flags representing if ntopng flagged the Destination as an Attacker (1) or not (0)
IS_SRV_VICTIM Destination Victim UInt8 A Boolean representing if ntopng flagged the Destination as a Victim (1) or not (0) of an attack
IS_SRV_BLACKLISTED Blacklisted Destination UInt8 1 if the Destination was blacklisted, 0 otherwise
ALERT_STATUS Alert Acknowledged UInt8 1 if the alert was acknowledged, 0 otherwise
USER_LABEL User Customizable Message String A description that users can add when acknowledging alerts
USER_LABEL_TSTAMP Date of User Message DateTime The Date when the User Message was customized
ALERT_JSON Extra Protocol Info String A JSON field containing extra informations regarding the procotocol (e.g. In HTTP, contains the HTTP URL)
IS_ALERT_DELETED Alert Deleted UInt8 1 if the alert was deleted, 0 otherwise
SRC2DST_PACKETS Source to Dest. Packets UInt32 The number of packets sent from the Source towards the Destination
DST2SRC_PACKETS Dest. to Source Packets UInt32 The number of packets sent from the Destination towards the Source
ALERT_CATEGORY Alert Category UInt8 Category of the alert (e.g. Flow alert, Host alert)

The list of the Layer 7 protocols can be found here

The list of the Layer 4 protocols can be found `here https://www.iana.org/assignments/protocol-numbers/protocol-numbers.xhtml`__

The list of Alerts Category Available:

ID Alert Category
0 Interface Alert
1 Host Alert
2 Network Alert
3 SNMP Alert
4 Flow Alert
5 MAC Alert
7 User Alert
8 Active Monitoring Alert
9 System Alert
15 Other Alert

Active monitoring description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Unique identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
resolved_ip Resolved IP String IP address associated with the alert, if resolved
resolved_name Resolved Name String Hostname associated with the alert, if resolved
measurement Measurement String Type or name of the measurement that triggered the alert
measure_threshold Measure Threshold UInt32 Threshold value that triggered the alert (nullable)
measure_value Measure Value REAL Actual measured value that triggered the alert (nullable)
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended (nullable)
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert, possibly indicating its importance or priority
counter Counter UInt32 Counter value, possibly indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data or full representation of the alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified (nullable)
alert_category Alert Category UInt8 Category of the alert, added in an ALTER TABLE statement

Flow alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Unique identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
tstamp Timestamp DateTime Time when the alert was created
tstamp_end End Timestamp DateTime Time when the alert has ended
severity Severity UInt8 Severity level of the alert (See severity mapping in ntopng.alert_severities table)
score Score UInt16 Numerical score associated with the alert
counter Counter UInt32 Counter value, indicating the number of times this alert has occurred
json JSON String JSON-formatted additional data or full representation of the alert
ip_version IP Version UInt8 Version of IP protocol used (4 or 6)
cli_ip Client IP String IP address of the client
srv_ip Server IP String IP address of the server
cli_port Client Port UInt16 Port number used by the client
srv_port Server Port UInt16 Port number used by the server
vlan_id VLAN ID UInt16 VLAN identifier
is_cli_attacker Is Client Attacker UInt8 Flag indicating if the client is classified as an attacker
is_cli_victim Is Client Victim UInt8 Flag indicating if the client is classified as a victim
is_srv_attacker Is Server Attacker UInt8 Flag indicating if the server is classified as an attacker
is_srv_victim Is Server Victim UInt8 Flag indicating if the server is classified as a victim
proto Protocol UInt8 IP protocol number (mapping can be found in table ntopng.l4_protocols)
l7_proto L7 Protocol UInt16 Layer 7 protocol identifier
l7_master_proto L7 Master Protocol UInt16 Master Layer 7 protocol identifier
l7_cat L7 Category UInt16 Category of Layer 7 protocol (mapping can be found in table ntopng.l7_categories)
cli_name Client Name String Name or hostname of the client
srv_name Server Name String Name or hostname of the server
cli_country Client Country String Country of the client
srv_country Server Country String Country of the server
cli_blacklisted Client Blacklisted UInt8 Flag indicating if the client is blacklisted
srv_blacklisted Server Blacklisted UInt8 Flag indicating if the server is blacklisted
cli2srv_bytes Client to Server Bytes UInt8 Number of bytes transferred from client to server
srv2cli_bytes Server to Client Bytes UInt8 Number of bytes transferred from server to client
cli2srv_pkts Client to Server Pkts UInt8 Number of packets transferred from client to server
srv2cli_pkts Server to Client Pkts UInt8 Number of packets transferred from server to client
first_seen First Seen DateTime Timestamp when the flow was first observed
community_id Community ID String Community identifier for the flow
alerts_map Alerts Map String HEX bitmap of all flow statuses
flow_risk_bitmap Flow Risk Bitmap UInt64 Bitmap representing risk factors associated with the flow
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
cli_host_pool_id Client Host Pool ID UInt16 Host pool identifier for the client
srv_host_pool_id Server Host Pool ID UInt16 Host pool identifier for the server
cli_network Client Network UInt16 Network identifier for the client
srv_network Server Network UInt16 Network identifier for the server
info Info String Additional information about the flow alert
cli_location Client Location UInt8 Location identifier for the client
srv_location Server Location UInt8 Location identifier for the server
probe_ip Probe IP String IP address of the probe that detected the flow
input_snmp Input SNMP UInt32 SNMP interface index for input
output_snmp Output SNMP UInt32 SNMP interface index for output
alert_category Alert Category UInt8 Category of the alert

Host alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
ip_version IP Version UInt8 Version of IP protocol used (4 or 6)
ip IP Address String IP address of the alerted host
vlan_id VLAN ID UInt16 VLAN identifier
name Host Name String Name or hostname of the host
is_attacker Is Attacker UInt8 Flag indicating if the host is classified as an attacker
is_victim Is Victim UInt8 Flag indicating if the host is classified as a victim
is_client Is Client UInt8 Flag indicating if the host is acting as a client
is_server Is Server UInt8 Flag indicating if the host is acting as a server
tstamp Timestamp DateTime Time when the alert was created
tstamp_end End Timestamp DateTime Time when the alert has ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, possibly indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data or full representation of the alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
host_pool_id Host Pool ID UInt16 Identifier for the pool of hosts this host belongs to
network Network ID UInt16 Identifier for the network this host belongs to
country Country String Country associated with the host’s IP address
alert_category Alert Category UInt8 Category of the alert

Mac address alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
address MAC Address String MAC address of the device
device_type Device Type UInt8 Type of the device (nullable)
name Device Name String Name or hostname of the device
is_attacker Is Attacker UInt8 Flag indicating if the device is classified as an attacker
is_victim Is Victim UInt8 Flag indicating if the device is classified as a victim
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, possibly indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data or full representation of the alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
alert_category Alert Category UInt8 Category of the alert

SNMP alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
ip IP Address String IP address of the SNMP device
port Port UInt32 Port number of the SNMP device
name Device Name String Name or hostname of the SNMP device
port_name Port Name String Name of the port on the SNMP device
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data for this alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
alert_category Alert Category UInt8 Category of the alert

Network alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
local_network_id Local Network ID UInt16 Identifier for the local network associated with the alert
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
name Network Name String Name of the network
alias Network Alias String Alias or alternative name for the network
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, possibly indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data or full representation of the alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
alert_category Alert Category UInt8 Category of the alert

Interface alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
ifid Interface ID UInt8 Identifier for the network interface
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Additional identifier of the network interface associated with the alert (nullable)
subtype Alert Subtype String Subtype of alert_id
name Interface Name String Name of the network interface
alias Interface Alias String Alias or alternative name for the interface
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, possibly indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data or full representation of the alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
alert_category Alert Category UInt8 Category of the alert

User alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
user User String Username or identifier of the user associated with the alert
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, possibly indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data or full representation of the alert
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
alert_category Alert Category UInt8 Category of the alert

System alerts description:

Field Readable Name Type Description
rowid Row ID UUID Unique identifier for each row in the table
alert_id Alert ID UInt32 Identifier for each alert
alert_status Alert Status UInt8 Current status of the alert
interface_id Interface ID UInt16 Identifier of the network interface associated with the alert (nullable)
name System Name String Name or identifier of the system associated with the alert
tstamp Timestamp DateTime Time when the alert was created or detected
tstamp_end End Timestamp DateTime Time when the alert was resolved or ended
severity Severity UInt8 Severity level of the alert
score Score UInt16 Numerical score associated with the alert
granularity Granularity UInt8 Frequency of alert check execution
counter Counter UInt32 Counter value, indicating the number of times this alert has occurred
description Description String Textual description of the alert
json JSON String JSON-formatted additional data
user_label User Label String Custom label assigned by a user when silencing the alert
user_label_tstamp User Label Timestamp DateTime Timestamp when the user label was last modified
alert_category Alert Category UInt8 Category of the alert

Vulnerability scan data:

Field Readable Name Type Description
HOST Host String The hostname or identifier of the scanned host
SCAN_TYPE Scan Type String The type or method of vulnerability scan performed
LAST_SCAN Last Scan Time DateTime The timestamp of when the last scan was performed
JSON_INFO JSON Information String Additional information about the scan in JSON format
VS_RESULT_FILE Result File Path String The file path or identifier for the full vulnerability scan results