Error Counts by Region and Environment From Hostnames

Medium
338.

Error Counts by Region and Environment From Hostnames

Every log line records the full hostname of the machine that wrote it, always in three dot-separated parts: <node>.<region>.<environment>, for example 'api-07.ap-south-1.prod'. The node name and the region contain hyphens but never dots.

Count the lines with level 'ERROR' per region and environment. Return the columns region, environment and errors, only for combinations with at least one error, ordered by errors descending, then region, then environment.

Tables

Table: ErrorLog

ColumnTypeValues
log_idint
hostnamevarchar
levelenumERROR, WARN, INFO
logged_atdatetime

Primary key: log_id.

One row per log line shipped to the central log store. All names are lower case.

Example

The rows must come back in the order shown.

ErrorLog

log_idhostnamelevellogged_at
1api-07.ap-south-1.prodERROR2025-12-01 10:00:00
2api-03.ap-south-1.prodERROR2025-12-01 10:00:04
3worker-1.ap-south-1.stagingERROR2025-12-01 10:01:00
4api-07.eu-west-1.prodWARN2025-12-01 10:01:30
5db-02.eu-west-1.prodERROR2025-12-01 10:02:10
6web-11.us-east-1.prodINFO2025-12-01 10:02:45
7web-11.us-east-1.prodERROR2025-12-01 10:03:00
8api-07.ap-south-1.prodINFO2025-12-01 10:03:20

Output

regionenvironmenterrors
ap-south-1prod2
ap-south-1staging1
eu-west-1prod1
us-east-1prod1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Error Counts by Region and Environment From Hostnames

Every log line records the full hostname of the machine that wrote it, always in three dot-separated parts: <node>.<region>.<environment>, for example…

  • Difficulty: Medium
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #338

Problem statement

Every log line records the full hostname of the machine that wrote it, always in three dot-separated parts: <node>.<region>.<environment>, for example 'api-07.ap-south-1.prod'. The node name and the region contain hyphens but never dots.

Count the lines with level 'ERROR' per region and environment. Return the columns region, environment and errors, only for combinations with at least one error, ordered by errors descending, then region, then environment.

Tables

Table: ErrorLog

ColumnType
log_idint
hostnamevarchar
levelenum(ERROR, WARN, INFO)
logged_atdatetime

Primary key: log_id.

One row per log line shipped to the central log store. All names are lower case.

Examples

Example 1

ErrorLog

log_idhostnamelevellogged_at
1api-07.ap-south-1.prodERROR2025-12-01 10:00:00
2api-03.ap-south-1.prodERROR2025-12-01 10:00:04
3worker-1.ap-south-1.stagingERROR2025-12-01 10:01:00
4api-07.eu-west-1.prodWARN2025-12-01 10:01:30
5db-02.eu-west-1.prodERROR2025-12-01 10:02:10
6web-11.us-east-1.prodINFO2025-12-01 10:02:45
7web-11.us-east-1.prodERROR2025-12-01 10:03:00
8api-07.ap-south-1.prodINFO2025-12-01 10:03:20

Output

regionenvironmenterrors
ap-south-1prod2
ap-south-1staging1
eu-west-1prod1
us-east-1prod1

How to solve Error Counts by Region and Environment From Hostnames

The hostname packs three fields into one string, so the work is cutting it at the dots. SUBSTRING_INDEX(s, delim, n) is made for it: with a positive n it returns everything before the n-th delimiter, with a negative one everything after the n-th delimiter from the right. The environment is the last part, SUBSTRING_INDEX(hostname, '.', -1). The region is in the middle, so it takes two cuts: SUBSTRING_INDEX(hostname, '.', 2) gives 'api-07.ap-south-1', and the last part of that is 'ap-south-1'.

With both parts extracted, it is an ordinary aggregation: keep level = 'ERROR' lines, group by the two expressions and count. Filtering in WHERE before grouping is what makes combinations with no errors disappear; the alternative positions also only exist for error lines. The hyphens in the region names are harmless, because only dots are delimiters.

The alternative finds the two dot positions with LOCATE (its third argument starts the search after the first dot) and slices with SUBSTRING — more arithmetic, same result. Both read the log once; in a real log store you would extract these fields at ingestion and index them rather than parse strings at query time.

Reference solution (MySQL)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(hostname, '.', 2), '.', -1) AS region,
       SUBSTRING_INDEX(hostname, '.', -1) AS environment,
       COUNT(*) AS errors
FROM ErrorLog
WHERE level = 'ERROR'
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(hostname, '.', 2), '.', -1), SUBSTRING_INDEX(hostname, '.', -1)
ORDER BY errors DESC, region, environment

Another way

WITH parts AS (
  SELECT hostname, LOCATE('.', hostname) AS d1, LOCATE('.', hostname, LOCATE('.', hostname) + 1) AS d2
  FROM ErrorLog WHERE level = 'ERROR'
)
SELECT SUBSTRING(hostname, d1 + 1, d2 - d1 - 1) AS region, SUBSTRING(hostname, d2 + 1) AS environment, COUNT(*) AS errors
FROM parts
GROUP BY region, environment
ORDER BY errors DESC, region, environment

← Services Missing Their Uptime Target · Agent CSAT Rank Within Each Support Team →