Log Explorer SQL Mode

SQL mode lets you query stored logs and alerts with your own SQL statement (SQL is the standard language for asking a database questions). Use it when filters are not enough: to count, group, compare fields or look at how a field is stored. Queries are read-only and only ever see your own tenant's records.

Before you start

• SQL mode is part of Log Explorer, so it needs the loganalyzer.read permission. All three built-in roles have it.

• The language is ClickHouse SQL, because UTMStack v12 keeps its logs and alerts in a ClickHouse database. Standard SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT works as you would expect.

• In SQL mode, the time range picker, filters, chart view and saved searches do not apply. Put the time window in your WHERE clause.

Turn on SQL mode

1. Open Log Explorer.

2. Click SQL in the query bar. The search box becomes a SQL editor with the example SELECT * FROM logs ORDER BY `@timestamp` DESC.

3. Type your statement and press Enter or click Run.

You can also type a statement that starts with SELECT or WITH and contains FROM straight into the normal search box: Log Explorer switches to SQL mode and runs it. Click SQL again to go back to the normal search.

Editor keys

As you type, the editor suggests SQL words (SELECT, WHERE, GROUP BY, COUNT and others), the two table names and the field names of the dataset selected on the left.

What a statement can read

A statement can read two names, and nothing else:

UTMStack adds the tenant condition for you. Naming any other table, another database or a table function is refused.

Columns of logs

Columns of alerts

Each alert is returned once, in its latest version.

Fields inside origin, target, log and other objects

origin, target, log, compliance and the alert objects (adversary, lastEvent, …) are JSON columns: they hold whatever fields the pipeline wrote. Reach a field inside one with a dot, for example log.eventCode or origin.geolocation.country.

The same field can be stored as text in one record and as a number in another. To avoid type errors, wrap these fields in toString() whenever you compare, group or sort by them:

SELECT toString(log.eventCode) AS event_code, count() AS event_count
FROM logs
WHERE dataType = 'wineventlog'
  AND toString(log.eventCode) = '4625'
  AND `@timestamp` > now() - INTERVAL 1 DAY
GROUP BY event_code

Other rules:

• A column whose name starts with @ needs backticks: `@timestamp`.

• Text values go in single quotes: 'wineventlog'.

• To check whether a field inside an object exists, use IS NOT NULL, for example origin.ip IS NOT NULL.

• To see how a field is stored, use dynamicType() (example below).

Rules a statement must follow

UTMStack checks every statement before it runs:

• It must start with SELECT or WITH.

• Only one statement. A single ; at the end is allowed.

• No comments (-- or /*).

• It must not contain certain words, anywhere in the text, even inside a field name, an alias or a quoted value. Among them: create, drop, insert, alter, rename, truncate, attach, detach, optimize, grant, revoke, kill, set followed by a space, system, default., url, file, input, merge, remote, cluster, statistics, dictionary, executable, s3, mysql, postgresql, mongodb, jdbc, odbc, hdfs and information_schema.

A refused statement shows a message such as invalid SQL: "url" is not allowed here: the query may read the logs and alerts datasets and nothing else. A statement that passes these checks but has a mistake in it shows the database's own error message instead.

Limits

To work with more rows, aggregate (GROUP BY), narrow the WHERE clause, or export.

Export a SQL result: click Export CSV. Log Explorer runs the statement again and saves every column it returned, in a file named query-<time>.csv.

Make statements fast

The logs table is sorted by tenant, data type, source and time. Statements that name a dataType and a time window read far less data:

SELECT count() AS event_count
FROM logs
WHERE dataType = 'linux'
  AND `@timestamp` > now() - INTERVAL 1 HOUR

Always add a time condition on `@timestamp` and a LIMIT when you list rows.

Examples

All examples were checked against the rules above.

Latest events of one data type

SELECT `@timestamp`, dataSource, action, actionResult, toString(origin.ip) AS source_ip
FROM logs
WHERE dataType = 'linux'
  AND `@timestamp` > now() - INTERVAL 1 HOUR
ORDER BY `@timestamp` DESC
LIMIT 50

Which sources sent data in the last day

SELECT dataType, dataSource, count() AS event_count, max(`@timestamp`) AS last_seen
FROM logs
WHERE `@timestamp` > now() - INTERVAL 1 DAY
GROUP BY dataType, dataSource
ORDER BY event_count DESC
LIMIT 50

Find text in the original log line

positionCaseInsensitive() finds text anywhere in raw, ignoring letter case. It is what the normal search box does.

SELECT `@timestamp`, dataSource, raw
FROM logs
WHERE dataType = 'linux'
  AND positionCaseInsensitive(raw, 'sudo') > 0
  AND `@timestamp` > now() - INTERVAL 1 DAY
ORDER BY `@timestamp` DESC
LIMIT 20

Failed Windows logons per account

Windows event 4625 is a failed logon. The Windows pipeline writes the event id to log.eventCode and the account to target.user.

SELECT toString(target.user) AS account, count() AS failed_logons
FROM logs
WHERE dataType = 'wineventlog'
  AND toString(log.eventCode) = '4625'
  AND `@timestamp` > now() - INTERVAL 24 HOUR
GROUP BY account
ORDER BY failed_logons DESC
LIMIT 20

Accounts that failed to log on from more than 3 addresses

SELECT account, uniqExact(source_ip) AS source_ips, count() AS failed_logons
FROM (
  SELECT toString(target.user) AS account, toString(origin.ip) AS source_ip
  FROM logs
  WHERE dataType = 'wineventlog'
    AND toString(log.eventCode) = '4625'
    AND `@timestamp` > now() - INTERVAL 1 DAY
)
GROUP BY account
HAVING source_ips > 3
ORDER BY source_ips DESC

Blocked pfSense traffic by source address

SELECT toString(origin.ip) AS source_ip, count() AS blocked
FROM logs
WHERE dataType = 'firewall-pfsense'
  AND action = 'block'
  AND `@timestamp` > now() - INTERVAL 7 DAY
GROUP BY source_ip
ORDER BY blocked DESC
LIMIT 10

How a field is stored

Useful when a rule or a filter on a number does not match. None means the field is missing in those records.

SELECT dynamicType(log.eventCode) AS stored_type, count() AS records
FROM logs
WHERE dataType = 'wineventlog'
  AND `@timestamp` > now() - INTERVAL 1 DAY
GROUP BY stored_type

Alerts per rule in the last 7 days

parentId = '' keeps top-level alerts only and leaves out the repeats grouped under them.

SELECT name, severity, count() AS alert_count
FROM alerts
WHERE parentId = ''
  AND `@timestamp` > now() - INTERVAL 7 DAY
GROUP BY name, severity
ORDER BY alert_count DESC
LIMIT 20

Alerts still waiting for work

SELECT status, severity, count() AS alert_count
FROM alerts
WHERE parentId = ''
  AND status IN ('Open', 'In review')
GROUP BY status, severity
ORDER BY alert_count DESC

Most frequent adversary addresses in high-severity alerts

SELECT toString(adversary.ip) AS adversary_ip, count() AS alert_count
FROM alerts
WHERE severity = 'high'
  AND adversary.ip IS NOT NULL
  AND `@timestamp` > now() - INTERVAL 30 DAY
GROUP BY adversary_ip
ORDER BY alert_count DESC
LIMIT 10

SOC-AI uses the same SQL

The SOC-AI assistant can run SQL on your behalf with its own search tool. It follows the same rules and limits, and it only reads your tenant's logs and alerts. See SOC-AI Assistant.