Query Details
Parse Apache Accesslog
Query
# *Parse Apache Access.log*
## Query Information
#### Description
This KQL query parses raw Apache access.log entries stored in the accesslog table. It uses regular expressions to extract key fields from each log line, such as ClientIP, Ident, User, Timestamp, HTTP Method, URL, Protocol, Status Code, Bytes Sent, Referer, and User-Agent. After extracting these values, the query removes the original raw data column and presents the parsed fields in a structured table format for easier analysis.
#### Author <Optional>
- **Name: Benjamin Zulliger**
- **Github: https://github.com/benscha/KQLAdvancedHunting**
- **LinkedIn: https://www.linkedin.com/in/benjamin-zulliger/**
#### References
## Azure Data Explorer
### Query Combination Devices with no AV Scans and Vulnerabilities
```KQL
accesslog
| extend
ClientIP = extract(@"^(\S+)", 1, data),
Ident = extract(@"^\S+\s+(\S+)", 1, data),
User = extract(@"^\S+\s+\S+\s+(\S+)", 1, data),
TimeRaw = extract(@"\[(.*?)\]", 1, data),
Method = extract(@"""(\S+)", 1, data),
Url = extract(@"""\S+\s+(\S+)", 1, data),
Protocol = extract(@"""\S+\s+\S+\s+(\S+)""", 1, data),
Status = toint(extract(@"""\s+(\d{3})\s", 1, data)),
Bytes = toint(extract(@"\s(\d+|-)\s+""", 1, data)),
Referer = extract(@"""\s+""([^""]*)""\s+""", 1, data),
UserAgent = extract(@"""\s+""([^""]*)""$", 1, data)
| project-away data
```
Explanation
This KQL query is designed to process and organize data from Apache access logs stored in a table called accesslog. Here's a simple breakdown of what the query does:
-
Extract Key Information: The query uses regular expressions to pull out important pieces of information from each log entry. These pieces include:
- ClientIP: The IP address of the client making the request.
- Ident: The RFC 1413 identity of the client (often a placeholder).
- User: The username of the person making the request, if available.
- Timestamp: The date and time when the request was made.
- HTTP Method: The HTTP method used (e.g., GET, POST).
- URL: The URL that was requested.
- Protocol: The HTTP protocol version used.
- Status Code: The HTTP status code returned by the server (e.g., 200, 404).
- Bytes Sent: The size of the response in bytes.
- Referer: The URL of the webpage that referred the request.
- User-Agent: Information about the client's browser or software.
-
Remove Raw Data: After extracting these fields, the query removes the original raw log data column (
data) to clean up the output. -
Structured Output: The result is a neatly organized table that displays the extracted fields, making it easier to analyze and understand the log data.
This query is useful for transforming raw log data into a more readable and analyzable format, which can help in monitoring and troubleshooting web server activity.
Details
Benjamin Zulliger
Released: December 17, 2025
Tables
Keywords
Operators