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 |