Search functionality for Alert Logic® has been completely revised for a better, more comprehensive, user experience. Log search utilizes SQL-type text strings with an advanced search assistant and projection editor.
Note: Improved log search, and the SQL-type text string search that comes with it, is available within the Alert Logic console - for Alert Logic Cloud Defender or Log Manager customers at Search > Log Search or Log Search BETA, and for Alert Logic Essentials, Professional, or Enterprise customers in the navigation menu () > Investigate > Search > Log Search.
In This Article
Log Search Elements
A query, or a complete log search, is made of two parts, WHERE and SELECT.
- WHERE allows you to filter the results of your search. The system will return logs WHERE the statement is true. Think of WHERE as "I want to see logs WHERE 'X' is true."
- SELECT allows you to SELECT the fields that you want to be presented in the results. By default, a log search will return the raw log and timestamp associated with it - [Time Received], [Message].
The following terms are used throughout this article and are defined here for your reference:
- Metadata: Additional information associated with the log from Alert Logic parsers and agents
-
Tokens: Identified by parsers and the "Key" part of the Key:Value pair; you must use brackets around tokens
Note: Metadata and tokens are case sensitive. - Operators: Define how a query will work and what will be returned; identifiable as orange text within a query.
-
String: Defined by the user and is usually compared to the "Value" part of a Key:Value pair
Note: Strings are not case sensitive.
Note: [Tokens/Metadata] are the same across different environments. What makes your logs and environments unique are the values/"strings" associated with those [Tokens/Metadata].
WHERE Operators
The following is a basic structure of a WHERE operator:
[Token] OPERATOR "String"
Example:
[User Name] = "admin"
= and CONTAINS
The following details explain how = and CONTAINS operators act within a log search:
-
= searches for messages where a token is equal to a specified value - an exact match.
Example:[Host Name] = "workstation-dev-12b" -
CONTAINS searches for messages where a token contains a specified value but does not need to match the entire value.
Example:[Host Name] CONTAINS "dev"
IN, CONTAINS_ANY, and CONTAINS_ALL
The following details explain how IN, CONTAINS_ANY, and CONTAINS_ALL operators act within a log search:
-
IN searches for messages where a token value is equal to any of the specified values in a set.
Example:[Windows Event ID] IN ("4625", "4771")is the logical equivalent of([Windows Event ID] = "4625" or [Windows Event ID] = "4771")
-
CONTAINS_ANY searches for messages where a token contains any of the specified values in a set.
Example:[Windows Event ID] CONTAINS_ANY ("4625", "4771")is the logical equivalent of([Windows Event ID] CONTAINS "4625" or [Windows Event ID] CONTAINS "4771")
-
CONTAINS_ALL searches for messages where a token contains all the specified values in a set.
Example:[Windows Event ID] CONTAINS_ALL ("4625", "4771")is the logical equivalent of
([Windows Event ID] CONTAINS "4625" and [Windows Event ID] CONTAINS "4771")
Note: Be careful with CONTAINS_ALL and keep in mind that each log has a single value associated with most tokens. Most of the time you will have to use CONTAIN_ANY unless the token you are specifying is [Message].
| Single Value | List of Values | |
| Exact Match | = "x" | IN ("x", "y", "z") |
| String Exists within Value | CONTAINS "x" |
CONTAINS_ANY ("x", "y", "z") CONTAINS_ALL ("x", "y", "z") |
EXISTS and ISNULL
Not all tokens exist in all logs. For example, it would be difficult to find an Amazon Resource Name (ARN) in a Windows log. Understanding this is important because we can use EXISTS and ISNULL to find log messages based on whether they have a certain token or not.
-
EXISTS searches for messages where the specified token is defined.
Example:
EXISTS [src Host] -
ISNULL searches for messages where the specified token does not exist.
Example:
ISNULL [src Host]
Note the following differences in syntax that apply to EXISTS and ISNULL that do not apply to the other WHERE operators described above:
- The operator goes in front of the token: EXISTS [ARN]
- There is no need to use a "string"/value because these operators do not consider what value is associated with the [token]. They only care if the [token] is present or not in the log.
Common Use Case
[Message Type] is metadata that is associated to a log after a parser determines that it is a particular kind of log. If a log comes through the Alert Logic system and does not match any of the parser patterns, then no [Message Type] is associated with it, and it is considered an unparsed log.
In order to limit a search to only return parsed logs, use the query EXISTS [Message Type]. Conversely, use ISNULL [Message Type] to return only unparsed logs.
LIKE
LIKE is an advanced operator that allows you to use wildcards in your searches. A wildcard is a symbol that is used to replace or represent one or more characters.
Wildcards available for LIKE include:
- % represents zero or more characters. For example, bl% finds bl, black, blue, and blob.
- _ represents a single character. For example, h_t finds hot, hat, and hit.
- [ ] represents any single character within the brackets. For example, h[oa]t finds hot and hat, but not hit.
- ^ represents any single character not in the brackets. For example, h[^oa]t finds hit, but not hot and hat.
- - represents a range of characters. For example, c[a-b]t finds cat and cbt.
LIKE operates similar to the = operator in that it compares your string to the entire value. For example, say the log you are looking for has a [Host Name] of "AlertOfLogic". If your query is [Host Name] LIKE "of", it will not match against that log. You would have to consider the other characters in that value and use [Host Name] LIKE "%of%".
Negations
NOT
NOT negates the condition that follows it. This can be applied to any operator. For example, NOT [Host Name] CONTAINS "AlertLogic" will return all logs where the [Host Name] does not contain the string "AlertLogic".
!=
!= searches for messages where a token is not equal to a specified value. For example, [Host Name] != "AlertLogic" would return all logs where the [Host Name] is not exactly "AlertLogic".
Note: = is the only operator that lets you use a "bang" (!) to quickly negate something. For all other operators, use NOT.
Compounds
AND
When using AND, both conditions must be true for the query to return results (X and Y).
Example: This query will return Windows failed logins with usernames that contain "admin" or "dev" -
[Message Type] = "Windows Login Failed" AND [User Name] CONTAINS_ANY ("admin", dev")
Only the overlap between the two conditions is returned.
OR
When using OR, either condition must be true for the query to return results (X or Y).
Example: This query will return all Windows failed logins and all logs where usernames contain "admin" or "dev" -
[Message Type] = "Windows Login Failed" OR [User Name] CONTAINS_ANY ("admin", "dev")
All matches from each condition are returned.
Use parentheses for order of operations. The two queries below are similar but will return different results:
(EXISTS [ARN] AND [Windows Event ID] = "4625") OR [Message Type] = "Windows Account Lockouts"
EXISTS [ARN] AND ([Windows Event ID] = "4625" OR [Message Type] = "Windows Account Lockouts")
SELECT Operators
The SELECT statement of a search query is where you get to SELECT what data you see. Remember that SELECT acts as both the name of a statement and an operator. All SELECT statements will start with the operator SELECT, as seen below:
The default SELECT statement is SELECT [Time Received], [Message]. This returns the following because only Time Received and Message are selected. In this context, [Message] is referring to the entire raw log:
SELECT allows you to specify any [Tokens/Metadata] that you want displayed separately in the query results.
Example: The following query will return the format pictured below -
SELECT [Time Received], [AWS Source], [AWS Event Name], [awsRegion], [accountId]
Note: The order that you place these tokens into the SELECT operator is important. If you want to pull individual tokens alongside the full log message, Alert Logic recommends listing [Message] last to make it easier to read.
ORDERBY
With ORDERBY, you can specify the token that the results should be sorted on. You should then specify if they are sorted in ascending (ASC) or descending (DESC) order.
Example:
ORDERBY [Time Received] DESC
ORDERBY [User Name] ASC
Aggregation
Aggregation allows you to cluster logs together based on the tokens that you define. For example, assume that we have 5,000 logs in total. If these logs are then aggregated on [accountID], a query will give you a count of how many of the 5,000 logs belong to each AWS account:
| accountID | Message Count |
| 123456 | 1000 |
| 987654 | 4000 |
When multiple tokens are aggregated on, a query will return a count of the unique combinations of the values from those tokens. The results from an aggregation on [accountID] and [sourceIP] could look like the following:
| accountID | sourceIP | Message Count |
| 123456 | 156.251.2.1 | 750 |
| 123456 | 156.251.2.5 | 250 |
| 987654 | 32.16.25.1 | 1000 |
| 987654 | 32.16.25.4 | 900 |
| 987654 | 156.251.2.1 | 2100 |
The following information shows a structure of aggregation SELECT statement and breaks down each element:
SELECT [Token], COUNT ( [Message] ) AS "MessageCount" GROUPBY [Token] ORDERBY "MessageCount" DESC
-
SELECT: This designates the token that you want to aggregate on. If multiple tokens are aggregated on, you will get a count of unique combinations of each token. You will have to separate the SELECT function from the rest of the SELECT statement with a single comma after the list of tokens. This is the only operator that requires this.
Example:
SELECT [X], [Y], - COUNT: This part of the aggregation statement counts the number of logs that fall into the tokens that you have designated in SELECT and presents it as "Message count". This portion is always the same - use COUNT ( [Message] ) AS "MessageCount".
- GROUPBY: This must mirror the tokens designated in SELECT.
- ORDERBY: This is typically used to order by the count of logs but in this case, you need to reference the output of the COUNT function, which is the alias "MessageCount".
Aggregation Use Cases
Example 1
Understand the types of logs in a customer environment by running a blank WHERE statement and aggregating on Message Type. This query gives you a high-level view of what types of logs you have.
SELECT [Message Type], COUNT( [Message] ) AS "MessageCount" GROUPBY [Message Type] ORDERBY
"MessageCount" DESC
Example 2
Investigate users being added to groups by filtering for Windows Group Member Added (Security Enabled) for WHERE and aggregating on Message Type, Caller User Name, Target User, and Target Group for SELECT. This query will return a list of users performing changes on Windows groups.
[Message Type] = "Windows Group Member Added (Security Enabled)"
SELECT [Message Type], [Caller User Name], [User Name], [Target Group], COUNT( [Message] ) AS
"MessageCount" GROUPBY [Message Type], [Caller User Name], [User Name], [Target Group] ORDERBY
"MessageCount" DESC
HAVING
HAVING limits results based on a number in the resulting logs and is usually used alongside an aggregation query. This can use mathematical logic such as <, >, or =. Example:
HAVING "Message Count" > 30
Note: This is currently limited to output for the COUNT operator, as all other numbers are stored as strings and cannot be used against mathematical logic.
Example Log Search Queries
The following examples are some of the endless possibilities of log search queries that you can utilize to find logs that are important to you.
- Utilize the following query to return logs where the Host Name does not contain the given string(s), in this case "Alert" or "Logic":
NOT [Host Name] CONTAINS_ANY ("Alert", "Logic") - Utilize the following query to return users who failed to log in to a Windows machine greater than 100 times and to aggregate on User Name and Host Name:
[Message Type] = "Windows Login Failed"
SELECT [User Name], [Host Name], COUNT( [Message] ) AS "MessageCount" GROUPBY [User Name],
[Host Name] ORDERBY "MessageCount" DESC HAVING "Message Count" > 100 - Utilize the following query to display all types of log messages associated with the Host, in this case named "alert-wf-ad7":
[Host Name] = "alert-wf-ad7"
SELECT [Message Type], COUNT( [Message] ) AS "MessageCount" GROUPBY [Message Type] ORDERBY
"MessageCount" DESC
Comments
0 comments
Please sign in to leave a comment.