# SQL search
## How to pivot to SQL Search from the Investigate page (Advanced Investigation)
Click the **Advanced** 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
| | | |
| ------------ | ---------------------------- | ------------------------------------ |
| `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](https://docs.vectra.ai/reference/metadata-attributes/vectra-ai-platform-network-metadata-attributes)
AWS: [Vectra AI Platform AWS CloudTrail metadata attributes](https://docs.vectra.ai/reference/metadata-attributes/vectra-ai-platform-aws-cloudtrail-metadata-attributes)
Azure AD and M65: [Vectra AI Platform Azure AD and M365 metadata attributes](https://docs.vectra.ai/reference/metadata-attributes/vectra-ai-platform-azure-ad-and-m365-metadata-attributes)
## 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 | Notes | |
| --------------------- | -------------------------------------------------------------------- | ----- | - |
| 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 |
| -- | ------------------------- | ------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------- |
| 1 | | Greater than operator | |
| 2 | | Greater than or equal operator | |
| 3 | | Less than operator | |
| 4 | <= | Less than or equal operator | |
| 5 | | Not equal operator | |
| 6 | | Equal operator | |
| 7 | | Logical AND | |
| 8 | IS NOT NULL | NOT NULL value test | |
| 9 | IS NULL | NULL value test | |
| 10 | NOT | Negates value | NOT IN, NOT LIKE, and NOT BETWEEN are all supported. |
| 11 | ! | Negates Value | |
| 12 | | Logical OR | |
| 13 | | Whether a value is within a set of values | |
| 14 | f(DISTINCT) | Return the count of a number of different values | |
| 15 | MAX() | Return the maximum value | |
| 16 | MIN() | Return the minimum value | |
| 17 | SUM() | Return the sum | |
| 18 | | Simple pattern matching | |
| 19 | REGEXP\_COUNT | Returns the number of occurrences of the regex pattern | |
| 20 | REGEXP\_EXTRACT\_ALL | Returns **all matches** of a regex pattern as an array | |
| 21 | REGEXP\_EXTRACT | Returns the **first match** (or capture group) from a regex pattern | |
| 22 | REGEXP\_LIKE | Returns **TRUE/FALSE** if the string matches the pattern | |
| 23 | REGEXP\_POSITION | Returns the **position (index)** of the first match | |
| 24 | REGEXP\_REPLACE | Replaces parts of the string that match the pattern with something else | |
| 25 | REGEXP\_SPLIT | Splits the string into an **array of substrings** using a regex | |
| 26 | | Whether a value is within a range of values | |
| 27 | CONTAINS() | Used for IP-based operations in pill-based search. Specifically checking if cidr range contains IP | |
| 28 | LOWER() | Return the argument in lowercase | |
| 29 | AVG() | Return the average value of the argument | |
| 30 | COUNT() | Return a count of the number of rows returned | |
| 31 | LENGTH() | Return the length of a string in bytes | |
| 32 | COALESCE | Used for is/not empty assessment | |
| 33 | TRY\_CAST() and CAST() | Used for IP based operations in pill based search. Specifically casting to IPADDRESS and cidr | |
| 34 | ANY\_MATCH and ALL\_MATCH | Used for nested data structures in pill based search | |
| 35 | | Convert to uppercase | |
| 36 | | Return the population standard deviation | |
| 37 | STDDEV\_SAMP() | Return the sampled standard deviation | |
| 38 | STDDEV\_POP() | Return the population standard deviation | |
| 39 | | Return the absolute value | |
| 40 | | Return concatenated string | |
| 41 | CONTAINS() |
Validates if a string contains a specific string, usage would be: WHERE CONTAINS(query,'deepseek')
| |
## Date interactions we support
| | | | |
| - | ----------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------- | ----- |
| | Field | Description | Notes |
| 1 | | Extract the date part of a date or datetime expression. Can also be used to convert a string to a Date object, eg | |
| 2 | NOW() | Return the current date and time | |
| 3 | TIMESTAMP | Used for time range in pill based translation | |
| 4 | DATE\_ADD([)](https://trino.io/docs/current/functions/datetime.html#date_add) | Used for relative date ranges in pill based search | |
| 5 | DATE\_DIFF() | Return the difference between 2 date elements | |
| 6 | FROM\_ISO8601\_TIMESTAMP() | Converts an ISO 8601 formatted timestamp into the relevant datetime | |
## Functions we support
| | Function | Description | Notes |
| - | -------------------------------------------------------- | ------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- |
| 1 | sum(x) | The sum of the values in a cell for the rows returned | Aggregate functions are not supported in Where conditions. |
| 2 | count(x) | Return a count of the number of rows returned | Aggregate functions are not supported in Where conditions. |
| 3 | **date**(x) → date | Convert a value to a date-type value | Needed for users to compare against date-type columns like |
| 4 | **date\_add**(unit, value, timestamp) → \[same as input] | Adds an interval of type to . Subtraction can be performed by using a negative value. | Eg |
| 5 | **date\_diff**(unit, timestamp1, timestamp2) → number | Express as numbers of | Eg |
| 6 | **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: |
| 7 | **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 |
