AQL data retrieval functions

Use the Ariel Query Language (AQL) built-in functions to retrieve data by using data query functions and field ID properties from the Ariel database.

Use the following AQL functions to extract data from the Ariel databases:

Data retrieval functions


APPLICATIONNAME

Purpose

Returns flow application names by application ID

Parameters

Application ID

Example 
SELECT APPLICATIONNAME(applicationid) 
AS 'Name of App' 
FROM flows

Returns the names of applications from the flows database. These application names are listed in the Name of App column, which is an alias.


ARIELSERVERS4EPID

Purpose

Use the ARIELSERVERS4EPID function to specify the Event Processor ID when you use it with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS.

Parameters
ARIELSERVERS4EPID(processor_ID)
The following examples show how to use the ARIELSERVERS4EPID function with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS:
     PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPID(processor_ID) 
     PARAMETERS REMOTESERVERS=ARIELSERVERS4EPID(processor_ID)
Examples

In the following example, only the search results from ARIELSERVERS4EPID(8) are included in the output. If the processor ID that you specify as a parameter for the ARIELSERVERS4EPID function is not in your QRadar deployment, then the query does not run.

 SELECT ARIELSERVERS4EPID(8), ARIELSERVERS4EPID(11), processorid, 
 PROCESSORNAME(processorid), 
 LOGSOURCENAME(logsourceid) from events 
 GROUP BY logsourceid 
 LAST 20 MINUTES 
 PARAMETERS REMOTESERVERS=ARIELSERVERS4EPID(8)
You can also use the ARIELSERVERS4EPID function to returns the Ariel servers that are connected to a specific Event Processor that is identified by ID, as shown in the following example:
 SELECT processorid, PROCESSORNAME(processorid), 
 ARIELSERVERS4EPID(processorid) 
 FROM events GROUP BY processorid

ARIELSERVERS4EPNAME

Purpose

You use the ARIELSERVERS4EPNAME function to specify the Event Processor name when you use it with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS.

Parameters
ARIELSERVERS4EPNAME('eventprocessor_name')
The following examples show how you use ARIELSERVERS4EPNAME PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS:
PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPNAME ('eventprocessor104')
PARAMETERS REMOTESERVERS=ARIELSERVERS4EPNAME ('eventprocessor255')
Examples

In the following example, records from servers that are associated with eventprocessor104 are excluded from the search.

SELECT processorid,PROCESSORNAME(processorid), 
LOGSOURCENAME(logsourceid) 
FROM events 
GROUP BY logsourceid 
PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPNAME ('eventprocessor104')

You can also use the function to return Ariel servers that are associated with an Event Processor that is identified by name.

SELECT PROCESSORNAME(processorid), 
ARIELSERVERS4EPNAME(PROCESSORNAME(processorid)) 
FROM events GROUP BY processorid

Returns Ariel servers for the named Event Processor.


ASSETHOSTNAME

Purpose

Searches for the host name of an asset at a point in time.

The domain can optionally be specified to target an asset on a particular domain. 
ASSETHOSTNAME(sourceip)
ASSETHOSTNAME(sourceip, NOW())
ASSETHOSTNAME(sourceip, domainid)
Parameters

IP address, (timestamp and domain ID are optional)

If the time stamp is not specified, the current time is used.

Examples
SELECT ASSETHOSTNAME(destinationip, NOW()) 
AS 'Host Name' 
FROM events
SELECT ASSETHOSTNAME(sourceip, NOW()) 
AS 'Host Name' 
FROM events

Returns the host name of the asset at the time of the query.


ASSETPROPERTY

Purpose

Looks up a property for an asset.

The domain can optionally be specified to target an asset on a particular domain. 
ASSETPROPERTY
('Unified Name', sourceIP, domainId)
Parameters

Property name, IP address

Domain ID is optional

Example 
SELECT
ASSETPROPERTY('Location',sourceip) 
AS Asset_location,
COUNT(*) 
AS 'event count'
FROM events
GROUP BY Asset_location
LAST 1 days

Returns the asset location that is affiliated with the source IP address.


ASSETUSER

Purpose

Searches for the user of an asset at a point in time.

