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
- ARIELSERVERS4EPID
- ARIELSERVERS4EPNAME
- ASSETHOSTNAME
- ASSETPROPERTY
- ASSETUSER
- CATEGORYNAME
- COMPONENTID
- DOMAINNAME
- GLOBALVIEW
- GEO::LOOKUP
- GEO::LOOKUP_BY_DOMAIN
- GEO::LOOKUP_TEXT
- GEO::LOOKUP_TEXT_BY_DOMAIN
- GEO::DISTANCE
- GEO::DISTANCE_BY_DOMAIN
- HOSTNAME
- INCIDR
- INOFFENSE
- LOGSOURCENAME
- LOGSOURCEGROUPNAME
- LOGSOURCETYPENAME
- MATCHESASSETSEARCH
- NETWORKNAME
- FULLNETWORKNAME
- OFFENSE_TIME
- PARAMETERS EXCLUDESERVERS
- PARAMETERS REMOTESERVERS
- PROCESSORNAME
- PROTOCOLNAME
- QIDNAME
- QIDESCRIPTION
- REFERENCEMAP
- REFERENCEMAPSETCONTAINS
- REFERENCETABLE
- REFERENCESETCONTAINS
- RULENAME
APPLICATIONNAME
- Purpose
-
Returns flow application names by application ID
- Parameters
-
Application ID
- Example
-
SELECT APPLICATIONNAME(applicationid) AS 'Name of App' FROM flows
ARIELSERVERS4EPID
- Purpose
-
Use the ARIELSERVERS4EPID function to specify the Event Processor ID when you use it with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS.
- Parameters
The following examples show how to use the ARIELSERVERS4EPID function with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS:ARIELSERVERS4EPID(processor_ID)
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:
ARIELSERVERS4EPNAME
- Purpose
-
You use the ARIELSERVERS4EPNAME function to specify the Event Processor name when you use it with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS.
- Parameters
The following examples show how you use ARIELSERVERS4EPNAME PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS:ARIELSERVERS4EPNAME('eventprocessor_name')
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')
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
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
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)
- Example
-
SELECT ASSETUSER(sourceip, now()) AS 'Username of Asset' FROM events
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
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')
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
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. The data is returned in JSON format.
- 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::LOOKUP_BY_DOMAIN
- Purpose
-
Returns location data, provided by MaxMind, for a selected IP address and domain ID. The data is returned in JSON format.
- Parameters
-
IP address (required), domain ID
Strings (at least one required):
city
,continent
,physical_country
,registered_country
,represented_country
,location
,postal
,subdivisions
,traits
,geo_json
my_domain_id
- Example
-
SELECT sourceip, GEO::LOOKUP_BY_DOMAIN(sourceip, 'city', 'my_domain_id') AS GEO_CITY FROM events last 10 minutes
GEO::LOOKUP_TEXT
- Purpose
-
Returns location data in plain text, provided by MaxMind, for a selected IP address.
- Parameters
-
IP address (required), primitive field name
Strings (at least one required):
city_name
,city_geo_id
,city_confidence
,continent_name
,continent_geo_id
,continent_code
,country_name
,country_geo_id
,country_iso_code
,country_confidence
,physical_country_name
,physical_country_geo_id
,physical_country_iso_code
,physical_country_confidence
,registered_country_name
,registered_country_geo_id
,registered_country_iso_code
,registered_country_confidence
,represented_country_name
,represented_country_geo_id
,represented_country_iso_code
,represented_country_confidence
,represented_country_type
,postal_confidence
,accuracy_radius
,average_income
,latitude
,longitude
,metro_code
,population_density
,time_zone
,autonomous_system_number
,autonomous_system_organization
,domain
,internet_service_provider
,user_type
,full_name
- Example
-
SELECT sourceip, GEO::LOOKUP_TEXT(sourceip, 'city_name') AS GEO_CITY FROM events last 10 minutes
GEO::LOOKUP_TEXT_BY_DOMAIN
- Purpose
-
Returns location data in plain text, provided by MaxMind, for a selected IP address and domain ID.
- Parameters
-
IP address (required), primitive field name, domain ID
Strings (at least one required):
city_name
,city_geo_id
,city_confidence
,continent_name
,continent_geo_id
,continent_code
,country_name
,country_geo_id
,country_iso_code
,country_confidence
,physical_country_name
,physical_country_geo_id
,physical_country_iso_code
,physical_country_confidence
,registered_country_name
,registered_country_geo_id
,registered_country_iso_code
,registered_country_confidence
,represented_country_name
,represented_country_geo_id
,represented_country_iso_code
,represented_country_confidence
,represented_country_type
,postal_confidence
,accuracy_radius
,average_income
,latitude
,longitude
,metro_code
,population_density
,time_zone
,autonomous_system_number
,autonomous_system_organization
,domain
,internet_service_provider
,user_type
,full_name
- Example
-
SELECT sourceip, GEO::LOOKUP_TEXT_BY_DOMAIN(sourceip, 'city_name', 'my_domain_id') 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
GEO::DISTANCE_BY_DOMAIN
- Purpose
-
Returns the distance, in kilometers, of two IP addresses. and a domain ID
- Parameters
-
IP address (two required), domain ID
- Example
-
SELECT GEO::DISTANCE(sourceip, destinationip, domainid) 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)
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)
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%'
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%'
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.
MATCHESASSETSEARCH
- Purpose
- If the asset is returned in the results of the saved search, it returns true.
- 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.
- Parameters
-
Host property (domain is optional)
- Examples
-
SELECT NETWORKNAME(sourceip) ILIKE 'servers' AS 'My Networks' FROM flows
FULLNETWORKNAME
- Purpose
- Returns the full network name from the network hierarchy for the host that is passed in.
- Parameters
-
Host property (domain is optional)
- Examples
-
SELECT FULLNETWORKNAME('1.2.3.4') FROM events LIMIT 1
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(12345) times OFFENSE_TIME(12345)
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
or127.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:
- 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:
- 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:
- 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:
- 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
- Example
-
SELECT processorid, PROCESSORNAME(processorid) FROM events WHERE processorid=104 GROUP BY processorid LAST 5 MINUTES
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
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
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
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 thedomainID
is excluded.
- Parameters
-
String, String, Integer
- Example
-
SELECT REFERENCEMAP('Full_name_lookup', username, 5) AS Name_of_User FROM events
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 thedomainID
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
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 thedomainID
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
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%'