CH logo® Knowledge Base
Contents Search
   

 

SQL Alert Settings

Note: While SQL-based alerting remains available for customers with existing SQL-based alerts, users are encouraged to create all new alerts with Policy-based alerting.

The following topics explain how the portal is used to configure, edit, and control alerts from Kentik Detect:

Notes:
- For an in depth look at the concepts behind SQL Alerts, including the SQL Alert Terminology used in this article, see SQL Alerts.
- For general information about alerting in Kentik Detect, see Alerts Overview.
- For information about policy-based alerting, see Policy Alert Overview.

 

 
 top

Global SQL Alert Settings

Global settings apply to all SQL alerts for a given customer. In the portal, these settings are set on a Global SQL Alert Settings page. To reach that page, go to the SQL Alerts page (Alerts » SQL Alerts Admin), then click Global SQL Alert Settings at the upper right.

Note: The Alerts heading on the main portal navbar only opens a menu (which includes the SQL Alerts Admin option) if your organization has existing SQL alerts. If not, Alerts takes you directly to the portal’s Alerting page, where you can add and manage alert policies (see Policy Alert Overview).

The Global Alert Settings page is used for the following customer-wide alert settings:

Field Description
General Settings
Enable Alerts Master on/off for all alerts.
SQL Poll Frequency The time interval (30, 60, or 120 seconds) at which the queries for the alerts will be run. Default is one minute.
Email Settings
Enable email notifications On/off for alert notification by email. Email notifications include both status change notifications and periodic status updates.
Send updates every The interval at which status update emails will be sent if one or more update is in alarm state.
Email addresses Comma-delimited list of the email addresses of alert subscribers.
URL Settings
Enable URL notifications On/off for alert notification by URL. URL notifications are generated for status change only (no periodic status updates).
Notification URL URL for alert notifications. The alert information in the notification will be sent as JSON in the message body of an HTTP POST to the specified URL.
Syslog Settings
Enable syslog notifications On/off for alert notification via syslog. Syslog notifications are generated for status change only (no periodic status updates).
Syslog receiver IP IP address to which syslog alert notification should be sent.
Syslog port The port number for syslog on the receiving server (default is 514).

Notes:
- Periodic status updates are by email only.
- In most cases the lookback window configured for each SQL alert should match the global setting for SQL Poll Frequency.
- Notification emails are sent from the email address no-reply@kd.kentik.io.

 

 
 top

Add or Edit SQL Alerts

Alerts are created and edited via theSQL Alerts page of the Kentik Detect portal (Alerts » SQL Alert Admin). The add/edit process is covered in the following sections:

Note: An alternative to adding a new SQL alert is to modify one of the ready-made alerts provided by Kentik, which are placed in each customer’s SQL Alert List. The ready-made SQL alerts are listed and described in SQL Alert Presets.

 

 
 top  |  section

Adding a SQL Alert

To add a new SQL alert:

  • Open the Add SQL Alert page by clicking the Add SQL Alert button on the SQL Alerts page in the portal.
  • Name the alert in the Alert Name field.
  • Specify the values of the other alert fields (see SQL Alert Field Definitions).
  • Click the Enabled button to make the alert currently active, meaning that incoming flow is being evaluated to see if it meets the configured conditions for triggering the alert.
  • Click the Save button to save the new SQL alert. Otherwise click Cancel.

 

 
 top  |  section

Editing a SQL Alert

To edit an existing SQL alert:

  • Open the Edit SQL Alert page for a given alert by clicking the alert’s name in the SQL Alert List.
  • Edit the alert fields that you’d like to change (see SQL Alert Field Definitions).
  • If you want the edited alert to be enabled, click the Enabled checkbox.
  • To save changes, click the Save button. Otherwise click Cancel.

 

 
 top  |  section

SQL Alert Field Definitions

Adding or editing a SQL alert requires specifying a name in the Alert Name field as well as the additional alert settings in the following table:

