SQL search

Accessing SQL Search, examples, syntax, fields, tables, operators, and functions supported.

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:

SELECT *
FROM network.isession
LIMIT 10

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

AWS: Vectra AI Platform AWS CloudTrail metadata attributes

Azure AD and M65: 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
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