SIEM - 7.7.2.5 - Extreme Networks
Extreme Networks Security Ariel Query Language Guide
9034853 Published July 2015
Copyright © 20132015 All rights reserved.
Legal Notice
Extreme Networks, Inc. reserves the right to make changes in specifications and other information contained in this document and its website without prior notice. The reader should in all cases consult representatives of Extreme Networks to determine whether any such changes have been made. The hardware, firmware, software or any specifications described or referred to in this document are subject to change without notice.
Trademarks
Extreme Networks and the Extreme Networks logo are trademarks or registered trademarks of Extreme Networks, Inc. in the United States and/or other countries. All other names (including any product names) mentioned in this document are the property of their respective owners and may be trademarks or registered trademarks of their respective companies/owners. For additional information on Extreme Networks trademarks, please see: www.extremenetworks.com/company/legal/trademarks/
Support
For product support, including documentation, visit: www.extremenetworks.com/ documentation/
For information, contact: Extreme Networks, Inc. 145 Rio Robles San Jose, California 95134 USA
Table of Contents
About this guide.........................................................................................................................4 Conventions.............................................................................................................................................................................4 Providing Feedback to Us................................................................................................................................................ 5 Getting Help............................................................................................................................................................................ 6 Related Publications............................................................................................................................................................6
Chapter 1: Ariel Query Language (AQL).................................................................................8 Ariel Query Language (AQL) deprecated versions.............................................................................................8 AQL functions.........................................................................................................................................................................11 Logical and comparative operators........................................................................................................................... 15 Event, flow and simarc fields for AQL queries......................................................................................................18 SELECT statement.............................................................................................................................................................22 WHERE clause..................................................................................................................................................................... 23 GROUP BY clause.............................................................................................................................................................. 24 ORDER BY clause.............................................................................................................................................................. 26 LIKE clause.............................................................................................................................................................................27 COUNT function..................................................................................................................................................................28
Index.......................................................................................................................................... 29
Extreme Networks Security Ariel Query Language Guide
3
About this guide
The Ariel Query Language (AQL) Guide provides you with information for using the AQL advanced searching and API.
Intended audience
System administrators who view event or flow data stored in the Ariel database.
Statement of good security practices
IT system security involves protecting systems and information through prevention, detection and response to improper access from within and outside your enterprise. Improper access can result in information being altered, destroyed, misappropriated or misused or can result in damage to or misuse of your systems, including for use in attacks on others. No IT system or product should be considered completely secure and no single product, service or security measure can be completely effective in preventing improper use or access. Extreme Networks® systems, products and services are designed to be part of a lawful comprehensive security approach, which will necessarily involve additional operational procedures, and may require other systems, products or services to be most effective. EXTREME NETWORKS DOES NOT WARRANT THAT ANY SYSTEMS, PRODUCTS OR SERVICES ARE IMMUNE FROM, OR WILL MAKE YOUR ENTERPRISE IMMUNE FROM, THE MALICIOUS OR ILLEGAL CONDUCT OF ANY PARTY.
Note Use of this Program may implicate various laws or regulations, including those related to privacy, data protection, employment, and electronic communications and storage. Extreme Networks Security Analytics may be used only for lawful purposes and in a lawful manner. Customer agrees to use this Program pursuant to, and assumes all responsibility for complying with, applicable laws, regulations and policies. Licensee represents that it will obtain or has obtained any consents, permissions, or licenses required to enable its lawful use of Extreme Networks Security Analytics.
Conventions
This section discusses the conventions used in this guide.
Text Conventions
The following tables list text conventions that are used throughout this guide.
Extreme Networks Security Ariel Query Language Guide
4
About this guide
Table 1: Notice Icons
Icon
Notice Type
Note
Alerts you to... Important features or instructions.
Caution
Risk of personal injury, system damage, or loss of data.
Warning
Risk of severe personal injury.
New
This command or section is new for this release.
Table 2: Text Conventions
Convention
Description
This typeface indicates command syntax, or represents information as it appears on Screen displays the screen.
The words enter and type
When you see the word "enter" in this guide, you must type something, and then press the Return or Enter key. Do not press the Return or Enter key when an instruction simply says "type."
[Key] names
Key names are written with brackets, such as [Return] or [Esc]. If you must press two or more keys simultaneously, the key names are linked with a plus sign (+). Example: Press [Ctrl]+[Alt]+[Del]
Words in italicized type Italics emphasize a point or denote new terms at the place where they are defined in the text. Italics are also used when referring to publication titles.
Terminology
When features, functionality, or operation is specific to a switch family, the family name is used. Explanations about features and operations that are the same across all product families simply refer to the product as the "switch."
Providing Feedback to Us
We are always striving to improve our documentation and help you work better, so we want to hear from you! We welcome all feedback but especially want to know about:
· Content errors or confusing or conflicting information. · Ideas for improvements to our documentation so you can find the information you need faster. · Broken links or usability issues.
If you would like to provide feedback to the Extreme Networks Information Development team about this document, please contact us using our short online feedback form. You can also email us directly at InternalInfoDev@extremenetworks.com.
Extreme Networks Security Ariel Query Language Guide
5
About this guide
Getting Help
If you require assistance, contact Extreme Networks Global Technical Assistance Center using one of the following methods:
Web Phone
Email
www.extremenetworks.com/support
1-800-872-8440 (toll-free in U.S. and Canada) or 1-603-952-5000 For the Extreme Networks support phone number in your country: www.extremenetworks.com/support/contact
support@extremenetworks.com To expedite your message, enter the product name or model number in the subject line.
Before contacting Extreme Networks for technical support, have the following information ready:
· Your Extreme Networks service contract number · A description of the failure · A description of any action(s) already taken to resolve the problem (for example, changing mode
switches or rebooting the unit)
· The serial and revision numbers of all involved Extreme Networks products in the network · A description of your network environment (such as layout, cable type, other relevant
environmental information)
· Network load and frame size at the time of trouble (if known) · The device history (for example, if you have returned the device before, or if this is a recurring
problem)
· Any previous Return Material Authorization (RMA) numbers
Related Publications
The Extreme Security product documentation listed below can be downloaded from http:// documentation.extremenetworks.com.
Extreme Security Analytics Threat Protection · Extreme Networks Security API Reference Guide · Extreme Networks Security Application Configuration Guide · Extreme Networks Security Ariel Query Language Guide · Extreme Networks Security DSM Configuration Guide · Extreme Security DSM Configuration Guide Addendum · Extreme Networks Security Hardware Guide · Extreme Networks Security Installation Guide · Extreme Networks Security Juniper NSM Plug-in User Guide · Extreme Networks Security Log Manager Administration Guide · Extreme Networks Security Log Sources User Guide · Extreme Networks Security Managing Log Sources Guide · Extreme Networks Security Offboard Storage Guide · Extreme Security Release Notes
Extreme Networks Security Ariel Query Language Guide
6
About this guide
· Extreme Networks Security Risk Manager Adapter Configuration Guide · Extreme Networks Security Risk Manager Getting Started Guide · Extreme Networks Security Risk Manager Installation Guide · Extreme Networks Security Risk Manager Migration Guide · Extreme Networks Security Risk Manager User Guide · Extreme Networks Security Troubleshooting System Notifications Guide · Extreme Networks Security Upgrade Guide · Extreme Networks Security Vulnerability Manager Release Notes · Extreme Networks Security Vulnerability Manager User Guide · Extreme Networks Security WinCollect User Guide · Extreme Networks SIEM Administration Guide · Extreme Networks SIEM Getting Started Guide · Extreme Networks SIEM High Availability Guide · Extreme Networks SIEM Troubleshooting Guide · Extreme Networks SIEM Tuning Guide · Extreme Networks SIEM Users Guide · Migrating Extreme Security Log Manager to Extreme SIEM Extreme Security Threat Protection · Extreme Security Intrusion Prevention System Hardware Replacement Guide · Extreme Security Threat Protection Release Notes
Extreme Networks Security Ariel Query Language Guide
7
1 Ariel Query Language (AQL)
Ariel Query Language (AQL) deprecated versions AQL functions Logical and comparative operators Event, flow and simarc fields for AQL queries SELECT statement WHERE clause GROUP BY clause ORDER BY clause LIKE clause COUNT function
The Ariel Query Language (AQL) is a structured query language that you use to communicate with the Ariel databases. Use AQL to manage event and flow data from the Ariel database.
Ariel Query Language (AQL) deprecated versions
Ariel Query Language (AQL) v1 and v2 are deprecated.
The command-line script, /opt/qradar/bin/arielClient is deprecated. The following warning message is displayed both before and after the results are returned: WARNING: AQL v1 and v2 will be deprecated in the future. For information about using AQL v3, see the product documentation.
During your migration to v3, you can suppress the warning message by typing: /opt/qradar/bin/ arielClient | grep -v WARNING
The Python client and the Advanced search option use AQL v3.
AQL fields changed in AQL V3
Ariel Query Language (AQL) V2 is deprecated in Extreme Security V7.2.4 and later. Some Ariel database fields were changed or removed in AQL V3. If you have queries that use these fields, you must replace them.
This table shows the new Ariel database fields.
Extreme Networks Security Ariel Query Language Guide
8
Ariel Query Language (AQL)
Table 3: Fields that were replaced in AQL V3
Field name (AQL V2)
Replacement function name (AQL V3)
destinationAssetName
AssetHostname
deviceGroup
LogSourceGroupName
sourceAssetName
AssetHostname
eventDescription
QidName
destinationNetwork
NetworkName
endDate
DateFormat
endDateFormatted
DateFormat
eventProcessor
Processorname
identityUsername
AssetUser
identityMAC
AssetProperty
identityHostName
AssetHostname
identityNetBiosName
AssetHostname
identityGroupName
AssetProperty
identityExtendedField
AssetProperty
deviceDate
DateFormat
payloadHex
UTF8
protocol
ProtocolName
sourceNetwork
NetworkName
startDate
DateFormat
startDateFormatted
DateFormat
destinationAssetName
AssetHostname
sourceAssetName
AssetHostname
destinationNetwork
NetworkName
sourceNetwork
NetworkName
application
ApplicationName
destinationPayloadHex
UTF8
firstPacketDate
DateFormat
eventProcessorId
ProcessorName
This lists shows the Ariel database fields that were removed.
· partialorMatchList · qidNumber · token · destinationHost
Extreme Networks Security Ariel Query Language Guide
9
· destinationIPSearch · destinationPortNA · sourceHost · sourceIPSearch · sourcePortNA · destinationDscpOnly · anyDestinationFlag · smallDestinationPayload · smallDestinationPayloadHex · destinationPrecedanceOnly · lastPacketDate · localHost · remoteHost · sourceDscpOnly · anySourceFlag · sourcePayloadHex · smallSourcePayload · smallSourcePayloadHex · sourcePrecedanceOnly · sourceHostString · destinationHostString · destinationNetwork · application · sourceNetwork · smallPayload · smallPayloadHex · quickSearchMatches · bitsPerSecond · srcBitsPerSecond · dstBitsPerSecond · bytesPerSecond · bytesPerPacket · srcBytesPerPacket · dstBytesPerPacket · destinationByteRatio · destinationPacketRatio · packetsPerSecond · sourceByteRatio · sourcePacketRatio · totalBytes · totalPackets · retentionBucket
Extreme Networks Security Ariel Query Language Guide
Ariel Query Language (AQL) 10
Ariel Query Language (AQL)
· properLastPacketTime · properLastPacketDate
AQL functions
Use Ariel Query Language (AQL) built-in functions to do calculations on data in the Ariel database.
Table 4: Basic functions
Operator
Description
Example
STR
Converts any parameter to a string.
STR(sourceIP)
STRLEN
Returns the length of this string.
STRLEN.(userName)
SUBSTRING
Copies a range of characters into a new string. SUBSTRING(userName, 0, 3)
CONCAT
Concatenates all passed strings into 1 string.
CONCAT(userName, STR(sourceIP))
PARSEDATETIME Returns the current time, which is expressed as milliseconds since the time 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970.
PARSEDATETIME('1 week ago')
DATEFORMAT
Formats a time, which is expressed as milliseconds since the time 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970 to a user-readable form.
DATEFORMAT(startTime, 'YYYY-MM-DD HH:mm:ss') as StartTime
NOW
Returns the current time that is expressed as NOW()
milliseconds since the time 00:00:00
Coordinated Universal Time (UTC) on January 1,
1970.
UTF8
Returns the UTF8 string of a byte array.
UTF8(payload)
Table 5: Aggregate functions Operator GROUP BY
COUNT UNIQUECOUNT
FIRST SUM AVG
Information
Example
Creates an aggregate on one or more columns.
SELECT sourceIP, COUNT(*) from events group by sourceIP, destinationIP
Returns the count of the rows in the aggregate.
SELECT sourceIP, COUNT(*) from events group by sourceIP
Returns the unique count of the value in the aggregate.
SELECT sourceIP, UNIQUECOUNT (category) from events group by sourceIP
Returns the first entry of the rows in the aggregate.
SELECT sourceIP, FIRST(magnitude) from events group by sourceIP
Returns the sum of the rows in the aggregate.
SELECT sourceIP, SUM(sourceBytes) from flows group by sourceIP
Returns the average value of the rows in the aggregate. SELECT sourceIP, AVG(magnitude) from events group by sourceIP
Extreme Networks Security Ariel Query Language Guide
11
Ariel Query Language (AQL)
Table 5: Aggregate functions (continued)
Operator
Information
Example
MIN
Returns the minimum value of the rows in the aggregate. SELECT sourceIP, MIN(magnitude)
from events group by sourceIP
MAX
Returns the maximum value of the rows in the
SELECT sourceIP, MAX(magnitude)
aggregate.
from events group by sourceIP
HAVING
Allows operators on the result of a grouped by column.
SELECT sourceIP, MAX(magnitude) as MAG from events group by sourceIP HAVING MAG > 5
Table 6: External functions
Name
Description
Argument type
HostName
Looks up a log source ID NUMERIC or a flow source ID.
AssetHostname Looks up a host name of VARCHAR an asset at a point in time. DOUBLE
AssetProperty Looks up a property for an asset at the current time.
VARCHAR OTHER DOUBLE
AssetUser
Looks up a user for an asset at a point in time.
VARCHAR DOUBLE
MatchesAsset Search
If the asset is contained in VARCHAR
the results of the asset saved search it returns
VARCHAR
true.
ReferenceMap Looks up the value for a JAVA_OBJECT key in a reference map. JAVA_OBJECT
ReferenceTabl e
Looks up the value for a VARCHAR column key in a table that JAVA_OBJECT is identified by a table key JAVA_OBJECT in a specific reference table collection.
Description Log source ID or the flow source ID.
IP address, Time stamp Optional: If not specified, uses NOW() IP address, Property name, Time stamp Optional: If not specified, uses NOW()
IP address, Timestamp Optional: If not specified, uses NOW() IP address, Saved Search Name
String, String
Example ReferenceMap ('IPLookup', 'userName')
String, String, String (or IP address)
Example ReferenceTable ('testTable', 'numKey', '100.10.10.1') or ReferenceTable ('testTable', 'numKey', sourceIP)
Extreme Networks Security Ariel Query Language Guide
12
Ariel Query Language (AQL)
Table 6: External functions (continued)
Name
Description
Argument type
Reference MapSet Contains
If a value is contained in a VARCHAR
reference set that is
JAVA_OBJECT
identified by a key in a JAVA_OBJECT
specific reference map of
set it returns true.
Description
String, String, String
Example ReferenceMap SetContains( 'RiskyUs ersForIps', 'sourceIP', 'userName')
ReferenceSet Contains
If a value is contained in a VARCHAR specific reference set, it JAVA_OBJECT returns true.
String, String
Example ReferenceSetContains ('MySet', 'SourceIP')
CategoryName
LogSource Group Name
Looks up the name of a category by its ID.
Looks up the name of a log source group by its log source group ID.
NUMERIC NUMERIC
Category ID
Device group list Example LogSourceGroupName(de viceGroupList)
QidDescriptio n QidName
Application Name LogSource Name
Looks up the description of a QID by its QID.
Looks up the name of a QID by its QID.
Returns the name of a flow application.
Looks up the name of a log source by its log source ID.
NUMERIC NUMERIC NUMERIC NUMERIC
QID
QID
Application ID
Log source ID Example LogSourceName(logSour ceId)
LogSource Type Name
Looks up the name of a log source type by its device type.
Types .NUMERIC Device type
Example LogSourceTypeName(dev iceType)
UTF-8
Returns the UTF-8string. VARBINARY
A byte array
Example Payload
StrLen
Returns the length of this VARCHAR string.
String
Extreme Networks Security Ariel Query Language Guide
13
Ariel Query Language (AQL)
Table 6: External functions (continued)
Name
Description
Argument type
Description
Str
Converts parameter to JAVA_OBJECT
String
string.
SubString
Copies a range of characters into a new string.
VARCHAR
A String, a start that is offset, and a
NUMERICNUMERIC length
Concat
Concatenates all passed VARCHARNUMERIC List of strings
strings into 1 string.
NUMERIC
ParseDate time
Returns the current time, which is expressed as milliseconds since the time 00:00:00 Coordinated Universal Time (UTC) on January 1, 2014.
VARCHAR
A String that represents a date and time
Now
Returns the current time, NULL
None
which is expressed as
milliseconds since the
time 00:00:00
Coordinated Universal
Time (UTC) on January 1,
2014.
ProtocolName
Returns the name of a NUMERIC protocol, which is based on a protocol ID number.
Protocol ID number
InOffense
If an event or flow
NUMERIC
belongs to the specified
offense, it returns true.
Offense ID Example
InCIDR NetworkName
SELECT * FROM events WHERE InOffense(123)SELECT * FROM flows WHERE InOffense(123)
If the IP/column, specified VARCHAR, OTHER IP/CIDR, IP address
is contained in, or equal to, the specified IP/CIDR,
Example
it returns true.
...WHERE
InCIDR('172.16.0.0/16',
sourceip) AND ...
Looks up the network name from the network
hierarchy for the Host that is passed in.
OTHER
Host property Example
NetworkName(sourceip)
Extreme Networks Security Ariel Query Language Guide
14
Ariel Query Language (AQL)
Table 6: External functions (continued)
Name
Description
Argument type
RuleName
Returns one or more rule names that are based on the rule ID or IDs that are passed in.
INTEGER
Long Double DomainName
Parses a string that represents a number into a Long (integer) data type.
VARCHAR
Parses a string that represents a number into a Double (integer) data type.
VARCHAR
Looks up the domain name based on domain ID.
NUMERIC
Description A single rule ID, or a list of rule IDs.
Example
RuleName(creEventList), RuleName(1033) A string that represents a number.
Example
Long('1234') A string that represents a number.
Example
Double('1234') domain ID
Example DomainName(domainID)
Logical and comparative operators
Logical operators are used in AQL statements to determine any equality or difference between values. By using logical operators in the WHERE clause of an AQL statement, the results returned are restricted/filtered to those that match the conditions in the WHERE clause. The following table lists the supported operators.
Table 7: Operators for the Ariel API
Operator
Information
Example
=
Compares 2 values and returns true if they ...WHERE sourceIP =
are equal.
destinationIP
!=
Compares 2 values and returns true if they ...WHERE sourceIP !=
are not equal.
desintationIP)
( and )
Use brackets to nest components of a WHERE or HAVING clause to create complex Boolean expressions.
...WHERE ( sourceIP = destinationIP ) AND ( sourcePort = destinationPort )
< and <=
Compares two values and returns true if the ...WHERE sourceBytes <
left value is less than or, less than or equal 64 and destinationBytes
to, the right value.
<= 64
> and >=
Compares two values and returns true if the ...WHERE sourceBytes >
left value is greater than or, greater than or 64 and destinationBytes
equal to, the right value.
>= 64
Extreme Networks Security Ariel Query Language Guide
15
Ariel Query Language (AQL)
Table 7: Operators for the Ariel API (continued)
Operator
Information
Example
*
Multiplies 2 values and returns the result. ...WHERE sourceBytes *
1024 < 1
/
Divides 2 values and returns the result.
...WHERE sourceBytes /
8 > 64
+
Adds 2 values and returns the result.
...WHERE sourceBytes +
destinationBytes < 64
-
Subtracts 1 value from another and returns ...WHERE sourceBytes -
the result.
destinationBytes > 0
^
Takes a value and raises it to the specified ...WHERE sourceBytes ^
power and returns the result.
2 < 256
%
Takes the modulo of a value and returns the ...WHERE sourceBytes %
result.
8 == 7
AND
Takes the left side of a statement and the ...WHERE ( sourceIP =
right side of a statement and returns true if destinationIP ) AND
both are true.
( sourcePort =
destinationPort )
OR
Takes the left side of a statement and the ...WHERE ( sourceIP =
right side of a statement and returns true if destinationIP ) OR
either one is true.
( sourcePort =
destinationPort )
NOT
Takes in a statement and returns true if the ...WHERE NOT ( sourceIP
statement evaluates to false.
= destinationIP )
IS NULL
Takes in a value and returns true if the value ...WHERE userName IS
is null.
NULL
NOT NULL
Takes in a value and returns true if the value ...WHERE userName IS
is not null.
NOT NULL
BETWEEN (X,Y)
Takes in a left side and two values and returns true if the left side is between the two values.
...WHERE magnitude BETWEEN 1 AND 5
LIMIT
Limits the number of results to the provided ...WHERE magnitude > 5
number.
LIMIT 10
ORDER BY (ASC,DESC)
Orders the result set by the provided columns.
SELECT * FROM EVENTS ORDER BY sourceIP DESC
COLLATE
Parameter to order by that allows a BCP47 SELECT * FROM EVENTS
language tag to collate.
ORDER BY sourceIP DESC
COLLATE 'de-CH'
INTO
Creates a named cursor that contains results that can be queried at a different time.
SELECT * FROM EVENTS INTO 'MyCursor' WHERE....
Extreme Networks Security Ariel Query Language Guide
16
Ariel Query Language (AQL)
Table 7: Operators for the Ariel API (continued)
Operator
Information
Example
START
You can pass a time interval to start selecting data from in the format yyyyMM-dd HH:mm. Use in combination with STOP.
...WHERE userName IS NULL START '2014-01-01 12:00' STOP '2014-02-01 17:00'
STOP
You can pass a time interval to stop selecting data from in the format yyyyMM-dd HH:mm. Use in combination with START.
...WHERE userName IS NULL START '2014-01-01 12:00' STOP '2014-02-01 17:00'
LAST
You can pass a time interval to select data ...WHERE userName IS from. Valid intervals are MINUTES, HOURS, NULL LAST 6 HOURS and DAYS
LIKE
Matches if the string passed, is LIKE the passed value. % is a wildcard.
...WHERE userName LIKE '%bob%'
ILIKE
Matches if the string passed, is LIKE the
...WHERE userName ILIKE
passed value in a case-insensitive manner. % '%bob%'
is a wildcard.
MATCHES
Matches if the string matches the provided ...WHERE userName
regular expression.
MATCHES '^.bob.$'
IMATCHES
Matches if the string matches the provided ...WHERE userName
regular expression in a case-insensitive
IMATCHES '^.bob.$'
manner.
TEXT SEARCH
Full-text search for the passed value. You can also do full-text searches by using the Quick filter in the Extreme Security user interface. For information about Quick filter functions, see the Extreme Networks SIEM Users Guide TEXT SEARCH is valid with AND operators. You can't use TEXT SEARCH with OR or other operators; otherwise you will get a syntax error.
...WHERE TEXT SEARCH 'firewall' AND ...
Examples of logical and comparative operators · To sort events that are unparsed, type the following query: SELECT * FROM events WHERE
payload = 'false'
· To sort flows to find a specific source IP address that has an offense, type the following query:
SELECT * FROM events WHERE sourceIP = '231.12.37.17' AND hasOffense = 'true'
· You can do a Quick filter search in AQL. To sort events for "firewall", type the following
query:SELECT QIDNAME(qid) AS EventName, * from events where TEXT SEARCH 'firewall'
Extreme Networks Security Ariel Query Language Guide
17
Ariel Query Language (AQL)
Event, flow and simarc fields for AQL queries
Use the Ariel Query Language (AQL) to retrieve specific fields from the events, flows and simarc table in the Ariel database.
Supported flow fields for AQL queries
The flow fields that you can query are listed in the following table.
Table 8: Supported flow fields for AQL queries
Field name
Description
applicationId
Application ID
category
Category
credibility
Credibility
destinationASN
Destination ASN
destinationBytes
Destination bytes
destinationDSCP
Destination DSCP
destinationFlags
Destination flags
destinationIP
Destination IP
destinationIfIndex
Destination if index
destinationPackets
Destination packets
destinationPayload
Destination payload
destinationPort
Destination port
destinationPrecedence
Destination precedence
destinationTOS
Destination QoS
destinationv6
IPv6 destination
processorID
Event processor ID
fullMatchList
Full match list
firstPacketTime
First packet time
flowBias
Flow bias
flowDirection
Flow direction
· local-to-local (L2L) · local-to-remote (L2R) · remote-to-local (R2L) · remote-to-remote (R2R)
flowInterfaceID flowSource flowType geographic hasDestinationPayload
Flow interface ID Flow Source Flow type Matches geographic location Has destination payload
Extreme Networks Security Ariel Query Language Guide
18
Ariel Query Language (AQL)
Table 8: Supported flow fields for AQL queries (continued)
Field name
Description
hasOffense
Has offense payload
hasSourcePayload
Has source payload
icmpCode
Icmp code
icmpType
ICMP type or code
flowInterface
Flow interface
intervalId
Interval ID
isDuplicate
Duplicate event
lastPacketTime
Last packet time
partialMatchList
Partial match list
protocol
Protocol
protocolId
Protocol ID
qid
Qid
relevance
Relevance
retentionBucket
Retention bucket dummy
severity
Severity
sourceASN
Source ASN
sourceBytes
Source bytes
sourceDSCP
Source DSCP
sourceFlags
Source flags
sourceIP
Source IP
sourceIfIndex
Source if index
sourcePackets
Source packets
sourcePayload
Source payload
sourcePort
Source port
sourcePrecedence
Source precedence
sourcev6
IPv6 source
startTime
Start time
viewObjectPair
View object pair
Supported event fields for AQL queries
The event fields that you can query are listed in the following table.
Extreme Networks Security Ariel Query Language Guide
19
Ariel Query Language (AQL)
Table 9: Supported event fields for AQL queries
Field name
Description
category
Low-level category
creEventList
Matched custom rule
credibility
Credibility
destinationMAC
Destination MAC
destinationPort
Destination port
destinationv6
IPv6 destination
deviceTime
Log source time
deviceType
Log source type
domainID
Domain ID
Note Log Manager only
duration endTime eventCount eventDirection
processorId hasIdentity hasOffense highLevelCategory isCREEvent magnitude payload postNatDestinationIP postNatDestinationPort postNatSourceIP postNatSourcePort preNatDestinationIP preNatDestinationPort preNatSourceIP preNatSourcePort
Duration End time Event count Event direction:
· local-to-Local (L2L) · local-to-remote (L2R) · remote-to-local (R2L) · remote-to-remote (R2R)
Event Processor ID Has identity Associated with offense High-level category Is custom rule event Magnitude Payload Destination IP after NAT Destination port after NAT Source IP after NAT Source port after NAT Destination IP before NAT Destination port before NAT Source IP before NAT Source port before NAT
Extreme Networks Security Ariel Query Language Guide
20
Ariel Query Language (AQL)
Table 9: Supported event fields for AQL queries (continued)
Field name
Description
protocolID
Protocol
qid
Event name ID
relevance
Relevance
severity
Severity
sourceIP
Source IP
sourceMAC
Source MAC
sourcePort
Source port
sourcev6
IPv6 source
startTime
Start time
isunparsed
Event is unparsed
userName
User name
Supported simarc fields for AQL queries
The simarc fields that you can query are listed in the following table.
Table 10: Supported simarc fields for AQL queries
Field name
Description
destinationPort
Destination port key creator
destinationType
Destination type key creator
deviceId
Device key creator
direction
Direction key creator
eventCount
Event count key creator
eventFlag
Flag key creator
applicationId
Application ID key creator
flowCount
Flow count key creator
destinationBytes
Destination bytes key creator
flowSource
Flow source key creator
sourceBytes
Source bytes key creator
lastPacketTime
Time key creator
protocolId
Protocol key creator
source
Source key creator
sourceType
Source type key creator
sourceRemoteNetwork
Source remote network key creator
destinationRemoteNetwork
Destination remote network key creator
Extreme Networks Security Ariel Query Language Guide
21
Ariel Query Language (AQL)
Table 10: Supported simarc fields for AQL queries (continued)
Field name
Description
sourceCountry
Source geographic key creator
destinationCountry
Destination geographic key creator
destination
Destination key creator
creEventList
Normalized event properties CRE event list
partialMatchList
Normalized event properties partial match list
SELECT statement
Use the SELECT statement to retrieve specific data from the events or flows table in the Ariel database. A SELECT operation is called a query.
Syntax
SELECT selectList FROM joinClauses [WHERE searchCondition] [GROUP BY groupClause] [ORDER BY orderClause]
Usage
A SELECT statement can include one or more fields from the flow or event tables. Use an asterisk, *, to denote all columns. All field names are case-sensitive. However, SELECT and FROM statements are not case-sensitive.
Overriding the time settings passed to the AQL query
The SELECT statement supports an arieltime option, which overrides the time settings. You can limit the time period for which an AQL query is evaluated. You can use the START and STOP keywords.
Example SELECT sourceIP FROM events START '2014-05-02 09:25' stop '2014-05-02 09:30'
You can also use the LAST keyword.
Extreme Networks Security Ariel Query Language Guide
22
Ariel Query Language (AQL)
Example SELECT * FROM events LAST 15 MINUTES SELECT * FROM events LAST 1 HOURS SLECT * FROM events LAST 2 DAYS
Examples of SELECT statements that use CIDR ranges
You can also use SELECT statements for CIDR-based queries. To query by source IP address, sourceIP, or by destination IP address, destinationIP, use the following format:
SELECT <query item> FROM <flows|events> WHERE <sourceCIDR|destinationCIDR> = `<CIDR Range>'
Example SELECT * FROM flows WHERE sourceCIDR = '10.100.100/24'
To return all flows that are coming from the 10.100.100 subnet or capture flows that are coming from and into the subnet, use the regular OR expression.
Example SELECT * FROM flows WHERE sourceCIDR = '10.100.100/24' OR destinationCIDR = '10.100.100/24'
To query when source IP is contained in the 192.168.222.0/24 range, use the following format: SELECT <query item> FROM <events> WHERE <INCIDR> = `<INCIDR Range>'
Example SELECT * FROM events WHERE INCIDR('192.168.222.0/24, sourceIP)
To query when source IP is not contained in the 192.168.222.0/24 range, use the following format: SELECT <query item> FROM <events> WHERE <INCIDR> != `<INCIDR Range>'
Example SELECT * FROM events WHERE NOT INCIDR('192.168.222.0/24, sourceIP)
WHERE clause
Restrict your AQL queries by using WHERE clauses. The WHERE clause describes the filter criteria to apply to the query and filters the resulting view to accept only those events or flows that meet the specified condition.
Extreme Networks Security Ariel Query Language Guide
23
Ariel Query Language (AQL)
Syntax
WHERE searchCondition
A searchCondition is a combination of logical and comparison operators that together make a test. Only those input rows that pass the test are included in the result.
Examples of WHERE clauses
The following query example shows events that have a severity level of greater than 9 are selected from a category.
SELECT sourceIP, category, credibility FROM events WHERE severity > 9 AND category = 5013
You can change the order of evaluation by using parentheses. The search conditions that are enclosed in parentheses are evaluated first.
SELECT sourceIP, category, credibility FROM events WHERE (severity > 9 AND category = 5013) OR (severity < 5 and credibility > 8)
GROUP BY clause
Use the GROUP BY clause to aggregate your data. To provide meaningful results of the aggregation, usually, data aggregation is combined with arithmetic functions on remaining columns.
Syntax
GROUP BY groupClause
You can use aggregate functions in Ariel Query Language (AQL) queries to summarize information from multiple rows. The aggregate functions that are supported are shown in the following table.
Table 11: Aggregate functions
Function
Description
GROUP BY
Creates an aggregate on one or more columns.
COUNT
Returns the count of the rows in the aggregate.
UNIQUECOUNT Returns the unique count of the value in the aggregate.
FIRST
Returns the first entry of the rows in the aggregate.
SUM
When used with numeric data, returns the sum of the values. When used with categorical data,
it returns the union of the categorical values.
AVG
Returns the average value of the rows in the aggregate.
MIN(expr)
Returns the lowest value of the rows in the aggregate..
Extreme Networks Security Ariel Query Language Guide
24
Ariel Query Language (AQL)
Table 11: Aggregate functions (continued)
Function
Description
MAX(expr)
Returns the highest value of the rows in the aggregate.
HAVING
Allows operators on the result of a grouped by column.
Examples of GROUP BY clauses
The following query example shows IP addresses that sent more than 1 million bytes within all flows in a specific time.
select sourceIP, SUM(sourceBytes) from flows where sourceBytes > 1000000 group by sourceIP
The results might look similar to the following output.
----------------------------------| sourceIP | SUM_sourceBytes | ----------------------------------| 64.124.201.151 | 4282590.0 | | 10.105.2.10 | 4902509.0 | | 10.103.70.243 | 2802715.0 | | 10.103.77.143 | 3313370.0 | | 10.105.32.29 | 2467183.0 | | 10.105.96.148 | 8325356.0 | | 10.103.73.206 | 1629768.0 | -----------------------------------
However, if you compare this information to a non-aggregated query, the output displays all the IP addresses that are unique, as shown in the following output:
-----------------------------| sourceIP | sourceBytes | -----------------------------| 64.124.201.151 | 1448629 | | 10.105.2.10 | 2412426 | | 10.103.70.243 | 1793095 | | 10.103.77.143 | 1449148 | | 10.105.32.29 | 1097523 | | 10.105.96.148 | 4096834 | | 64.124.201.151 | 2833961 | | 10.105.2.10 | 2490083 | | 10.103.73.206 | 1629768 | | 10.103.70.243 | 1009620 | | 10.105.32.29 | 1369660 | | 10.103.77.143 | 1864222 | | 10.105.96.148 | 4228522 | ------------------------------
To view the maximum number of events, use the following syntax:
SELECT MAX(eventCount) FROM events
Extreme Networks Security Ariel Query Language Guide
25
Ariel Query Language (AQL)
To view the number of average events from a source IP, use the following syntax:
SELECT AVG(eventCount) FROM events GROUP BY sourceIP
The output displays the following results:
--------------------------------| sourceIP | protocol | --------------------------------| 64.124.201.151 | TCP.tcp.ip | | 10.105.2.10 | UDP.udp.ip | | 10.103.70.243 | UDP.udp.ip | | 10.103.77.143 | UDP.udp.ip | | 10.105.32.29 | TCP.tcp.ip | | 10.105.96.148 | TCP.tcp.ip | | 64.124.201.151 | TCP.tcp.ip | | 10.105.2.10 | ICMP.icmp.ip | ---------------------------------
ORDER BY clause
Use the ORDER BY clause to sort the resulting view that is based on expression results. The order is sorted by ascending or descending sequence.
Syntax
ORDER BY orderClause
Only one field can be used in the ORDER BY clause. You can switch sorting between ascending or descending by appending the ASC or DESC keyword to the order by clause.
Combining GROUP BY and ORDER BY clauses to create data
To determine the top abnormal events or the most bandwidth-intensive IP addresses, you can combine GROUP BY and ORDER BY clauses in a single query. When you combine the clauses, you create data, such as TopN lists. For example, the following query displays the most traffic intensive IP address in descending order:
SELECT sourceIP, SUM(sourceBytes) FROM flows GROUP sourceIP ORDER BY SUM(sourceBytes) DESC
Examples of ORDER BY clauses
To query AQL to return results in descending order. use the following syntax:
SELECT sourceBytes, sourceIP FROM flows WHERE sourceBytes > 1000000 ORDER BY sourceBytes
Extreme Networks Security Ariel Query Language Guide
26
Ariel Query Language (AQL)
To display results in ascending order, use the following syntax:
SELECT sourceBytes, sourceIP FROM flows WHERE sourceBytes > 1000000 ORDER BY sourceBytes ASC
LIKE clause
Use the LIKE clause to retrieve partial string matches in the Ariel database.
Syntax
ORDER BY orderClause You can search fields by using the LIKE clause. The following wildcard options are supported by the Ariel Query Language (AQL):
Table 12: Supported wildcard options for LIKE clauses
Wildcard character
Description
%
Matches a string of zero or more characters
_
Matches any single character
Examples of LIKE clauses
To match names such as Joe, Joanne, Joseph, or any other name that begins with Jo, type the following query:
SELECT * FROM events WHERE userName LIKE `jo%'
To match names beginning with Jo that are three characters long, such as, Joe or Jon, type the following query:
SELECT * FROM events WHERE userName LIKE `Jo_'
You can enter the wildcard option at any point in the command, as shown in the following examples.
SELECT * FROM flows WHERE sourcePayload LIKE `%xyz' SELECT * FROM events WHERE payload LIKE `%xyz%' SELECT * FROM events WHERE payload LIKE `_yz'
Examples of string matching keywords
The keywords, ILIKE and IMATCHES are case-insensitive versions of LIKE and MATCHES.
SELECT qidname(qid) as test FROM events WHERE test LIKE 'Information%' SELECT qidname(qid) as test FROM events WHERE test ILIKE 'inForMatiOn%'
Extreme Networks Security Ariel Query Language Guide
27
Ariel Query Language (AQL)
SELECT qidname(qid) as test FROM events WHERE test MATCHES '.*Information.*' SELECT qidname(qid) as test FROM events WHERE test IMATCHES '.*Information.*'
COUNT function
The COUNT function returns the number of rows that satisfy the WHERE clause of a SELECT statement. If the SELECT statement does not have a WHERE clause, the COUNT function returns the total number of rows in the table.
Syntax
COUNT
Examples
To count all events with credibility equal to or greater than 9, type the following query, type the following query:
SELECT COUNT() FROM events WHERE credibility >= 9
Extreme Networks Security Ariel Query Language Guide
28
Index
A
AQL8 Ariel Query Language8 arieltime option23, 24
C
command-line options COUNT function28 GROUP BY24, 25 LIKE clause27 ORDER BY clause26 SELECT clause22, 23 WHERE clause23, 24
comparative operators WHERE clause23, 24
conventions, guide notice icons4 text5
COUNT function description28
customer support contact information4
E
events and flows field list18, 19, 21
F
functions fields8 supported list11
G
GROUP BY description24, 25
L
LIKE clause description27
logical operators WHERE clause23, 24
N
network administrator description4
O
ORDER BY clause description26
overriding time settings23, 24
S
SELECT clause description22, 23
T
time settings23, 24
W
WHERE clause description23, 24
Extreme Networks Security Ariel Query Language Guide
29
AH XSL Formatter V6.0 MR4a for Linux64 : 6.0.5.6999 (2012/08/28 12:05JST) Antenna House PDF Output Library 6.0.283 (Linux64)