Field Description
General Settings
Enabled A checkbox that allows the alert to be disabled (uncheck) without being deleted.
Alert name A string of 5 to 64 characters: alphanumeric, underscore or period (no spaces).
Alert description Optional user-defined descriptive text about the alert.
Criticality Four levels: Info, Minor, Major, Critical.
Once triggered, a Major or Critical alert will not automatically return to clear state; it must first be acknowledged by a notification recipient.
Trigger Settings
SQL The SQL query that defines the conditions that the alerting engine will recognize as a match (see SQL Alert Query).
Event window The time range (10 minutes to 24 hours) over which to count matches.
Lookback window The timespan (back in time from “now”) over which flow is averaged each time an alert query is run. May be 30, 60 (default), or 120 seconds.
Note: Typically set to match the global SQL poll frequency.
Trigger threshold The number of matches that must occur within the event window put an alert into alarm state (which triggers a notification).
Reset period The period that must pass without a new match before an alarm can be cleared.
Supplemental Settings
Supplemental query 1 description Optional user-defined descriptive text about the supplemental query.
Supplemental query 1 A query run at the polling frequency whenever an alert is in alarm state. Used to provide additional information for inclusion in notifications.
Supplemental query 2 description Optional user-defined descriptive text about the supplemental query.
Supplemental query 2 A query run at the polling frequency whenever an alert is in alarm state. Used to provide additional information for inclusion in notifications.
Supplemental query lookback The lookback window for supplemental queries 1 and 2.

 

 
 top

SQL Alerts Page

The SQL Alert List on the SQL Alerts page (Alerts » SQL Alert Admin) is a table in which each row represents one of the SQL alerts that is currently set in the system for your organization. Details of the SQL Alerts page are covered in the following topics:

Note: Kentik provides a set of ready-made SQL alerts (see SQL Alert Presets) that will appear in the SQL Alert List when you first register and sign on. Before these SQL alerts are enabled they must be configured for use in your specific situation. For assistance with SQL alert templates, contact support@kentik.com.

 

 
 top  |  section

SQL Alerts Page UI

The SQL Alerts page has the following main elements:

  • Global SQL Alert Settings: A link at upper right that takes you to the Global SQL Alert Settings page.
  • SQL Alerts Incidents: A button that takes you to the SQL Alerts Incidents page (see SQL Alerts Incidents).
  • Add SQL Alert: A button that takes you to the Add SQL Alert page (see Adding a SQL Alert).
  • Filter field: A field at right that filters the alert list. The Alert name, Criticality, and Alert email columns of the alert list are searched for a match on the string entered in this field.
  • SQL Alert List: see SQL Alert List.
  • Page navigation: A control that is shown only when the number of rows in the list is greater than 50. The control determines the display of the alert list, and includes:
    - first page and last page icon buttons;
    - previous page and next page icon buttons;
    - current page indicator (e.g. “3 of 5”).
    - an All button that hides the page navigation control and displays all rows in the list.

 

 
 top  |  section

SQL Alert List

The SQL Alert List is a table whose rows each represent one SQL alert. Each row is made up of the following columns:

  • Enabled: indicates if the alert is currently enabled.
    - A checkmark in a circle indicates enabled; an empty cell indicates not enabled.
    - Alerts are enabled/disabled with the Enable/Disable button (see SQL Alert Actions) or the Enabled checkbox at the top of the alert’s configuration page.
  • Name: The name given to the alert when it is added or edited.
    - Hovering the cursor over the displayed portion of the name will pop up a tool tip with the full SQL query represented by the alert.
    - Clicking on the name will take you to the alert’s configuration page.
  • Criticality: The criticality (info, minor, major, or critical) assigned to the alert when it was added or edited.
  • Trigger: The trigger threshold and event window that was specified for the alert when it was added or edited.
  • Supplemental: The number of supplemental queries specified for the alert when it was added or edited.
  • ID: The alert’s Kentik-assigned unique ID.
  • Action buttons: Buttons for actions that can be taken on the alert (see SQL Alert Actions).

Note: For further information on the SQL alert settings mentioned above, see SQL Alert Field Definitions.

 

 
 top  |  section

SQL Alert Actions

