Skip to content

Part 8: Table unions

References: - ClickHouse UNION clause

Unions aggregate records from different tables into a single output. This is useful whenever the same kind of information lives in more than one place: a list of usernames for a given host across the RDP, FTP and NTLM tables, for example.

Three rules govern every union:

  • Both halves must return the same number of columns, in the same order.
  • The column types must match, position by position. The names come from the first query.
  • ClickHouse requires you to be explicit: write UNION ALL to keep duplicates, or UNION DISTINCT to remove them. A bare UNION will error.

💡 Label each half. Add a literal column such as 'NTLM' AS Protocol to each side. Without it, once the results are combined you cannot tell which table a given row came from, and the output becomes hard to read.

ORDER BY and LIMIT at the end apply to the combined result. To limit each half separately, they go inside each query.

8a. Union across estate tables

Write a query using a union that returns a list of operating systems, patch versions and SenseOn agent versions, showing the hosts with that operating system and agent version installed. Include a column that counts the unique number of devices.

Hint - which tables Operating system and patch level are in `endpoint_os_version`. The SenseOn agent is installed software, so its version is in `endpoint_program` alongside everything else installed on the host.
Answer 
SELECT
  'Operating System' AS Source,
  name AS Item,
  concat(version, ' patch ', toString(patch)) AS Version,
  groupUniqArray(_hostname) AS Hosts,
  uniqExact(_hostname) AS Device_Count
FROM endpoint_os_version
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
GROUP BY Item, Version

UNION ALL

SELECT
  'SenseOn Agent' AS Source,
  name AS Item,
  version AS Version,
  groupUniqArray(_hostname) AS Hosts,
  uniqExact(_hostname) AS Device_Count
FROM endpoint_program
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
  AND lower(name) LIKE lower('%SenseOn%')
GROUP BY Item, Version

ORDER BY Source asc, Device_Count desc

8b. Authentication across protocols

Write a query using UNION that shows all the hosts a given username has successfully authenticated on, for both the NTLM and Kerberos tables. Order by descending number of successful logins, where the total is greater than 100.

Answer 
SELECT
  'NTLM' AS Protocol,
  auth_username AS Username,
  groupUniqArray(_hostname) AS Hosts,
  COUNT(*) AS Successes
FROM network_ntlm
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
  AND success = 1
GROUP BY Username
HAVING Successes > 100

UNION ALL

SELECT
  'Kerberos' AS Protocol,
  username AS Username,
  groupUniqArray(_hostname) AS Hosts,
  COUNT(*) AS Successes
FROM network_kerberos
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
  AND success = 1
GROUP BY Username
HAVING Successes > 100

ORDER BY Successes desc

Adjust the query so it returns failures instead. Add a column showing the dates these occurred using a time format change, and another showing the number of days on which failures occurred for that username, in descending order of total failures. Filter so it only shows usernames with 10 or fewer and 2 or more failure days.

Hint - counting days Format the timestamp down to the date, then count the distinct values of it. `uniqExact` over a date-only string gives you the number of separate days.
Answer 
SELECT
  'NTLM' AS Protocol,
  auth_username AS Username,
  groupUniqArray(formatDateTime(toDateTime(_time_observed / 1000), '%F')) AS Dates,
  uniqExact(formatDateTime(toDateTime(_time_observed / 1000), '%F')) AS Failure_Days,
  COUNT(*) AS Failures
FROM network_ntlm
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
  AND success = 0
GROUP BY Username
HAVING Failure_Days <= 10 AND Failure_Days >= 2

UNION ALL

SELECT
  'Kerberos' AS Protocol,
  username AS Username,
  groupUniqArray(formatDateTime(toDateTime(_time_observed / 1000), '%F')) AS Dates,
  uniqExact(formatDateTime(toDateTime(_time_observed / 1000), '%F')) AS Failure_Days,
  COUNT(*) AS Failures
FROM network_kerberos
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
  AND success = 0
GROUP BY Username
HAVING Failure_Days <= 10 AND Failure_Days >= 2

ORDER BY Failures desc

💡 Why bound the failure days at both ends: a single bad day is usually a forgotten password after a change, and failures every day for a fortnight is usually a stale service account or a mapped drive nobody owns. The interesting band is in between: a few separate days of failures, which is what a slow, deliberate attempt looks like.

Note the widened time bound on this last one. Counting distinct days is meaningless over a 24 hour window.

Great! Now you can move onto Part 9 of the Hunt Lab training.