Query and Indexing Tables
| Purpose | In this document, we will learn about an important feature from the Insights module: Query, which helps in indexing and visualizing tables, enhancing our data analytics capabilities. |
|---|
| Last Updated | October 31, 2024 |
|---|
Purpose of Queries in AI SIEM
In an AI-SIEM solution, queries enable SOC analysts to quickly search and extract critical security insights from large datasets. By leveraging structured querying in PostgreSQL and PRQL languages, analysts can isolate events, correlate logs, and identify potential threats based on specific criteria.
Why Queries are Critical for Security Analysts
Queries are essential to:
-
Accelerate Threat Detection: Enable rapid filtering and indexing of data to locate unusual patterns or behaviors.
-
Enhance Investigation Efficiency: Allow SOC analysts to drill down into logs, alerts, and user activity, helping to clarify security incidents quickly.
-
Optimize Workflow: Saved and repeatable queries streamline response processes for common investigative actions, reducing redundancy in analysis.
-
Support Compliance and Reporting: Queries can help generate reports that meet compliance requirements by pulling relevant data sets on-demand.
Different levels of analysts may use queries in unique ways:
-
Tier 1 Analysts: Often need quick access to saved queries for common alerts to validate or dismiss potential threats. These queries provide immediate insights into frequent incidents or data trends.
-
Tier 2 Analysts: Focus on in-depth analysis and correlation across datasets to understand incident root causes, often using more customized queries to build out detailed timelines or activity patterns.
-
Tier 3 Analysts and Threat Hunters: Rely on complex, flexible queries to investigate advanced threats, creating and adjusting queries to track indicators of compromise , unusual network activity, or user behavior anomalies.
For more information on PostgresSQL and PRQL languages follow links bellow:
Query Analyzer Overview
The Query Analyzer is your central interface for accessing, building, and managing queries. To access, open the Query app within the Raven portal, which provides the following main features:
Saved Queries
In the Saved Queries section, analysts can manage commonly used or high-value queries to enhance investigative efficiency. Accessing saved queries provides details such as:
-
Creator: Who created the query
-
Title: Query name
-
Query Prompt: The search string or criteria used in the query
-
Schedule: When is a query configured to run on a recurring schedule
-
Last Status: Most recent query result status
-
Rows: Number of records returned
-
Execution Time: Time taken to complete
-
Last Run Date: Timestamp for the last query run
Features for managing saved queries include:
-
Search Bar: Locate specific saved queries for quick access.
-
Pin Columns: Pin columns for key data points by selecting the column and choosing
Pin. -
Reorder Columns: Drag and drop columns to customize their order.
-
Reset Settings: Revert to default view settings.
-
Column Visibility: Toggle columns on/off to tailor the displayed information.
Query History
In the "History" section, analysts can view a chronological list of previously executed queries, which is useful for audits, tracking past investigations, or repeating similar queries. This section includes details such as:
-
Status: Result of the previous execution
-
Query Prompt: The search criteria used
-
Number of Rows: Data points returned
-
Execution Time: Time it took to run the query
-
Execution Date: Date and time of the query execution
Tables
The "Tables" section provides a reference list of available tables and columns within your Raven instance, making it easier to construct queries with precise data fields. This section is especially valuable for Tier 2 and Tier 3 analysts who may need a full data schema reference for complex queries.
Creating a Query
Steps to Run a New Query
-
Write the Query: Enter your query in the designated input section. Analysts can select either PostgreSQL or PRQL as the query language.
-
Run the Query: Once the query is complete, click the "Run Query" button at the bottom-right corner to execute it.
After execution, a unique Query ID will be generated and can be copied for reference or troubleshooting.
Post-Execution Results
After running a query, two additional sections become available:
-
Query Results: Shows the retrieved data in a table format. Analysts can search within the results or download them as an Arrow or Excel file for further analysis.
-
Visualize Results: Analysts can select chart types and libraries within the Raven Portal to create visualizations of the data. To use, choose a chart type and assign relevant data to each axis for a customized view.
Filtering Tables
The filtering feature helps users quickly explore and make sense of the query results. In the Raven Portal, we provide various options for filtering this data:
-
Hover and Filter: Hover over an object within rows or columns to display the filter icon (funnel). Click it to apply filters based on that object.
-
Column Filter: Alternatively, click the filter button, then select the specific column you wish to filter.
To clear applied filters, use the "CLEAR" button at the top of the Table view.
Using POSIX Regex Operators in SQL Queries
Regular expressions, commonly known as regex, are a powerful tool used to search, match, and manipulate text patterns within strings. In querying, regex allows you to define flexible search patterns, helping you identify text that follows a particular structure, rather than an exact match.
For example, rather than searching for the exact term "DTACT," you can use a POSIX regular expression to find variations like "DTOCT" or "DT1CT," capturing any pattern that fits a specific structure. With these expressions, you can fine-tune your queries using the ~ operator for case-sensitive searches, or opt for the ~* operator if you need to perform case-insensitive searches, allowing for more flexibility in your data filtering.
POSIX regular expressions are a specific dialect of regex. Different systems may use other dialects, but here at DTACT, we use POSIX.
Example Query:
To match any author name that includes "michael" (case-insensitive):
SELECT * FROM security_events WHERE author ~* 'michael';
Regex Pattern Example:
To match patterns where "DT" is followed by any single character and then "CT" (e.g., DTACT, DTOCT, DT1CT):
SELECT * FROM security_events WHERE event_description ~ 'DT.CT';
Follow link below to learn more about supported operators: