Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 
ON THIS PAGE
 

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

    The domain can optionally be specified to target an asset on a particular domain.

  • Parameters--ARIELSERVERS4EPID(processor_ID)

    The following examples show how to use the ARIELSERVERS4EPID function with PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS:

  • 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 JSA deployment, then the query does not run.

    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--ARIELSERVERS4EPNAME(’eventprocessor_name’)

    The following examples show how you use ARIELSERVERS4EPNAME PARAMETERS REMOTESERVERS or PARAMETERS EXCLUDESERVERS:

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

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

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.

  • Parameters--IP address, (timestamp and domain ID are optional)

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

  • Example

    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

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 Juniper Secure Analytics 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):

  • Example

GEO::DISTANCE

  • Purpose--Returns the distance, in kilometers, of two IP addresses.

  • Parameters--IP address (two required)

  • Example

HOSTNAME

  • Purpose--Returns the host name of an event processor with a certain processor ID.

    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.

    CIDR IP Addresses in AQL Queries

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

  • 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,

  • Example --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.

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

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

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

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

    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.

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

    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.

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,

  • Example --In the following example, only the specified server is searched.

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

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

    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.

    Note:

    If the processor ID that you specify as a parameter for the ARIELSERVERS4EPID function is not in your JSA deployment, then the query does not run.

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

    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.

PROCESSORNAME

  • Purpose--Returns the name of a processor by the processor ID.

  • Parameters--Processor ID number

  • Example

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

  • Example

    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.

    Reference Data Query 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.

Related Documentation