SQL search

This is documentation for the Private Preview of SQL Search. Please contact your Vectra account team if you are interested in joining the private preview.

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:

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

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	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()	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