Domain can optionally be specified to target an asset in a specific domain.
ASSETUSER(sourceIP,NOW(), domainId)
Parameters
IP address, (timestamp and domain ID are optional)
If the time stamp is not specified, the current time is used.
Example 
SELECT 
ASSETUSER(sourceip, now()) 
AS 'Username of Asset' 
FROM events

Returns the user name that is affiliated with the source IP address.


CATEGORYNAME

Purpose

Searches for the name of a category by the category ID.

CATEGORYNAME(Category)
Parameters

Category

Example 
SELECT sourceip, category, 
CATEGORYNAME(category) 
AS 'Category name' 
FROM events

Returns the source IP, category ID, and category name


COMPONENTID

Purpose

Retrieves the ID for a component with a given name.

For example, ARIELSERVERS4EPNAME() is a shortcut for the ARIELSERVERS4EPID(COMPONENTID(<event_processor_name>)) function.

Parameters
COMPONENTID(<component_name>))
Example 
SELECT  * from events where  processorid = COMPONENTID('eventprocessor0')

Retrieves events for the named Event Processor.


DOMAINNAME

Purpose

Searches for the domain name by the domain ID.

DOMAINNAME(domainID)
Parameters

Domain ID

Example 
SELECT sourceip, username,
DOMAINNAME(domainid) 
AS 'Domain name' 
FROM events

Returns source IP, user name, and domain names from events database


GLOBALVIEW

Purpose

Returns the GLOBALVIEW database results for a given saved search name based on the time range that is input.

This query can be run only by using API.

For more information about accessing a GLOBALVIEW database, see the IBM® Security QRadar® Administration Guide.

Parameters

Saved search, time range (DAILY, NORMAL, HOURLY)

Example 
SELECT * 
FROM GLOBALVIEW
('Top Log Sources','DAILY')
LAST 2 days


GEO::LOOKUP

Purpose

Returns location data, provided by MaxMind, for a selected IP address.

Parameters

IP address (required)

Strings (at least one required):

city, continent, physical_country, registered_country, represented_country, location, postal, subdivisions, traits, geo_json

Example 
SELECT sourceip, GEO::LOOKUP(sourceip, 'city') 
AS GEO_CITY 
FROM events last 10 minutes

GEO::DISTANCE

Purpose

Returns the distance, in kilometers, of two IP addresses.

Parameters

IP address (two required)

Example 
SELECT GEO::DISTANCE(sourceip, destinationip) 
AS GEO_DISTANCE 
FROM events last 10 minutes

HOSTNAME

Purpose

Returns the host name of an event processor with a certain processorID.

HOSTNAME(processorId)
Parameters

Processor ID

Example
SELECT HOSTNAME(processorId) FROM events

INCIDR

Purpose

Filters the output of the SELECT statement by referencing the source/destination CIDR IP address that is specified by INCIDR.

Parameters

IP/CIDR, IP address

Example 
SELECT sourceip, username 
FROM events 
WHERE INCIDR('172.16.0.0/16', sourceip)

Returns the source IP and user name columns from the flows database where the source CIDR IP address is from the 172.16.0.0/16 subnet.

See more examples


INOFFENSE

Purpose

If an event or flow belongs to the specified offense, it returns true.

Parameters

Offense ID

Example 
SELECT * FROM events
 WHERE InOffense(123)
SELECT * FROM flows
 WHERE InOffense(123)

LOGSOURCENAME

Purpose

Looks up the name of a log source by its log source ID. 

LOGSOURCENAME(logsourceid)
Parameters

Log source ID

Example 
SELECT * FROM events 
WHERE LOGSOURCENAME(logsourceid) 
ILIKE '%mylogsourcename%'

Returns only results that include mylogsourcename in their log source name.


SELECT LOGSOURCENAME(logsourceid) 
AS Log_Source 
FROM events

Returns the column alias Log_source, which shows log source names from the events database.


LOGSOURCEGROUPNAME

Purpose

Searches for the name of a log source group by its log source group ID.

LOGSOURCEGROUPNAME(deviceGroupList)

Parameters

Device group list

