The following article provides tips and sample queries that can be used in Expert Mode search in the Alert Logic console to give you an idea of how search can be used to find log messages and File Integrity Monitoring (FIM) events. This is not an exhaustive list.
Tips for Writing Queries in Expert Mode
These tips are meant to help you create and refine queries in the Search area of the Alert Logic console. In Search, you can use a graphical query builder ("Simple mode") or write queries using the underlying query language ("Expert mode").
The language shares concepts with Structured Query Language (SQL), and if you are familiar with SQL, you will be able to apply your knowledge to create and refine your queries. Unlike SQL, these queries operate on data with little or no inherent structure. Though Alert Logic defines many data types, like log messages, FIM events, and observations, the only common fields are a unique message identifier and at least one timestamp defining “when” the record occurred.
You can explore the query language by building queries in Simple mode and then changing the view to Expert mode. Here are some additional tips for building your own queries:
- Strings are 'single quoted'.
- Field names and aliases are "double quoted." Double quoting can be used to access fields with the same name as SQL-reserved words, such as "count".
- LIMIT and WHERE are optional functions.
- GROUP BY, ORDER BY, and HAVING work just as they do in SQL.
- Many functions operate on arrays of values. Array data is written with square brackets, such as a list of three numbers like [1, 2, 3]. You can match a field against any item in a list using an expression like User IN ['alert', 'alertlogic'].
- You currently cannot join multiple data types in a single query.
- You can use single-line comments starting with -- to help organize large queries.
- Access JSON data in log messages using parsed.json.field.subfield syntax. For example, if a log message contains the JSON {"user":{"name":"albert", "id":123}}, parsed.json.user.name would return the string 'albert'.
- null, true, and false are reserved words that work as they do in SQL.
Sample Search Queries
You can use the following sample queries as a starting point to build your own Search queries. After each sample is a link that will open the Alert Logic console with the sample automatically entered in Search.
Search for Text in Log Messages
SELECT time_recv AS "Time Received", message AS "Message"
FROM logmsgs
WHERE Message CONTAINS 'a pattern'
ORDER BY time_recv DESC
LIMIT 1000
Open this query in the Alert Logic Console.
Search for Parsed Log Messages
SELECT
time_recv,
parsed.rule_name AS "Message Type",
pased.token_names AS "Tokens",
message
FROM logmsgs
WHERE "Message Type" != NULL
Open this query in the Alert Logic Console.
Search for Amazon Web Services Log Messages Mentioning a Specific User
SELECT
time_recv,
parsed.token_names.aws.username AS "AWS user name",
message
FROM logmsgs
WHERE
-- string "null" comes from AWS, not the same null in our language
"AWS user name" != 'null' AND "AWS user name" IS NOT NULL
Open this query in the Alert Logic Console.
Count File Integrity Monitoring Events by Event Type
SELECT event_type AS "Event Type",
COUNT(event_type) AS "Count"
FROM fimdata
GROUP BY event_type
Open this query in the Alert Logic Console.
Complex File Integrity Monitoring Summary Query
-- File hashes seen in FIM events across the most hosts
-- with summary information about hosts, files, owner, and event type
SELECT
sha1_hash AS "SHA1 Hash",
UCOUNT(asset.dict.asset.host.name AS "Asset Name") AS "Host Count",
SET("Asset Name") AS "Hosts",
UCOUNT(CONCAT_WS([path, file_name], '/') AS "File") AS "File Count",
SET("File") AS "Files",
SET(file_owner) AS "Owners",
SET(file_group) AS "Groups",
COUNT(event_type) AS "Events",
SET(event_type) AS "Event Types"
FROM fimdata
WHERE sha1_hash != NULL
GROUP BY "SHA1 Hash"
ORDER BY "Host Count" DESC, "Events" DESC
Comments
0 comments
Please sign in to leave a comment.