Skip to content

Part 8: Table unions

References: - ClickHouse UNION clause

A union aggregates records from different tables into one output. This is useful when the same type of information is in more than one location. For example, you can get a list of usernames for a given host from the RDP, FTP and NTLM tables.

These three rules apply to each union:

  • Both halves must return the same number of columns, in the same sequence.
  • The column types must match, in each position. The column names come from the first query.
  • In ClickHouse, you must be explicit: write UNION ALL to keep duplicates, or UNION DISTINCT to remove them. A UNION without ALL or DISTINCT gives an error.

💡 Label each half. Add a literal column, for example 'NTLM' AS Protocol, to each half. Without this column, you cannot tell which table a row came from after the query combines the results. Also, the output is difficult to read.

ORDER BY and LIMIT at the end apply to the combined result. To limit each half separately, put them in each query.


8a. Union across estate tables

Use a union to write a query that returns a list of operating systems, patch versions and SenseOn agent versions. Show the hosts that have each operating system and agent version installed. Include a column that counts the number of unique devices.

Hint - which tables The operating system and the patch level are in `endpoint_os_version`. The SenseOn agent is installed software. Thus, its version is in `endpoint_program`, together with all the other software 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

Use UNION to write a query that shows all the hosts where a given username authenticated successfully. Use the NTLM and Kerberos tables. Show only the totals that are greater than 100. Sort the results by the number of successful logins, in descending order.

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

Change the query so that it returns failures, not successes. Add a column that shows the dates of the failures. Use a time format change for this column. Add another column that shows the number of days with failures for that username. Sort the results by total failures, in descending order. Filter the results to show only usernames with 10 or fewer and 2 or more failure days.

Hint - counting days Format the timestamp so that it shows only the date. Then count the distinct values. `uniqExact` on a string with only the date gives 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: Failures on only one day are usually a forgotten password after a change. Failures on each day for two weeks are usually a stale service account, or a mapped drive that has no owner. The range of interest is between these two: failures on a few separate days. This pattern is typical of a slow, deliberate attempt.

Notice the wider time bound on this last query. A count of distinct days is not useful in a 24 hour window.


Now you can continue to Part 9 of the Hunt Lab training.