Tips and Tricks for Building Queries
Quickly understand the components of LEQL to efficiently search your log data. As you write a LEQL query in Log Search, the query bar automatically suggests the elements of LEQL that you can use in your query. Use the up and down arrows to navigate the query suggestions and then press Enter to run a query. When running a blank query you can press Enter or Escape.
Order of operations
LEQL queries must be formatted in a specific order to generate the correct results.
After choosing the log sets you want to query, you can then choose the keys to view in your results to narrow your search, break log entries into sets to identify groups, and perform calculations to quantitatively understand your data.
Choose the keys to view in your results (optional)
Before you build your query, you can specify the keys you want to appear in your results with the select clause. This clause provides cleaner results that include only your specified keys, allowing you to find answers in your log data more quickly.
The select() clause is subject to some rules.
- It must be entered at the start of your query (the first clause).
- It is supported only with the
where()clause. - It is not supported with the
groupby()clause or thecalculate()function.
To learn more about the select clause and its usage, view the Components for Building a Query topic.
Find log entries
Begin writing a query by narrowing your search with a smaller selection of log entries. To find specific log events, you must start each query with a where() clause.
LEQL essentials
| Characters | Description | Example |
|---|---|---|
where( ) | Returns log events where the word "powershell" is present, case sensitive. | where(powershell) |
" " | Returns log events where the string "powershell.exe" is present, case sensitive, ignoring the period as a special character. | where("powershell.exe") |
/ \ | Returns log events where the string "password" is present with the quantifier, "{2,5}", stipulating the preceding character, "s", occurred a minimum of 2 times and a maximum of 5 times; 'password', 'passsword', 'passssword', or 'passsssword'. | where(/pass{2,5}word/) |
{ } | Returns log events where the character "w" occurs three consecutive times. | where(/w{3}/) |
[ ] | Returns log events where the characters "1", "2", and "3" are present in the event_code key. | where(event_code IN ["1", "2", "3"]) |
. | Returns logs events where the event-code key equals "467" with the character following "467" as a wildcard: "4673", "4674", "4675", and so on. | where(event_code=/467./) |
/.*text.*/ | Returns log events where the word "password" is present, regardless of what proceeds or follows the word if the word were to appear in a string. | where(/.*password.*/) |
Logical operators
| Syntax | Description | Example |
|---|---|---|
| AND | Returns log events that match both criteria. | expr1 AND expr2 |
| OR | Returns log events that match one or both criteria. | expr1 OR expr2 |
| NOT | Returns log events that match expr1 but not expr2. | expr1 NOT expr2 |
Comparison operators
| Syntax | Description | Example |
|---|---|---|
= | Returns log events where the key equals a specific value. You can input a numeric or string value. | KeyA=3 |
!== | Returns log events where the key values are not the same. You can input strings or numeric values. | KeyA!==KeyB |
== | Returns log events where the key values are the same. Use this operator to compare keys. You can compare strings or numeric values. | KeyA==KeyB |
< > | Returns log events that are less than or greater than the specified value. You can input a numeric value or key. | KeyA<KeyB or KeyA>KeyB |
<= >= | Returns log events that are less than or equal to, or greater than or equal to the specified value. You can input a numeric value or a key. | KeyA<=KeyB or KeyA<=3 or KeyA>=KeyB or KeyA>=3 |
Learn more about comparison operators.
Regex
| Syntax | Description | Example |
|---|---|---|
/ | Returns log events where "kurt cobain" is present using regex; the "/" character must be used to begin any regex query. | where(/kurt cobain/). |
\ | Returns log events where a "." is a part of the value, not a regex single character wildcard. | where(user=/jennifer\.lopez/) |
. | Returns log events where the string "202" is followed by any single character wildcard. | where(year=/202./) |
{ } | Returns log events where the key is outgoing_bytes and the value begins with "5" and "0" repeated 9 times: "50000000000". | where(outgoing_bytes=/50{9}/) |
[ ] | Returns log events where the key is asset and the value can range from "acme01" to "acme09". | where(asset=/acme0[1-9]/) |
? | Returns log events where the character closest to the '?' is optionally present: "passwords" or "password". | where(/passwords?/) |
Learn more about regex.
Variables
| Syntax | Description | Example |
|---|---|---|
${ } | Opens a previously stored single-value LEQL variable that can be referenced using the specified keys. | ${single-value variable} |
[${ }] | Opens a previously stored list-value LEQL variable that can be referenced using the specified keys. | [${list-value variable}] |
Learn more about variables.
Break log entries into sets
Now that you've narrowed your search, you can separate your log data into even smaller categories with clauses.
Clauses
| Syntax | Description | Example |
|---|---|---|
groupby() | Returns logs where users have failed authentications, grouped by user. | where(result ISTARTS-WITH "failed") groupby(user) |
having() | Returns logs where users have failed authentications, grouped by users that have 5 or more failed authentications. | where(result ISTARTS-WITH "failed") groupby(user) having(count>=5) |
sort() | Returns logs where users have failed authentications, grouped by user, and sorted from highest to lowest number of failed authentications. | where(result ISTARTS-WITH "failed") groupby(user)sort(dsc) |
limit() | Returns logs where users have failed authentications, grouped by user, but only showing 10 results. | where(result ISTARTS-WITH "failed") groupby(user) limit(10) |
The sort() clause must precede the limit() clause and immediately follow the groupby() clause when writing a query.
Learn more about groupby, having, sort, and limit.
Perform a calculation
Finally, you can leverage LEQL functions to perform calculations from the query bar.
| Syntax | Description | Example |
|---|---|---|
calculate(count) | Returns log events where the word "powershell" is present, grouped by the user, and the number of occurrences for each group has been calculated. | where(powershell) groupby(user) calculate(count) |
Learn more about calculations.