Log Data Analysis: Aggregation & String Functions

The Logic of Data Aggregation: A Quick Recap

Before we add string functions to the mix, let's briefly review the logical process of a query that uses aggregation. The GROUP BY clause is the engine that drives this analysis.

Your Toolkit for Text: An Introduction to String Functions

Log entries—whether from a web server, an EDR tool, or a cloud audit trail—are filled with text: IP addresses, request URIs, user-agent strings, file paths, and more. To analyze this data effectively, you need to be able to manipulate these strings.

The following video provides a great overview of the most common and useful string functions. As you watch, don't just focus on the syntax; think about how each function could be applied to a security context. For example, LOWER() can standardize inconsistent user agent strings before grouping, and SUBSTRING() can extract a session ID from a URL parameter.

From Theory to Practice: Exploratory Data Analysis for SecOps

Now, let's apply these concepts to a realistic security scenario. Imagine you've been given a large JSON file containing enriched Endpoint Detection and Response (EDR) data. Your task is to perform an initial exploratory data analysis (EDA) to find suspicious patterns.

The following article is an excellent, hands-on guide that walks you through this exact process. It uses SQL to query security data, demonstrating how to combine aggregation, filtering, and string manipulation to gain insights.

Putting It All Together: Real-World Security Queries

The techniques in the article you just read are not just academic exercises; they are the basis for real-world security analytics. Let's look at a few more examples tailored to analyzing audit logs.

Example 1: Finding the Most Common Actions

Imagine you are analyzing Google Cloud audit logs. You want to know what the most frequent activities are in your environment over the last 30 days. This could help you establish a baseline of normal activity or spot an unusual spike in a particular action.

This query uses COUNT(*), GROUP BY, and ORDER BY to find the top 100 most frequent API calls.

SELECT
    proto_payload.audit_log.method_name AS api_method,
    proto_payload.audit_log.service_name AS service,
    resource.type AS resource_type,
    COUNT(*) AS action_count
FROM
    `Your_Log_View`
WHERE
    timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND log_id = "cloudaudit.googleapis.com/data_access"
GROUP BY
    api_method,
    service,
    resource_type
ORDER BY
    action_count DESC
LIMIT 100;

(This query is adapted from the Google Cloud documentation on "SQL queries for security insights".)

Example 2: Hunting for Malicious User Agents

Web server logs contain the user-agent string for every request, which identifies the client browser or tool. Attackers often use automated tools like sqlmap or nmap that have distinctive user agents. You can hunt for these tools using the LIKE operator.

SELECT
    client_ip,
    user_agent,
    COUNT(*) AS request_count
FROM
    web_access_logs
WHERE
    user_agent LIKE '%sqlmap%' OR user_agent LIKE '%nmap%'
GROUP BY
    client_ip,
    user_agent
ORDER BY
    request_count DESC;

This query identifies IP addresses that are using known hacking tools and tells you how many requests each one has made. This is a direct, actionable piece of intelligence derived from combining string matching with aggregation.

Example 3: Detecting Anomaly in API Usage

Let's consider a more advanced example. What if you want to find users whose API activity today is significantly higher than their own personal average? This is a form of anomaly detection. While the query below uses some advanced window functions we haven't formally covered, you should be able to understand the core logic. It calculates a running average and standard deviation for each user's daily activity and flags days where the activity is more than three standard deviations above the average.

This demonstrates the ultimate goal of these techniques: moving from simple counting to sophisticated, statistics-based threat hunting.

Conclusion

This lesson concludes our module on data manipulation and analysis. You have moved from simple data retrieval to complex analysis, equipping yourself with a powerful set of SQL skills directly applicable to cybersecurity.

Key Takeaways:

• String functions (SUBSTRING, LOWER, CONCAT, etc.) are essential for parsing, cleaning, and extracting data from text-based log fields.
• The LIKE operator with wildcards (%, _) is your primary tool for pattern matching in strings, perfect for hunting for indicators of compromise (IoCs) in log data.
• By combining aggregate functions (COUNT, AVG), grouping (GROUP BY), filtering (WHERE, HAVING), and string manipulation, you can build powerful analytical queries to establish baselines, find anomalies, and hunt for specific threats.

We have focused on analyzing data within a single table. However, security data is often spread across multiple tables. For instance, a log file might give you a suspicious user ID, but to find that user's name, role, and department, you need to look in a separate users table. In our next module, we will tackle this exact challenge by learning how to use JOIN clauses to combine data from multiple tables, unlocking a much deeper level of analysis.