Example 
SELECT sourceip, logsourceid 
FROM events 
WHERE LOGSOURCEGROUPNAME(devicegrouplist) 
ILIKE '%other%'

Returns the source IP address and log source IDs for log source groups that have 'other' in their name.


LOGSOURCETYPENAME

Purpose

Searches for the name of a log source type by its device type.

LOGSOURCETYPENAME(deviceType)

Parameters

Device type

Example 
SELECT LOGSOURCETYPENAME(devicetype) 
AS 'Device names', COUNT(*) 
FROM events 
GROUP BY "Device names" 
LAST 1 DAYS

Returns device names and the event count.

All log sources functions example: 
SELECT logsourceid, 
LOGSOURCENAME(logsourceid) 
AS 'Name of log source', 
LOGSOURCEGROUPNAME(devicegrouplist) 
AS 'Group Names', 
LOGSOURCETYPENAME(devicetype) 
AS 'Devices' 
FROM events 
GROUP BY logsourceid

Returns log source names, log source group names, and log source device names.

When you use the GROUP BY function, the first item only in the GROUP BY list is shown in the results.


MATCHESASSETSEARCH

Purpose
If the asset is returned in the results of the saved search, it returns true. 
MATCHESASSETSEARCH
('My Saved Search', sourceIP)
Parameters

Saved Search Name, IP address

Example 
MATCHESASSETSEARCH
('My Saved Search', sourceIP)

NETWORKNAME

Purpose
Searches for the network name from the network hierarchy for the host that is passed in. 
NetworkName(sourceip)
The domain can optionally be specified to target a network in a particular domain. 
NETWORKNAME(sourceip, domainId)
Parameters

Host property (domain is optional)

Examples
SELECT NETWORKNAME(sourceip) 
ILIKE 'servers' 
AS 'My Networks' 
FROM flows

Returns any networks that have the name servers.


SELECT NETWORKNAME(sourceip, domainID) 
ILIKE 'servers' 
AS 'My Networks' 
FROM flows

Returns any networks that have the name servers in a specific domain.


SELECT NETWORKNAME(sourceip) 
AS 'Src Net',
NETWORKNAME(destinationip) 
AS Dest_net
FROM events

Returns the network name that is associated with the source and destination IP addresses.


OFFENSE_TIME

New in 7.4.3 Fix Pack 1

Purpose

Limits the query to applicable times that an offense could be active.

This function increases the speed of the query.

Parameters

Offense ID

Example 
SELECT * FROM events
 WHERE INOFFENSE(1) times OFFENSE_TIME(1)

PARAMETERS EXCLUDESERVERS

Purpose
Filters search criteria by excluding the specified servers.
Parameters

[Server IP address:Port number]

Use port 32006 for an Event Processor, and port 32011 for a Console.

Parameters accept a comma-separated list of arguments. For example,

"host1:port1,host2:port2,host3:port3".

Examples

In the following example, search results from 192.0.2.0 are excluded. To exclude a Console, you must use localhost or 127.0.0.1. Do not use the IP address of the Console in this query. 

SELECT processorid,PROCESSORNAME(processorid),
LOGSOURCENAME(logsourceid) 
from events 
GROUP BY logsourceid 
PARAMETERS EXCLUDESERVERS='192.0.2.0:32006'

In the following example, search results from the Console are excluded:

SELECT processorid,PROCESSORNAME(processorid),
LOGSOURCENAME(logsourceid) FROM events 
GROUP BY logsourceid start '2017-03-15 10:26' 
STOP '2017-03-15 10:30' 
PARAMETERS EXCLUDESERVERS='127.0.0.1:32011'

In the following example, search results from the Console are excluded. The Console is referred to as localhost in this example.

SELECT processorid,PROCESSORNAME(processorid),
LOGSOURCENAME(logsourceid) from events 
GROUP BY logsourceid start '2017-03-15 10:25' 
STOP '2017-03-15 10:30' 
PARAMETERS EXCLUDESERVERS='localhost:32011'

The following example uses multiple arguments to exclude search results from the Console and two other servers.

