> For the complete documentation index, see [llms.txt](https://docs.vectra.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.vectra.ai/operations/investigate/sql-search.md). # SQL search ## How to pivot to SQL Search from the Investigate page (Advanced Investigation) Click the **SQL Search** link to enter SQL Search:

You will then be on the SQL Search page where you can begin to enter a query:

## Getting Started Examples: ```language-markup SELECT * FROM network.isession LIMIT 10 ```

```language-markup SELECT orig_hostname, count(*) AS "requests" FROM network.isession GROUP BY orig_hostname ORDER BY "requests" DESC LIMIT 10 ```

## Syntax ``` SELECT [DISTINCT] select_expr [, select_expr] ... (SEE BELOW) FROM table [WHERE where_condition](SEE BELOW) [GROUP BY col_name, ...] [HAVING where_condition] [ORDER BY col_name, ...] [ASC/DESC] [LIMIT row_count] ``` Where: | Field | Description | Notes | | -------- | --------------------------------------------- | ------------------------------------------------------------------------------------- | | SELECT | | | | DISTINCT | Return only unique values | | | FROM | Target table to ingest from | | | table | The data stream that a user will select from. | | | WHERE | Filter clause | | | GROUP BY | Aggregate data and show the outputs | | | HAVING | Filter on a computed group by output | | | ORDER BY | Sort the resulting Data | Sorts Descending by Default | | LIMIT | Limit the number of values returned | Default is 10,000 if no limit is specified. 10,000 is also the maximum allowed limit. | ## select\_expr **Syntax** ``` 'select_expr' is: [function(]column_name[)] [AS alias] ``` Within select\_expr | Field | Description | Notes | | ------------ | ---------------------------- | ------------------------------------ | | function | | Supported functions are listed below | | column\_name | | | | \* | show all columns | | | AS | define an alias for a column | | ## Supported Fields The fields are the same as described in these existing docs that apply to the graphical pill-based search that already exists in Advanced Investigation: Network: [Vectra AI Platform network metadata attributes](/reference/metadata-attributes/vectra-ai-platform-network-metadata-attributes.md) AWS: [Vectra AI Platform AWS CloudTrail metadata attributes](/reference/metadata-attributes/vectra-ai-platform-aws-cloudtrail-metadata-attributes.md) Azure AD and M65: [Vectra AI Platform Azure AD and M365 metadata attributes](/reference/metadata-attributes/vectra-ai-platform-azure-ad-and-m365-metadata-attributes.md) ## Supported Tables * Microsoft 365 (m365) * active\_directory * exchange * general * sharepoint * Entra ID (entra) * signins * directory\_audits * AWS (aws) * cloudtrail * Azure (azurecp) * operations * Network (network) * beacon * dce\_rpc * dhcp * dns * http * isession * kerberos * ldap * match * ntlm * radius * rdp * smb\_files * smb\_mapping * ssh * ssl * x509 ## where\_condition **Syntax** ``` 'where_condition' is: column_name comparitive_operator [value] [logical_operator] [next condition] ``` | Field | Description | | --------------------- | -------------------------------------------------------------------- | | comparitive\_operator | these test the value of each column\_name against value | | logical\_operator | These allow for applying multiple different conditions to your query | ## Operators we support | Field | Description | Notes | | ------------------------- | ------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------- | | > | Greater than operator | | | >= | Greater than or equal operator | | | < | Less than operator | | | <= | Less than or equal operator | | | != | Not equal operator | | | = | Equal operator | | | AND | Logical AND | | | IS NOT NULL | NOT NULL value test | | | IS NULL | NULL value test | | | NOT | Negates value | NOT IN, NOT LIKE, and NOT BETWEEN are all supported. | | ! | Negates Value | | | OR | Logical OR | | | IN() | Whether a value is within a set of values | | | f(DISTINCT) | Return the count of a number of different values | | | MAX() | Return the maximum value | | | MIN() | Return the minimum value | | | SUM() | Return the sum | | | LIKE | Simple pattern matching | | | REGEXP\_COUNT | Returns the number of occurrences of the regex pattern | | | REGEXP\_EXTRACT\_ALL | Returns **all matches** of a regex pattern as an array | | | REGEXP\_EXTRACT | Returns the **first match** (or capture group) from a regex pattern | | | REGEXP\_LIKE | Returns **TRUE/FALSE** if the string matches the pattern | | | REGEXP\_POSITION | Returns the **position (index)** of the first match | | | REGEXP\_REPLACE | Replaces parts of the string that match the pattern with something else | | | REGEXP\_SPLIT | Splits the string into an **array of substrings** using a regex | | | BETWEEN ... AND ... | Whether a value is within a range of values | | | CONTAINS() | Used for IP-based operations in pill-based search. Specifically checking if cidr range contains IP | | | LOWER() | Return the argument in lowercase | | | AVG() | Return the average value of the argument | | | COUNT() | Return a count of the number of rows returned | | | LENGTH() | Return the length of a string in bytes | | | COALESCE | Used for is/not empty assessment | | | TRY\_CAST() and CAST() | Used for IP based operations in pill based search. Specifically casting to IPADDRESS and cidr | | | ANY\_MATCH and ALL\_MATCH | Used for nested data structures in pill based search | | | UPPER() | Convert to uppercase | | | STDDEV() | Return the population standard deviation | | | STDDEV\_SAMP() | Return the sampled standard deviation | | | STDDEV\_POP() | Return the population standard deviation | | | ABS() | Return the absolute value | | | CONCAT() | Return concatenated string | | | CONTAINS() |

Validates if a string contains a specific string, usage would be:
WHERE CONTAINS(query,'deepseek')

| | ## Date interactions we support | Field | Description | | -------------------------- | ----------------------------------------------------------------------------------------------------------------- | | DATE() | Extract the date part of a date or datetime expression. Can also be used to convert a string to a Date object, eg | | NOW() | Return the current date and time | | TIMESTAMP | Used for time range in pill based translation | | DATE\_ADD() | Used for relative date ranges in pill based search | | DATE\_DIFF() | Return the difference between 2 date elements | | FROM\_ISO8601\_TIMESTAMP() | Converts an ISO 8601 formatted timestamp into the relevant datetime | ## Functions we support | Function | Description | Notes | | -------------------------------------------------------- | ------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- | | sum(x) | The sum of the values in a cell for the rows returned | Aggregate functions are not supported in Where conditions. | | count(x) | Return a count of the number of rows returned | Aggregate functions are not supported in Where conditions. | | **date**(x) → date | Convert a value to a date-type value | Needed for users to compare against date-type columns like | | **date\_add**(unit, value, timestamp) → \[same as input] | Adds an interval of type to . Subtraction can be performed by using a negative value. | Eg `date_add('day', 1, date('2020-03-01')) = date('2025-03-02')` | | **date\_diff**(unit, timestamp1, timestamp2) → number | Express as numbers of | Eg `date_diff('day', date('2025-03-01'), date('2025-03-02')) = 1` | | **now**() → timestamp | Returns current time as of the start of this query's execution | Can be used with for relative time comparisons. Eg this gets all results from last 2 hours: | | **from\_iso8601\_timestamp**(string) → timestamp | Convert an ISO format string to a timestamp-type value | Needed for users to compare against timestamp-type columns like | --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.vectra.ai/operations/investigate/sql-search.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.