Each row of the SQL Alert List includes a set of buttons enabling action on the corresponding alert:

  • Enable/Disable: Toggles the alert between two states:
    - Enabled: the alert is currently active, meaning that incoming flow is being evaluated to see if it meets the configured conditions for triggering the alert.
    - Disabled: the alert is not currently active.
  • Edit alert: Takes you to the Edit Alert page for the alert (see Editing a SQL Alert).
  • Test query: Takes you to the portal’s Query Editor and populates that page’s SQL field with the alert’s SQL statement so that you can experiment with and refine the query on which the alert is based.
  • Alert history: Takes you to the SQL Alerts Log to see the history of the alert.
  • Copy Alert: Duplicates the alert by opening it in the Edit SQL Alert page (see Editing a SQL Alert) where it can be modified and saved, at which point it will be added to the SQL Alert List.
  • Delete: Opens a confirming dialog that allows you Deletes the alert and removes it from the alert list.

 

 
 top

SQL Alerts Incidents

The SQL Alerts Incidents page (Alerts » SQL Alerts Incidents) contains a pending alerts table listing all of the alerts that are currently in either alarm state or acknowledgement-required state. The table is updated every 30 seconds. To access the dashboard, click Alert on the main portal menu bar. The dashboard is covered in the following topics:

Note: A SQL alert of any level (Info through Critical) may be cleared manually by clicking on its Clear button in the pending alerts table. But if the alert is still within its specified Reset Period then the alert will reappear in the table as soon as the active alerts are next polled (every 30 seconds).

 

 
 top  |  section

SQL Alerts Incidents UI

The SQL Alerts Incidents page has the following main UI elements:

  • Group By: a drop-down menu at upper left that specifies how the pending alerts are grouped together for display in the table. Whenever there is more than one member row of a given group, the members of the group are represented collectively by a group row. Grouping choices are:
    - Alert Query: groups the pending alerts based on their SQL statement as defined in the SQL field on the Add/Edit Alert page.
    - Key: the value of the field specified as the key in the SELECT clause of the alert query.
    - Alert state: ALARM or ACK_REQ.
    - Criticality: info, minor, major, critical.
  • SQL Alerts Admin: a link at upper right to the SQL Alerts Page.
  • SQL Alerts Log: a link at upper right to the customer-wide SQL Alerts Log.
  • Clear Selected: a text button at upper right that clears all rows whose checkbox is checked. A dialog will appear allowing a comment to be added; the comment will appear in the alert log. To cancel the clearing operation, click Cancel in the dialog.
  • Table of pending SQL Alerts: see Pending SQL Alerts Table.

 

 
 top  |  section

Pending SQL Alerts Table

The table of pending SQL Alerts includes two types of rows:

  • Member row: represents a single alert. The background color of the row will vary depending on alert state and criticality:
    - If the alert is in alarm state (ALARM) the color will be red for major or critical alerts, yellow for minor, or grey for info.
    - If the alert is in acknowledge-required state (ACK_REQ) the color will be dark blue for major or critical alerts, light blue for minor or info.
  • Group row: appears whenever the current Group By setting results in multiple members of the same group (rows in which the value of the Group By parameter is the same).
    Example: If Group By is set to Alert Query, a heading row will appear before each group of alert rows that have the same value in the Alert Name column.

Each row in the table includes the following columns, the order of which will vary depending on the Group By setting:

  • Selection checkbox: when checked, the row is selected.
  • Key: The value of the key.
  • Key Type: the name of the main table column whose value is shown in the dashboard’s Key column.
  • Alert Name: The name of the alert represented by the row.
  • Criticality: The criticality (info, minor, major, or critical) assigned to the alert when it was added or edited.
  • State: the current state (ALARM or ACK_REQ) of the alert.
  • Output 1: the name and value that is defined in the alert query as output 1 for this alert.
  • Output 2: the name and value that is defined in the alert query as output 2 for this alert.
  • Alert ID: The start time of the event that resulted in the alert entering ALARM.
  • Start: The start time of the event that resulted in the alert entering ALARM.
  • End: If the alert state is ALARM, “In alarm.” If the alert state is ACK_REQ, the end time of the event.
  • Time over threshold: a percent representing n * f / t where:
    - n = number of matches since event start
    - f = poll frequency in seconds
    - t = total time (in seconds) since event start
  • Recent Comments: Comments recently added to this alert.
  • Actions: Buttons for actions that can be taken on the alert (see SQL Alerts Incidents Actions).

 

 
 top  |  section

