Reporting: IMDB Rule Syntax

Document created by RSA Information Design and Development on Mar 23, 2017
Version 1Show Document
  • View in full screen mode
  

This topic describes the supported rule syntax for the IMDB 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 IMDB 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 IMDB 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
EPOCH syntax.
alert.timestamp >= 1475658011
All string values must have single quote. In case of integer type, do not use quotes

Use the following operators to ensure accurate processing:
  • =  (equals)
  • !=  (does not equal)
  • exists (exists)

  • !exists(not exists)

  1. select alert.name where alert.numEvents = 4
  2. select priority where status = 'NEW'

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 IMDB data source.select *
Select specific columns from an IMDB Data Source (You must separate each column with a comma.). select column1,column2,column3,...,columnN
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.Categories
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

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
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
All timestamp values are supported.

alert.timestamp >= 1475658011 or alert.timestamp <= 1475658011

 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')
In where clause, adding lists is not supported.

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 alert.name asc
  • order by incidentCreated desc
  • order by count(numEvents)
  • order by status

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

Note: For all queries, it is mandatory for you to select the order by field.

Supported group by Clause Syntax

               
DescriptionExamples of Supported Syntax
Security Analytics picks the metas for Group By field from the selected Select clause automatically but outside any function.

group by : alert.severity, alert.timestamp, incidentCreated

Note: Group by field is enabled for Aggregate queries and are not editable.

Supported Aggregate Functions

The IMDB 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'
beginscolumn1 <= 'value'
containscolumn1 >= 'value'
endscolumn1 < 'value'
existscolumn1 > 'value'
!exists (does not exist)column1 NOT ('value1','value2',...,'valueN')
lengthcolumn1 between 'value1' and 'value2'
regex (regular expression)condition1 and condition2

condition1 or condition2

condition1 not in ('value1','value2',...,'valueN')
  

Sample Supported Queries

select alert.name, alert.numEvents, alert.severity where alert.severity exists

select alert.host_summary, alert.name where alert.host_summary contains '10.30.94.34'

select alert.name, alert.numEvents, count(alert.numEvents)

select alert.severity, avg(alert.severity)

select alert.timestamp, incidentCreated where alert.timestamp >= 1475658011

Sample Unsupported Queries

You cannot use timestamp meta values in where clause other than EPOCH format. For example, where : incidentCreated = "Tue Oct 25 07:15:38 UTC 2016".

You cannot use a nested select (sub-query) to get the msg.idfield on some other condition. For example,

Ex :- select incidentId where incidentid IN (select incidentId from table where alert.source = ‘ESA’)

You cannot enclose columns in parentheses. For example, select (alert.name), (alert.source), incidentId, receivedTime where alert.severity is not null.

Previous Topic:NWDB Rule Syntax
Next Topic:Rule Types
You are here
Table of Contents > Working with Reporting Rules > Rule Overview > IMDB Rule Syntax

Attachments

    Outcomes