Invalid IPv4 Access Log Census

Hard

A platform security team audits raw access logs and needs a frequency report of malformed IPv4 strings. Table: Logs log_id: unique log event identifier ip: captured client IPv4 text status_code: HTTP response code for that request An IPv4 string is invalid if at least one of the following is true: Any octet value is greater than 255. Any octet contains leading zeros (for example: 01, 001, 000). The address does not contain exactly 4 octets. Task: Return every invalid IP string with how many times it appears in Logs. Output requirements: Return exactly these columns in this order: ip, invalid_count Sort by invalid_count descending. For ties, sort by ip descending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Logs:
| log_id | ip            | status_code |
|--------|---------------|-------------|
| 1      | 192.168.1.1   | 200         |
| 2      | 256.1.2.3     | 404         |
| 3      | 192.168.001.1 | 200         |
| 4      | 192.168.1.1   | 200         |
| 5      | 192.168.1     | 500         |
| 6      | 256.1.2.3     | 404         |

Output

[
  {"ip":"256.1.2.3","invalid_count":2},
  {"ip":"192.168.1","invalid_count":1},
  {"ip":"192.168.001.1","invalid_count":1}
]

Explanation

256.1.2.3 is invalid because 256 > 255. 192.168.001.1 is invalid due to leading zeros. 192.168.1 is invalid because it has only 3 octets.

Example 2

Input

Logs:
| log_id | ip        | status_code |
|--------|-----------|-------------|
| 10     | 300.1.1.1 | 404         |
| 11     | 300.1.1.1 | 500         |
| 12     | 01.2.3.4  | 400         |
| 13     | 01.2.3.4  | 401         |
| 14     | 1.2.3     | 404         |
| 15     | 1.2.3     | 500         |
| 16     | 8.8.8.8   | 200         |

Output

[
  {"ip":"300.1.1.1","invalid_count":2},
  {"ip":"1.2.3","invalid_count":2},
  {"ip":"01.2.3.4","invalid_count":2}
]

Explanation

All three invalid IPs appear twice, so tie-breaking is done by ip in descending lexicographic order.

Example 3

Input

Logs:
| log_id | ip              | status_code |
|--------|-----------------|-------------|
| 20     | 0.0.0.0         | 200         |
| 21     | 1.2.3.4         | 200         |
| 22     | 255.255.255.255 | 500         |
| 23     | 192.168.10.20   | 404         |

Output

[]

Explanation

All rows contain valid IPv4 addresses, so the result is empty.

Constraints

0 <= number of rows in Logs <= 250000
1 <= log_id <= 2000000000
log_id is unique
ip is a non-null text value
1 <= length(ip) <= 40
status_code is an integer
An invalid IP is one where any octet > 255, or any octet has leading zeros, or octet count is not 4
Return only invalid IP strings
Aggregate by exact string match of ip
Output columns must be exactly: ip, invalid_count
Sort by invalid_count DESC, then ip DESC
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with required columns

Logs: | log_id | ip | status_code | |--------|---------------|-------------| | 1 | 192.168.1.1 | 200 | | 2 | 256.1.2.3 | 404 | | 3 | 192.168.001.1 | 200 | | 4 | 192.168.1.1 | 200 | | 5 | 192.168.1 | 500 | | 6 | 256.1.2.3 | 404 |

Logs: | log_id | ip | status_code | |--------|-----------|-------------| | 10 | 300.1.1.1 | 404 | | 11 | 300.1.1.1 | 500 | | 12 | 01.2.3.4 | 400 | | 13 | 01.2.3.4 | 401 | | 14 | 1.2.3 | 404 | | 15 | 1.2.3 | 500 | | 16 | 8.8.8.8 | 200 |

Logs: | log_id | ip | status_code | |--------|-----------------|-------------| | 20 | 0.0.0.0 | 200 | | 21 | 1.2.3.4 | 200 | | 22 | 255.255.255.255 | 500 | | 23 | 192.168.10.20 | 404 |