SQL Alerts Incidents Actions

Each row of the pending alerts table on the SQL Alerts Incidents page includes a set of buttons enabling action on the corresponding alert:

  • Comment (edit icon): click to add a comment about the alert.
  • History (history icon): Links to the SQL Alerts Log with only the logging for the event shown.
  • Details (zoom-in icon): opens a dialog providing details on the alert, including alert configuration, information on the event that put the alert into alarm state, and the alert query.
  • Graph (stats icon): opens a browser page or tab with the alert query loaded into the portal’s Data Explorer with the query results graphed.
  • Clear (clear icon): manually puts the alert into clear state. A dialog will appear allowing a comment to be added; the comment will appear in the alert log. To cancel the clearing operation, click Cancel in the dialog.

Notes:
- If an alert that’s within its specified reset period is cleared manually the alert will reappear in the table as soon as the active alerts are next polled (every 30 seconds).
- Clearing an alert does not clear its alert table of stored matches. If at the time of a subsequent match the match count is at the trigger threshold or greater, a new alarm will be triggered.

 

 
 top

SQL Alerts Log

The SQL Alerts Log contains a table showing the history of the most recent 500 SQL alert matches and events. The table is updated when opened, and not automatically updated thereafter (refresh the page manually to update). To access the dashboard, click Alert on the main portal menu bar. The dashboard is covered in the following topics:

 

 
 top  |  section

SQL Alerts Log Elements

The SQL Alerts Log page has the following main UI elements:

  • Back to SQL Alerts Incidents: a link at upper right to the SQL Alerts Incidents page.
  • Time-range filter: four controls for entering a time range:
    - Start day: a drop-down calendar to choose the start day.
    - Start time: a drop-down list of start times.
    - End day: a drop-down calendar to choose the end day.
    - End time: a drop-down list of end times.
  • Filter by: a drop-down that is used (along with Filter Value) to filter the rows of the log table. The item chosen from the drop-down determines which category the Filter Value is applied to:
    - Alert id: the unique ID for an alert that generated an event or match that is displayed in the table. The alert ID is system-assigned when the alert is created in the portal (see Adding a SQL Alert).
    - Event id: a unique ID assigned to each Event when the parent alert enters alarm mode.
    - Key: The value of a field in the Main Table (e.g. an ASN, an IP address, an interface, etc.) that an alert is set up to monitor.
    - Query id: reserved for future use.
  • Filter value: a text button at upper right that clears all rows whose checkbox is checked. A dialog will appear allowing a comment to be added; the comment will appear in the alert log. To cancel the clearing operation, click Cancel in the dialog.
  • Show events only: a checkbox that hides matches so the table displays only events.
  • Apply: a button that applies the current settings of the above controls (time-range, filter, etc.) to determine which rows are displayed in the table.
  • SQL Alerts Log table: see SQL Alerts Log Table.

Note: For definitions of terms such as key, alert, event, etc., see SQL Alert Terminology.

 

 
 top  |  section

SQL Alerts Log Table

The SQL Alerts Log is a table providing a historical look at the most recent matches and events (up to 500 total) for all of the currently enabled SQL alerts. The table has the following columns:

  • Row type: Event or Match.
  • Time of Event / Alert (UTC): if the row type is Event, the time of the first match of the event. If the row type is Match, the time of the match.
  • Event ID: a Kentik-assigned unique ID for the event.
  • Alert name (ID): the name given to the alert when it was added/edited, and the unique ID assigned to the alert by Kentik.
  • State Old: the prior state (ALARM, ACK_REQ, or CLEAR) of the alert (see SQL Alert States).
  • State New: the current state of the alert.
  • Key: the value of the field designated in the alert query as the key.
  • Out1: the name and value that is defined in the alert query as output 1 for this alert.
  • Out2: the name and value that is defined in the alert query as output 2 for this alert.
  • Query Result: the data returned from the main query, expressed as JSON.
  • Supplemental Result 1: the data returned from supplemental query 1, expressed as JSON.
  • Supplemental Result 2: the data returned from supplemental query 2, expressed as JSON.
  • Comment: a comment applied to the alert in the Alert Dashboard via the comment dialog that appears when you click either the comment icon or the clear icon for a given row.

