Reporting: IPDB Rule Syntax

Document created by RSA Information Design and Development Employee on Jul 28, 2016
Version 1Show Document
  • Comment • 0
  • View in full screen mode
 

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

                     
DescriptionExamples 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:
  • =  (equals)
  • !=  (does not equal)
  • in (is contained in)
  • not in (is not contained in)
 

Unsupported IN Syntax

             
DescriptionExamples of Unsupported Syntax
Security Analytics does not support inusage in IP addressesselect ip.src where ip.src in between 'n.n.n.n' and 'n.n.n.n'

Unsupported LIKE Syntax

             
DescriptionExamples of Unsupported Syntax
Security analytics does not support () for likeuser.dst not like ('%')

Supported LIST Syntax

               
DescriptionExamples 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:
  • alphanumeric characters
  • : (colon)
  • _ (underscore)
  • . (period)
select ip.src,ip.dst where ip.dst IN ($[LIST])

Unsupported LIST Syntax

                 
DescriptionExamples 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'.

           
DescriptionExamples of Supported Syntax
Insert $ before a variable.

Enclose a variable within braces.		columnname=${variable}

Unsupported Variable Syntax

                   
DescriptionExamples 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.

                         
DescriptionExamples 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

                           
DescriptionExamples 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.

                                                 
DescriptionExamples 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:
  • alphanumeric characters
  • : (colon)
  • _ (underscore)
  • . (period)

The following syntax does not work:

select url,size
  device spec: device-specifications
  where url = http://1.1.1.1//tsweb/images
   /clear.gif

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

                 
DescriptionExamples 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.

                   
DescriptionExamples of Supported Syntax

Use this syntax to perform ascending (asc) and descending (desc) sorts with order by.
  • order by size asc
  • order by msg desc
  • order by size asc, msg desc
  • order by count(size) asc

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

               
DescriptionExamples 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.
  • group by size
  • group by msg

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

                                                 
OperatorSyntax
= (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'
INcolumn1 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')
likecolumn1 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 QueryReason
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) ascWhen 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) descYou 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.
You are here: Working with Reporting Rules > Rule Overview > IPDB Rule Syntax

Attachments

    Outcomes

      Visibility: RSA Security Analytics 10.6.11072 Views
      Last modified on Jul 28, 2016 6:30 PM
      Ratings: