SQL to CQL: Joins and Correlations

SQL uses JOIN operations to combine data from multiple tables. LogScale provides similar functionality through join() and lookup() functions, with a focus on enrichment and time-series correlation.

There are some key differences in the approach between SQL and CQL:

> LogScale Joins

  • Often implemented using lookup tables or saved queries

  • More focused on enrichment than traditional relational joins

  • Better suited for time-series correlation than strict equality joins

> Join Performance

  • LogScale optimizes for searching through large volumes of log data

  • Joins should be used judiciously as they can impact performance

  • Consider using lookup tables for static reference data

Correlation vs. Joins

  • LogScale excels at temporal correlation (events happening within time windows)

  • Use time-based parameters to correlate events across different data sources

  • Pattern matching across logs often replaces traditional join operations

SQL CQL
Inner Join
sql
SELECT a.hostname, a.alert_name, e.os, e.ip_address 
FROM alerts a 
INNER JOIN endpoints e ON a.hostname = e.hostname 
WHERE a.severity = 'high';

Using saved query approach:

logscale
source="alerts" severity="high" 
| join(
    field=hostname, 
    query={source="endpoints" },
    include=[os, ip_address]
  )
Left Join
sql
SELECT u.username, u.department, COUNT(l.id) as login_count 
FROM users u 
LEFT JOIN logins l ON u.username = l.username 
GROUP BY u.username, u.department;

Using lookup tables:

logscale
source="users" 
| match(
    file="logins.csv", 
    field="username", 
    column="username"
  ) 
| eval(login_count = size(logins) or 0) 
| table([username, department, login_count])
Subquery as Join
sql
SELECT p.process_name, p.pid, 
  (SELECT COUNT(*) FROM network_connections n WHERE n.pid = p.pid) as connection_count 
FROM processes p;

Using correlation:

logscale
source="processes" 
| select([process_name, pid]) 
| join(
    field=pid, 
    query={source="network_connections" }
    
| groupby(pid, function=count())}, 
  )
Temporal Join

Not easily expressed in standard SQL, would require window functions:

sql
SELECT a.alert_id, a.timestamp, a.hostname, 
  (SELECT action FROM user_actions u 
   WHERE u.hostname = a.hostname 
   AND u.timestamp BETWEEN a.timestamp - INTERVAL '5 minutes' AND a.timestamp) as prior_action
FROM alerts a;

Using time correlation:

logscale
source="alerts" 
| select([alert_id, timestamp, hostname]) 
| join(
    field=hostname, 
    with={source="user_actions" 
| select([hostname, timestamp as action_time, action])}, 
    include=[action, action_time]
  ) 
| where(action_time >= timestamp-5m AND action_time <= timestamp) 
| select([alert_id, timestamp, hostname, action])