Note: For definitions of terms such as event, match, key, supplemental query, etc., see SQL Alert Terminology.

 

 
 top

SQL Alert Presets

The following table lists the SQL alerts that are provided ready-made by Kentik, which are placed in each customer’s SQL Alerts List.

Note: Before these alerts are enabled they must be configured for use in your specific situation. For assistance with alert templates, contact support@kentik.com.

Name Description
all_dst53_or_src53_to_1ip_from_all_external Inbound DNS Reflection:
DNS, port 53 traffic. Over 10Mb/s or 10k pps to or from port 53 destined to 1 IP from external. Dst IP indicated.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE (l4_dst_port = 53 OR l4_src_port=53)
  AND src_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 10000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 10
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

all_from_1ip_internal_to_all_external Outbound Total Traffic Flood from Single IP:
Over 1 Gb/s or 300k pps from 1 IP internal to external. Src IP listed.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE i_device_type != 'host'
  AND src_flow_tags LIKE '%MYNETWORK%'
  AND dst_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 300000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 1000
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

all_from_1ip_to_China High Outbound Traffic to China:
Over 30 Mb/s or 30k pps from 1 IP internal to China. Src IP listed.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE dst_geo= '17230'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 30000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 30
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

all_from_all_internal_to_1ip_external Outbound Total Traffic Flood to Single IP:
Over 1 Gb/s or 300k pps from internal to 1 IP external. Dst IP listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE i_device_type != 'host'
  AND src_flow_tags LIKE '%MYNETWORK%'
  AND dst_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 300000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 1000
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

all_src53_or_dst53_from_1ip_internal Outbound DNS Attack:
DNS, port 53 traffic. Over 10Mb/s or 10k pps to or from port 53 sourced from 1 IP internal. Src IP indicated.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE src_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
  AND (l4_src_port = 53 OR l4_dst_port=53)
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 10000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 10
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

all_to_1ip_internal_from_all_external Inbound Total Traffic Flood to Single IP:
Over 1 Gb/s or 300k pps to 1 IP internal from external. Dst IP listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE i_device_type != 'host'
  AND src_flow_tags NOT LIKE '%MYNETWORK%'
  AND dst_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 300000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 1000
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

