This topic describes the supported rule syntax for the IPDB Extractor service through descriptions and examples of supported and unsupported syntax. There is a finite set of syntax that you can use to construct rules for reports using the IPDB Extractor service in this release. This topic contains:
- Descriptions of supported and unsupported syntax with examples.
- Supported aggregate functions.
- Supported operators.
- Sample supported queries.
Supported and Unsupported Syntax
When you construct rules that contain SQL queries against the IPDB database in this release, you must adhere to the descriptions and syntax examples described in the following tables.
Supported Literal (Data) Values Syntax
| Description | Examples of Supported Syntax |
|---|---|
| For TEXT or string type data, enclose the string or text in single quote marks. If there is any special character such as apostrophe (for example 'data'), use two single quotes, ''data'', to enclose the data value. | select msg.id where msg='(Primary) Link status ''Down'' on interface INTNAME.' |
| For Date and Time (date/timestamp data type columns), use the ‘yyyy-mmm-dd hh:mm:ss’ syntax. | select time where time = ‘2012-sep-04 13:09:03’ |
| The system supports literal IP addresses. It treats columns that contain IP Addresses as strings/text so it uses the string comparison operator to evaluate the expressions. Use the following operators to ensure accurate processing:
|
Unsupported IN Syntax
| Description | Examples of Unsupported Syntax |
|---|---|
| Security Analytics does not support inusage in IP addresses | select ip.src where ip.src in between 'n.n.n.n' and 'n.n.n.n' |
Unsupported LIKE Syntax
| Description | Examples of Unsupported Syntax |
|---|---|
| Security analytics does not support () for like | user.dst not like ('%') |
Supported LIST Syntax
| Description | Examples of Supported Syntax |
|---|---|
| Enclose a list within parentheses in the Whereclause field. Use the IN operator. You must enclose values in a list within single quotes except for the following values:
| select ip.src,ip.dst where ip.dst IN ($[LIST]) |
Unsupported LIST Syntax
| Description | Examples of Unsupported Syntax |
|---|---|
| Do not omit parentheses. | select ip.src,ip.dst where ip.dst IN $[LIST] |
| Do not omit the IN operator. | select ip.src,ip.dst where ip.dst =($[LIST]) |
Supported Variable Syntax
When you assign the value of the variable in a run configuration, you must enclose the value within single quotes: 'value'.
| Description | Examples of Supported Syntax |
|---|---|
| Insert $ before a variable. Enclose a variable within braces. | columnname=${variable} |
Unsupported Variable Syntax
| Description | Examples of Unsupported Syntax |
|---|---|
| Do not omit the $. | columnname={variable} |
| Do not omit braces. | columnname=$variable |
| Do not substitute brackets for braces. | columnname=$[variable] |
Supported select Clause Syntax
You must include order by and group by columns in select clauses.
| Description | Examples of Supported Syntax |
|---|---|
| Select all columns for an IPDB data source. | select * |
| Select specific columns from an IPDB Data Source (You must separate each column with a comma.). | select column1,column2,column3,...,columnN |
| Use distinct in a select clause. You must enclose the column within parentheses when you use distinct. | select distinct(column1) |
| Use aggregate functions in select clause. Refer to "Supported Aggregate Functions," below, for a complete list of aggregate functions supported in this release contained in this article. | select count(msg.id) select count(distinct(msg.id)) |
Unsupported select Clause Syntax
| Description | Examples of Unsupported Syntax |
|---|---|
| Do not enclose column names in parentheses unless you are specifying an aggregation. The following example illustrates an unsupported use of parentheses. | select (msg.id), (ip.src) |
| Do not use computed columns. The following example illustrates an unsupported use of computed columns. | select msg.id+100, ip.src |
| Do not use column aliases (with or without AS). The following example illustrates an unsupported use of column aliases. | select msg.id as ID, ip.src SRC |
| Security Analytics does not support the Lower function in select clauses. |
Supported where Clause Syntax
You must include order by and group by columns in where clauses.
| Description | Examples of Supported Syntax |
|---|---|
| Enclose a value within single quotes if the value includes a space. The following syntax is incorrect: where msg = Auth start for user USERNAME from 20.20.20.2/20 to 10.10.10.1/10. | where msg = 'Auth start for user USERNAME from 20.20.20.2/20 to 10.10.10.1/10' |
| Enclose a value within single quotes if the value includes special characters. You do not need to enclose the following characters within single quotes:
The following syntax does not work: select url,size The following syntax does not work: where url = some/urls/string The following syntax does not work: where msg = Failover cable OK. | The following syntax works: where msg.id = 101001:10 The following syntax works: select url,size device spec: device-specifications where url = 'http://1.1.1.1//tsweb/images/clear.gif' The following syntax works: where url = 'some/urls/string' The following syntax works: where msg = ‘Failover cable OK.’ |
| Use this syntax to express a filter condition. | column1 <operator> 'value' |
| Use this syntax for Boolean and or Boolean or operators.Refer to "Supported Operators," below, for a complete list of operators supported in this release. | column1 <operator> 'value' and column2<operator> 'value' or column1 <operator> 'value' |
| Use this syntax for to check for null. | column1 is null column1 is not null |
| Use this syntax check for membership using the in operator. | column1 in ('value1','value2',...,'valueN') column1 not in ('value1','value2',...,'valueN') |
| Use this syntax to specify a range using the between operator. | column1 between 'value1' and 'value2' column1 not between 'value1' and 'value2' |
| Use this syntax to compare a string using the like operator. | column1 like 'value' column1 not like 'value' |
| Use this syntax to look for patterns using the % wildcard with the like operator. | select msg.id where msg like 'ip%' |
| Use the Lower function to ignore upper and lower case in where clause searches. You can associate the Lower function in a where clause with the TEXT Column type exclusively. If you specifyLower with Column type other than TEXT, Security Analytics displays an error message. Security Analytics does not support the Lower function for the BETWEEN, IN and NOT NULL Operators. | Lower(columnName) like ‘some%’ Lower(columnName) like lower(‘some%’) |
Unsupported where Clause Syntax
| Description | Examples of Unsupported Syntax |
|---|---|
| Do not use nested queries. | select msg.id where msg.id in (select msg.id from table where ip.src = '1.1.1.1') |
| Do not use the Lower function for the BETWEEN, IN and NOT NULL Operators. |
Supported order by Clause Syntax
Order by functionality is not case-sensitive.
| Description | Examples of Supported Syntax |
|---|---|
| Use this syntax to perform ascending (asc) and descending (desc) sorts with order by. |
|
| Only enclose column names in parentheses if you are applying an aggregate function to the column. The following example illustrates an invalid use of parentheses in an order by clause: order by (count(size)) asc | order by count(size) asc |
Supported group by Clause Syntax
| Description | Examples of Supported Syntax |
|---|---|
| Use this syntax to group one or more columns. Do not use parentheses to enclose column names. group by functionality is not case sensitive. |
|
Supported Aggregate Functions
The IPDB Extractor service supports the following aggregate functions and syntax in this release.
- count
- max
- min
- sum
- avg
You can use distinct with aggregation functions as shown in the following syntax:
- count(distinct)
- max(distinct)
- min(distinct)
- sum(distinct)
- avg(distinct)
Supported Operators
| Operator | Syntax |
|---|---|
| = (equals) | column1 = 'value' |
| != (does not equal) | column1 != 'value' |
| <= (less than or equal to) | column1 <= 'value' |
| >= (greater than or equal to) | column1 >= 'value' |
| < (less than) | column1 < 'value' |
| > (greater than) | column1 > 'value' |
| IN | column1 NOT ('value1','value2',...,'valueN') column1 not in ('value1','value2',...,'valueN') |
| between (range between two values) | column1 between 'value1' and 'value2' column1 NOT between 'value1' and 'value2' |
| and, or, NOT (Boolean) | condition1 and condition2 condition1 or condition2 condition1 not in ('value1','value2',...,'valueN') |
| like | column1 like 'value' column1 not like 'value' |
Sample Supported Queries
select msg.id, ip.src, ip.dst, user.dst where size is not null
select msg.id, size, ip.srcport where msg.id='109007' and size not between '10' and '20'
select max(distinct(size)) where msg.id in ('109007','109001')
select * where size != '99' and ip.src = '20.20.20.2'
select ip.srcport,ip.dstport where ip.dst != '225.31.125.90' order by ip.dstport asc
select ip.srcport,ip.dstport where ip.dst != '225.31.125.90' order by ip.dstport asc,ip.srcport desc
select ip.srcport,ip.dstport where ip.dst != '225.31.125.90' group by ip.srcport,ip.dstport order by min(distinct(ip.dstport)) asc, sum(distinct(ip.srcport)) desc
select time where time = ‘2012-sep-04 13:09:03’
select * where ip.src = '20.20.20.2' and ip.dst != '10.31.125.90' or ip.dst!= '225.31.125.90'
Sample Unsupported Queries
| Unsupported Query | Reason |
|---|---|
| select (msg.id), (ip.src), ip.dst, user.dst where size is not null. | You cannot enclose columns in parentheses. |
| select msg.id where msg.id IN (select msg.id from table where ip.src = ‘1.1.1.1’) | You cannot use a nested select (sub-query) to get the msg.idfield on some other condition. |
| select ip.src where ip.src in between ’10.10.10.1’ and ’10.10.9.1’ | You can only use the between operator for numeric and date and time data types. |
| select ip.srcport,ip.dstport where ip.dst != '225.31.125.90' order by count(distinct ip.dstport) asc | When you use distinct or any other aggregate function, you must enclose the column name in parentheses. |
| select ip.srcport,ip.dstport where ip.dst != '225.31.125.90' order by (ip.dstport) asc,(ip.srcport) desc | You cannot enclose column names in parentheses. |
| select ip.srcport,ip.dstport where ip.dst != '225.31.125.90' group by ip.srcport,ip.dstport order by COUNT((ipsrcport)) | You can only use one pair of parentheses to enclose column names. The system treats multiple sets of parentheses as nested expressions, which are not supported. |
| select time where time = ‘1999-NOVEMBER-01 10:10:10’ | Timestamp format is incorrect. |
| select time where time = ‘2012-11-11 10:10:10’ | Timestamp format is incorrect. |