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 ALLto keep duplicates, orUNION DISTINCTto remove them. AUNIONwithout 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.