all_to_24_from_China High Inbound Traffic from China:
Over 10 Mb/s or 10k pps to a /24 from China. Dst /24 listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS f_cidr24_ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE src_geo= '17230'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  f_cidr24_ipv4_dst_addr$_key_ HAVING ((sum(both_pkts)/%lookbackseconds%) > 10000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 10
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

high_fps_per_dst_ip High FPS to Single IP:
fps over 100 for any 1 dst IP all routers combined. Also displays pps as additional info.

SELECT ipv4_dst_addr AS ipv4_dst_addr$_key_,
  MAX(trautocount/60) AS _out1_fps,
  MAX(f_sum_both_pkts) AS _out2_pps
FROM (
SELECT i_start_time,
  ipv4_dst_addr,
  sum(i_count) AS trautocount,
  round(sum(both_pkts)/60) AS f_sum_both_pkts
FROM all_devices
WHERE ctimestamp > %lookbackseconds%+30
  AND i_sub_limit=50
GROUP BY i_start_time,
  ipv4_dst_addr HAVING sum(i_count)/60 > 100
ORDER BY i_start_time,
  sum(i_count) DESC) a
GROUP BY ipv4_dst_addr
ORDER BY _out1_fps DESC
LIMIT 200;

high_fps_per_src_ip High FPS from Single IP:
fps over 125 for any 1 src IP, all routers combined. Also displays pps as additional info.

SELECT ipv4_src_addr AS ipv4_src_addr$_key_,
  MAX(trautocount/60) AS _out1_fps,
  MAX(f_sum_both_pkts) AS _out2_pps
FROM (
SELECT i_start_time,
  ipv4_src_addr,
  sum(i_count) AS trautocount,
  round(sum(both_pkts)/60) AS f_sum_both_pkts
FROM all_devices
WHERE ctimestamp > %lookbackseconds%+30
  AND i_sub_limit=50
  AND i_device_type != 'host'
GROUP BY i_start_time,
  ipv4_src_addr HAVING sum(i_count)/60 > 125
ORDER BY i_start_time,
  sum(i_count) DESC) a
GROUP BY ipv4_src_addr
ORDER BY _out1_fps DESC
LIMIT 200;

icmp_from_1ip_internal Outbound ICMP Flood from Single IP:
Over 10 Mb/s or 10k pps of ICMP from 1 IP internal. Src IP listed.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=1
  AND src_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 10000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 10
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

icmp_to_1ip_internal Inbound ICMP Flood:
Over 10 Mb/s or 10k pps of ICMP to 1 IP internal. Dst IP listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=1
  AND dst_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 10000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 10
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

many_dst_ips_from_1_src High Count of Unique Dest IPs from Single Source IP:
There is one src IP talking to over 1000 dst IP’s through one router over 1 minute. Typically around 700

SELECT ipv4_src_addr AS ipv4_src_addr$_key_,
  MAX(f_countdistinct_ipv4_dst_addr) AS _out1_dst_ips,
  MAX(f_sum_both_pkts) AS _out2_pps,
  i_device_name
FROM (
SELECT i_start_time,
  i_device_name,
  ipv4_src_addr,
  (max(ipv4_dst_addr))::inet - '0.0.0.0'::inet AS f_countdistinct_ipv4_dst_addr,
  round(sum(both_pkts)/60) AS f_sum_both_pkts
FROM all_devices
WHERE ctimestamp > %lookbackseconds%+30
  AND i_sub_limit=50
  AND i_device_type != 'host'
GROUP BY i_start_time,
  i_device_name,
  ipv4_src_addr HAVING (max(ipv4_dst_addr))::inet - '0.0.0.0'::inet > 800
ORDER BY i_start_time,
  (max(ipv4_dst_addr))::inet - '0.0.0.0'::inet DESC) a
GROUP BY ipv4_src_addr,
  i_device_name
ORDER BY max(f_countdistinct_ipv4_dst_addr) DESC
LIMIT 200;

many_src_ips_to_1_dst High Count of Unique Source IPs to Single Dest IP:
There are over 100 source IP’s talking to 1 dst ip through one router over 1 minute. Reports the dst_ip over threshold and pps.

SELECT ipv4_dst_addr AS ipv4_dst_addr$_key_,
  MAX(f_countdistinct_ipv4_src_addr) AS _out1_src_ips,
  MAX(f_sum_both_pkts) AS _out2_pps,
  i_device_name
FROM (
SELECT i_start_time,
  i_device_name,
  ipv4_dst_addr,
  (max(ipv4_src_addr))::inet - '0.0.0.0'::inet AS f_countdistinct_ipv4_src_addr,
  max(protocol) AS f_count_protocol,
  round(sum(both_pkts)/60) AS f_sum_both_pkts
FROM all_devices
WHERE ctimestamp > %lookbackseconds%+30
  AND i_sub_limit=50
GROUP BY i_start_time,
  i_device_name,
  ipv4_dst_addr HAVING (max(ipv4_src_addr))::inet - '0.0.0.0'::inet > 100
ORDER BY i_start_time,
  (max(ipv4_src_addr))::inet - '0.0.0.0'::inet DESC) a
GROUP BY ipv4_dst_addr,
  i_device_name
ORDER BY max(f_countdistinct_ipv4_src_addr) DESC
LIMIT 200;

syn_from_1ip_internal Outbound SYN Flood from Single IP:
Over 10Mb/s or 10k pps of flows with only SYN packets from 1 IP internal. Src IP listed.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=6
  AND tcp_flags= 2
  AND i_device_type != 'host'
  AND src_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 10000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 10
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

syn_to_1ip_from_external Inbound SYN Flood to Single IP:
Over 20Mb/s or 20k pps of flows with only SYN packets to 1 IP internal. Dst IP listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=6
  AND tcp_flags= 2
  AND i_device_type != 'host'
  AND src_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 20000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 20
ORDER by f_sum_both_pkts$_out_pps DESC
LIMIT 100;

syn_to_24_from_external Inbound SYN Flood to /24 Network:
Over 60Mb/s or 60k pps of flows with only SYN packets to one /24 from external. Dst /24 listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS f_cidr24_ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=6
  AND tcp_flags= 2
  AND i_device_type != 'host'
  AND ctimestamp > %lookbackseconds%
  AND src_flow_tags NOT LIKE '%MYNETWORK%'
GROUP by i_device_name,
  f_cidr24_ipv4_dst_addr$_key_ HAVING ((sum(both_pkts)/%lookbackseconds%) > 60000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 60
ORDER by f_sum_both_pkts$_out_pps DESC
LIMIT 100;

udp_from_1ip_internal_to_all_external Outbound UDP Flood from Single IP:
More than 600 Mb/s or 125k pps UDP from 1 IP internal to external. Src IP listed.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=17
  AND i_device_type != 'host'
  AND src_flow_tags LIKE '%MYNETWORK%'
  AND dst_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 125000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 600
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

udp_from_all_internal_to_1ip_external Outbound UDP Flood:
More than 100 Mb/s or 75k pps UDP from internal to 1 IP external. Dst IP listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=17
  AND src_flow_tags LIKE '%MYNETWORK%'
  AND dst_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 75000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 100
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

udp_srcdst0_17_19_80_123_161_1900_from_1ip_internal Outbound UDP Reflection Attack:
Over 15Mb/s or 15k pps from 1 IP internal on well known UDP attack ports. This usually indicates this internal IP is sourcing an attack. Src IP indicated.

SELECT now(),
  i_device_name,
  ipv4_src_addr AS ipv4_src_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE src_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
  AND protocol=17
  AND (l4_src_port = 0 OR l4_src_port = 19 OR l4_src_port=80 OR l4_src_port=123 OR l4_src_port=161 OR l4_src_port=17 OR l4_src_port=1900 OR l4_dst_port = 0 OR l4_dst_port = 19 OR l4_dst_port=80 OR l4_dst_port=123 OR l4_dst_port=161 OR l4_dst_port=17 OR l4_dst_port=1900)
GROUP by i_device_name,
  ipv4_src_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 15000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 15
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

udp_srcdst0_17_19_80_123_161_1900_to_1ip_from_external Inbound UDP Reflection Attack:
Over 15Mb/s or 15k pps to 1 IP internal on well known attack UDP ports. This usually indicates this internal IP is being attacked. Dst IP indicated.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE src_flow_tags NOT LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
  AND protocol=17
  AND (l4_dst_port = 0 OR l4_dst_port = 19 OR l4_dst_port=80 OR l4_dst_port=123 OR l4_dst_port=161 OR l4_dst_port=17 OR l4_dst_port=1900 OR l4_src_port = 0 OR l4_src_port = 19 OR l4_src_port=80 OR l4_src_port=123 OR l4_src_port=161 OR l4_src_port=17 OR l4_src_port=1900)
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 15000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 15
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

udp_to_1ip_internal_from_all_external Inbound UDP Traffic Flood to Single IP:
Over 600 Mb/s or 125k pps UDP to 1 IP internal from external. Dst IP listed.

SELECT now(),
  i_device_name,
  ipv4_dst_addr AS ipv4_dst_addr$_key_,
  round(sum(both_pkts)/%lookbackseconds%) AS f_sum_both_pkts$_out_pps,
  round((sum(both_bytes)/%lookbackseconds%/1000000)*8) AS f_sum_both_bytes$_out2_mbps
FROM all_devices
WHERE protocol=17
  AND i_device_type != 'host'
  AND src_flow_tags NOT LIKE '%MYNETWORK%'
  AND dst_flow_tags LIKE '%MYNETWORK%'
  AND ctimestamp > %lookbackseconds%
GROUP by i_device_name,
  ipv4_dst_addr HAVING ((sum(both_pkts)/%lookbackseconds%) > 125000) OR ((sum(both_bytes)/%lookbackseconds%/1000000)*8) > 600
ORDER by f_sum_both_bytes$_out2_mbps DESC
LIMIT 100;

 

In this article: