Splunk

SELECT Statements

A SELECT statement can consist of the following basic clauses.

  • SELECT

  • INTO

  • FROM

  • JOIN

  • WHERE

  • GROUP BY

  • HAVING

  • UNION

  • ORDER BY

  • LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {

[ TOP <numeric_literal> | DISTINCT ]

{

*

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

<expression>

]

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

  1. Return all columns:

    SELECT * FROM DataModels

  2. Rename a column:

    SELECT [Owner] AS MY_Owner FROM DataModels

  3. Cast a column's data as a different data type:

    SELECT CAST(DatasetLimiting AS VARCHAR) AS Str_DatasetLimiting FROM DataModels

  4. Search data:

    SELECT * FROM DataModels WHERE Id = 'SampleDataset'

  5. Return the number of items matching the query criteria:

    SELECT COUNT(*) AS MyCount FROM DataModels

  6. Return the number of unique items matching the query criteria:

    SELECT COUNT(DISTINCT Owner) FROM DataModels

  7. Return the unique items matching the query criteria:

    SELECT DISTINCT Owner FROM DataModels

  8. Summarize data:

    SELECT Owner, MAX(DatasetLimiting) FROM DataModels GROUP BY Owner

    See Aggregate Functions below for details.

  9. Retrieve data from multiple tables.

    SELECT DataModels.Name, Datasets.ObjectName FROM DataModels INNER JOIN Datasets ON DataModels.Id = Datasets.ModelName

    See JOIN Queries below for details.

  10. Sort a result set in ascending order:

    SELECT Name, Owner FROM DataModels ORDER BY Owner ASC

  11. Restrict a result set to the specified number of rows:

    SELECT Name, Owner FROM DataModels LIMIT 10

  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM DataModels WHERE Id = @param

Aggregate Functions

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM DataModels WHERE Id = 'SampleDataset'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT Name) AS DistinctValues FROM DataModels WHERE Id = 'SampleDataset'

AVG

Returns the average of the column values.

SELECT Owner, AVG(DatasetLimiting) FROM DataModels WHERE Id = 'SampleDataset' GROUP BY Owner

MIN

Returns the minimum column value.

SELECT MIN(DatasetLimiting), Owner FROM DataModels WHERE Id = 'SampleDataset' GROUP BY Owner

MAX

Returns the maximum column value.

SELECT Owner, MAX(DatasetLimiting) FROM DataModels WHERE Id = 'SampleDataset' GROUP BY Owner

SUM

Returns the total sum of the column values.

SELECT SUM(DatasetLimiting) FROM DataModels WHERE Id = 'SampleDataset'

JOIN Queries

The Provider for Splunk supports standard SQL joins like the following examples.

Inner Join

An inner join selects only rows from both tables that match the join condition:

SELECT DataModels.Name, Datasets.ObjectName FROM DataModels INNER JOIN Datasets ON DataModels.Id = Datasets.ModelName

Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:

SELECT DataModels.Name, Datasets.ObjectName FROM DataModels LEFT JOIN Datasets ON DataModels.Id = Datasets.ModelName

Date Literal Functions

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

L_TODAY()

The current day.

SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()

Also available:

  • L_LAST/L_THIS/L_NEXT MONTH

  • L_LAST/L_THIS/L_NEXT QUARTER

  • L_LAST/L_THIS/L_NEXT YEAR

L_LAST_N_DAYS(n)

The previous n days, excluding the current day.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

  • L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

Every day in every week, starting n weeks before current week, and ending in the previous week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

Every day in every week, starting the following week, and ending n weeks in the future.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)

Also available:

  • L_LAST/L_NEXT_N_MONTHS(n)

  • L_LAST/L_NEXT_N_QUARTERS(n)

  • L_LAST/L_NEXT_N_YEARS(n)

Projection Functions

AVG([DISTINCT] expression)

Returns the average of the values of field expression.

  • expression: The expression to use to compute the average.

COUNT([DISTINCT] expression)

Returns the number of occurrences of the field expression. To indicate a specific field value to match, format expression as eval(field="value").

  • expression: The expression to use to compute the count.

EARLIEST(expression)

Returns the chronologically earliest seen value of expression.

  • expression: The expression to use to compute the earliest.

LATEST(expression)

Returns the chronologically latest seen value of expression.

  • expression: The expression to use to compute the latest.

MAX([DISTINCT] expression)

Returns the maximum value of the field expression. If the values of expression are non-numeric, the max is found from alphabetical ordering.

  • expression: The expression to use to compute the max.

MEDIAN(expression)

Returns the middle-most value of the field.

  • expression: The expression to use to compute the median.

MIN([DISTINCT] expression)

Returns the minimum value of the field expression. If the values of expression are non-numeric, the min is found from alphabetical ordering.

  • expression: The expression to use to compute the min.

MODE(expression)

Returns the most frequent value of the field expression.

  • expression: The expression to use to compute the mode.

RANGE(expression)

Returns the difference between the max and min values of the field expression.

  • expression: The expression to use to compute the range.

SUM([DISTINCT] expression)

Returns the sum of the values of the field expression.

  • expression: The expression to use to compute the sum.

SUMSQ(expression)

Returns the sum of the squares of the values of the field expression.

  • expression: The expression to use to compute the sum of the squares.

STDEV(expression)

Returns the sample standard deviation of the field expression.

  • expression: The expression to use to compute the sum of the STDEV.

STDEVP(expression)

Returns the population standard deviation of the field expression.

  • expression: The expression to use to compute the sum of the STDEVP.

VAR(expression)

Returns the sample variance of the field expression.

  • expression: The expression to use to compute the sum of the VAR.

VARP(expression)

Returns the population variance of the field expression.

  • expression: The expression to use to compute the sum of the VARP.

PreviousDataRobotNextAnalytics

Last updated