SELECT processorid,PROCESSORNAME(processorid),
LOGSOURCENAME(logsourceid) from events 
GROUP BY logsourceid start '2017-04-15 10:25' 
STOP '2017-04-15 10:30' 
PARAMETERS EXCLUDESERVERS='127.0.0.1:32011,192.0.2.0:32006,172.11.22.31:32006'

Specify the ID of the Event Processor in your query by using the following function:

PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPID(processor_ID)

Refine your query by using ARIELSERVERS4EPID with PARAMETERS EXCLUDESERVERS to specify the Event Processor ID that you want to exclude from your search. You can specify one or more Event Processor IDs.

Example

In the following example, all results from ARIELSERVERS4EPID(8) are excluded in the search.

SELECT processorid, 
PROCESSORNAME(processorid), 
LOGSOURCENAME(logsourceid) from events 
GROUP BY logsourceid 
LAST 20 MINUTES 
PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPID(8)

Specify the name of the Event Processor in your query by using the following function:

PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPNAME ('processor_name')

Refine your query by using ARIELSERVERS4EPNAME with PARAMETERS EXCLUDESERVERS to specify the Event Processor by name. You can specify one or more Event Processor names.

Example

In the following example, records from servers that are associated with eventprocessor104 are excluded from the search.

SELECT processorid,PROCESSORNAME(processorid), 
LOGSOURCENAME(logsourceid) 
FROM events 
GROUP BY logsourceid 
PARAMETERS EXCLUDESERVERS=ARIELSERVERS4EPNAME ('eventprocessor104')

PARAMETERS REMOTESERVERS

Purpose
Use the PARAMETERS REMOTESERVERS function to narrow your search to specific servers, which speeds up your search by not searching all hosts.
Parameters

[Server IP address:Port number]

Use port 32006 for an Event Processor, and port 32011 for a Console.

Use a comma-separated list for multiple arguments, for example,

"host1:port1,host2:port2,host3:port3".

Examples

In the following example, only the specified server is searched.

SELECT * FROM EVENTS START '2016-09-08 16:42' 
STOP '2016-09-08 16:47' 
PARAMETERS REMOTESERVERS='192.0.2.0:32006'

In the following example, multiple servers are specified, which includes search results from the Console and two other servers.

SELECT processorid,PROCESSORNAME(processorid),
LOGSOURCENAME(logsourceid) from events 
GROUP BY logsourceid start '2017-04-15 10:25' 
STOP '2017-04-15 10:30' 
PARAMETERS REMOTESERVERS='127.0.0.1:32011,192.0.2.0:32006,172.11.22.31:32006'

Specify the ID of the Event Processor in your query by using the following function:

PARAMETERS REMOTESERVERS=ARIELSERVERS4EPID(processor_ID)
Refine your query by using ARIELSERVERS4EPID with PARAMETERS REMOTESERVERS to specify the ID of the Event Processor that you want to include in your search. You can specify one or more Event Processor IDs.
Example

In the following example, only the search results from ARIELSERVERS4EPID(8) are included in the output.

SELECT ARIELSERVERS4EPID(8), ARIELSERVERS4EPID(11), processorid, 
PROCESSORNAME(processorid), 
LOGSOURCENAME(logsourceid) from events 
GROUP BY logsourceid 
LAST 20 MINUTES 
PARAMETERS REMOTESERVERS=ARIELSERVERS4EPID(8)
Note: If the processor ID that you specify as a parameter for the ARIELSERVERS4EPID function is not in your QRadar deployment, then the query does not run.

Specify the name of the Event Processor in your query by using the following function:

PARAMETERS REMOTESERVERS=ARIELSERVERS4EPNAME ('eventprocessor_name')
Refine your query by using ARIELSERVERS4EPNAME and PARAMETERS REMOTESERVERS to specify the name of the Event Processor that you want to include in your search. You can specify one or more Event Processor names.
Example

In the following example, only search records that are associated with eventprocessor104 are included in the search results.

SELECT processorid,PROCESSORNAME(processorid), 
LOGSOURCENAME(logsourceid) 
FROM events 
GROUP BY logsourceid 
PARAMETERS REMOTESERVERS=ARIELSERVERS4EPNAME ('eventprocessor104')

PROCESSORNAME

Purpose
Returns the name of a processor by the processor ID.
PROCESSORNAME(processorid)
Parameters

Processor ID number

Example 
SELECT sourceip, PROCESSORNAME(processorid) 
AS 'Processor Name' 
FROM events

Returns the source IP address and processor name from the events database.

Example 
SELECT processorid, PROCESSORNAME(processorid) 
FROM events WHERE processorid=104 
GROUP BY processorid LAST 5 MINUTES

Returns results from the Event Processor that has a processor ID equal to 104.


PROTOCOLNAME

Purpose
Returns the name of a protocol by the protocol ID
Parameters

Protocol ID number

Example 
SELECT sourceip, PROTOCOLNAME(protocolid) 
AS 'Name of protocol' 
FROM events

Returns the source IP address and protocol name from the events database.


QIDNAME

Purpose

Searches for the name of a QID by its QID.

QIDNAME(qid)
Parameters

QID

Example 
SELECT QIDNAME(qid) 
AS 'My Event Names', qid 
FROM events

Returns QID name and QID number.


QIDESCRIPTION

Purpose

Searches for the QID description by its QID. 

QIDDESCRIPTION(qid)
Parameters

QID

Example 
SELECT QIDDESCRIPTION(qid) 
AS 'My_Event_Names', QIDNAME(qid) 
AS 'QID Name' 
FROM events

Returns QID description and QID name.


REFERENCEMAP

Purpose

Searches for the value for a key in a reference map. 

ReferenceMap('Value',Key,domainID)

Although the domainID is optional, in a domain-enabled environment, the search is limited to only shared reference data when the domainID is excluded.

Parameters

String, String, Integer

Example 
SELECT 
REFERENCEMAP('Full_name_lookup', username, 5)
AS Name_of_User
FROM events

Searches for the userName (key) in the Full_name_lookup reference map in the specified domain, and returns the full name (value) for the user name (key).


REFERENCEMAPSETCONTAINS

Purpose

If a value exists for a key in a reference map of sets, for a domain, it returns true.

REFERENCEMAPSETCONTAINS(MAP_SETS_NAME, KEY, VALUE)
Parameters

String, String, String

Example 
ReferenceMapSetContains('RiskyUsersForIps','sourceIP','userName')

REFERENCETABLE

Purpose
Searches for the value of a column key in a table that is identified by a table key in a specific reference table collection. 
REFERENCETABLE
('testTable','value','key', domainID) 
or 
REFERENCETABLE
('testTable','value','key' domainID)

Although the domainID is optional, in a domain-enabled environment, the search is limited to only shared reference data when the domainID is excluded.

Parameters
String, String, String (or IP address), Integer
Example 
SELECT 
REFERENCETABLE('user_data','FullName',username, 5) 
AS 'Full Name',
REFERENCETABLE('user_data','Location',username, 5) 
AS Location, 
REFERENCETABLE('user_data','Manager',username, 5) 
AS Manager
FROM events

Returns the full name (value), location (value), and manager (value) for the username (key) from user_data.

See more Reference data examples


REFERENCESETCONTAINS

Purpose

If a value is contained in a specific reference set, it returns true.

REFERENCESETCONTAINS
('Ref_Set', 'value', domainID)

Although the domainID is optional, in a domain-enabled environment, the search is limited to only shared reference data when the domainID is excluded.

Parameters

String, String, Integer

Example 
SELECT 
ASSETUSER(sourceip, NOW()) 
AS 'Source Asset User'
FROM flows
WHERE 
REFERENCESETCONTAINS('Watchusers', username, 5)
GROUP BY "Source Asset User"
LAST 24 HOURS

Returns the asset user when the username (value) is included in the Watchusers reference set.


RULENAME

Purpose

Returns one or more rule names that are based on the rule ID or IDs that are passed in. 

RULENAME(creeventlist)

RULENAME(3453)
Parameters

A single rule ID, or a list of rule IDs.

Example 
SELECT * FROM events
WHERE RULENAME(creEventList) 
ILIKE '%my rule name%'

Returns events that trigger a specific rule name.


SELECT RULENAME(123) 
FROM events

Returns rule name by the rule ID.