Alert Logic Search technology allows you to aggregate the results of a search, which you can use to summarize data, like counting the unique IP addresses in your log messages or sorting by File Integrity Monitoring event by host with the most files changed.
This article provides steps to build a search query that will aggregate your search results to produce a report on the most frequent or common occurrences of a message type or field. Using sorting, grouping, and aggregation, you can create powerful queries to uncover patterns in your security data, and understanding how limits and conditions are applied to aggregation will help you generate concise and meaningful reports like top message types or failed logins. Once written, these queries can be saved or scheduled to execute periodically and to help you create review processes or standardize investigations.
Building a Query for Aggregation
The following query, which you can build in the Alert Logic console at (navigation menu) > Investigate > Search > Search, summarizes all your log messages by counting the unique message types, such as Windows Login Failed or Unix SUDO Successful Command. You can use this query to understand which kinds of security data Alert Logic will have available for analysis.
-- most frequently seen log message types collected in your environment
SELECT
parsed.rule_name AS "Message type",
COUNT (message) AS "Message count"
FROM logmsgs
GROUP BY "Message type"
ORDER BY "Message count" DESC
To utilize this query immediately, open this query in the Alert Logic console.
The search results will be unique to your data but will consist of lists of message types and counts in this format. Note that the row with an empty message type corresponds to the number of log messages with no known message type.
Message type | Message count |
---|---|
AWS EC2 Generic Message | 7834 |
AWS CloudFormation Generic Message | 1796 |
AWS Dynamo DB Generic Message | 1157 |
WSM Deny Log | 483 |
441 | |
Windows Event Generic | 236 |
CROND Session Created | 227 |
CROND Command Executed | 226 |
Windows Successful Login | 147… |
Breaking Down the Query
- The first three lines of this query ask for the message type (parsed.rule_name) and count to be included in the results. AS creates human-readable names for each column. COUNT is one of many summary functions, including MIN, MAX, UCOUNT (count of unique values), and SET (list of unique values).
- GROUP BY "Message Type" aggregates results, allowing for one result row for each unique message type. Note the re-use of the AS alias from the SELECT clause.
- ORDER BY "Message count" DESC sorts the message types from most to least common; that is, by decreasing message count.
You can summarize the combinations of fields, like message type and host name, by including both on the same line, like this:
GROUP BY "Message Type", "Host name"
Grouped fields appear unchanged in the results, and you must give an aggregation function, like COUNT, to summarize other data.
Using Aggregation in Simple Mode Searches
To explore aggregation without needing to use SQL syntax, you can use simple mode search and apply the group... function to any search term within a term's menu ().
Then, you can see the corresponding SQL by selecting Expert mode from the Simple/Expert Mode drop-down in the top right corner.
Limiting the Results of a Search
You can limit how many results a search will return, which reduces the size of a complete report and may help speed unaggregated searches. For example, if you are only looking for the first or most numerous results for a query, Alert Logic can complete the search as soon as the limit has been reached.
This query searches for recent Windows login failures:
-- The 10 most recent Windows login failures
SELECT
time_recv AS "Time received",
parsed.rule_name AS "Message type",
message AS "Message"
FROM logmsgs
WHERE "Message type" = 'Windows Login Failed'
ORDER BY "Time received" DESC
LIMIT 10
To utilize this query immediately, open this query in the Alert Logic console.
Breaking Down the Query
- "Message type" = "Windows Login Failed" matches only messages of this type.
- ORDER BY "Time received" DESC sorts the results from newest to oldest timestamp.
- LIMIT 10 restricts the search to only ten results. Because the results are sorted, this search will return the ten most recent results.
If there are ties in the sorting of your results, you may see varying results search-to-search. In this example, if the twenty most recent messages share the same timestamp, the ten messages that are returned with that timestamp may not be the same each time. To break ties, add more fields to sort by. A unique ID is assigned to every message as ingest_id, and this can always be used to break ties (ORDER BY "Time received" DESC, ingest_id ASC).
Combining Aggregation with Limited Results
The following query combines both aggregation and result limits to show the top ten most common message types:
-- The 10 most common message types in your environment
SELECT
parsed.rule_name AS "Message type",
COUNT (message) AS "Message count"
FROM logmsgs
GROUP BY "Message type"
ORDER BY "Message count" DESC
LIMIT 10
To utilize this query immediately, open this query in the Alert Logic console.
Breaking Down the Query
- By using GROUP BY and ORDER BY, this query produces a sorted list of message types seen by Alert Logic in the search window you specify; for example, the last hour or day.
- Adding LIMIT 10 summarizes the summary by only showing the ten most common message types. Note that the limit is taken after the messages are grouped and sorted by frequency, so limiting an aggregated search will not usually make the search faster. In this case, all the data is processed and then the limit is applied.
Summarizing Using a Condition
One final way to identify top results is by specifying a condition or test. For example, you might ask for only message types that appear more than 1000 times. To apply a test to the aggregated results, use HAVING in your query, such as in this example:
-- All message types in your environment that have been collected at least 1000 times
SELECT
parsed.rule_name AS "Message type",
COUNT (message) AS "Message count"
FROM logmsgs
GROUP BY "Message type"
HAVING "Message count" > 1000
ORDER BY "Message count" DESC
To utilize this query immediately, open this query in the Alert Logic console.
Breaking Down the Query
- HAVING works when you aggregate results with GROUP BY, and it must occur directly after the GROUP BY section of your query.
- To filter without grouping, use conditions in the WHERE clause.
Aggregation Functions
As shown above, you can use functions like COUNT or AVG to summarize data for the fields not listed in the GROUP BY clause. Here is the current list of these aggregation functions:
Function | Result | Notes |
---|---|---|
AVG(field) | Average (arithmetic mean) of numerical data in field | |
COUNT(field) | Number of results where field is not null | COUNT(*) returns the number of matching records, regardless of the value of any single field. |
MAX(field) |
Largest numerical value in field | |
MIN(field) |
Minimum numerical value in field | |
SET(field) LSET(field, max) |
Sorted list of the unique values of field | LSET returns at most max unique values. The largest allowed set size may be limited in future implementations. |
SUM(field) | Total of numeric data in field | |
UCOUNT(field) LUCOUNT(field, max) |
Number of unique values of field | LUCOUNT counts at most max unique values. The largest allowed set size may be limited in future implementations. |
Comments
0 comments
Please sign in to leave a comment.