Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 
ON THIS PAGE
 

Ariel Query Structure

Use AQL to extract, filter, and perform actions on event and flow data that you extract from the Ariel database in JSA. You can use AQL to get data that might not be easily accessible from the user interface.

The following diagram shows the flow of an AQL query.

Figure 1: AQL Query FlowAQL Query Flow

Structure Of an AQL Statement

Use the SELECT statement to select fields from events or flows in the Ariel database, which are displayed as columns. For example, the following query returns the results that are shown in the following table:

SELECT sourceip, destinationip, username, protocolid, eventcount FROM events

Table 1: AQL Query Results

sourceip

destinationip

Username

Protocolid

eventcount

192.0.2.21

198.51.100.21

Joe Ariel

233

1

192.0.2.22

198.51.100.24

Jim Ariel

233

1

AQL queries begin with a SELECT statement to select event or flow data from the Ariel database. You can refine the data output of the SELECT statement by using the WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, and LAST clauses.

  • SELECT--Use the SELECT statement to select fields from events or flows. For example, select all fields from events or flows by typing:

    SELECT * FROM events, or SELECT * FROM flows

Use the following clauses to filter and manipulate the data that is returned by the SELECT statement:

  • WHERE--Use the WHERE clause to insert a condition that filters the output, for example, WHERE logsourceid='65'.

  • GROUP BY--Use the GROUP BY clause to group the results by one or more columns that you specify in the query, for example, GROUP BY logsourceid.

  • HAVING--Use the HAVING clause to specify a condition after the GROUP BY clause, for example, HAVING MAG > 3.

  • ORDER BY--Use the ORDER BY clause to order the results for a column in the AQL query in an ascending or descending order, for example, ORDER BY username DESC.

  • LIMIT --Use a LIMIT clause to limit the number of results that are returned to a specific number, for example LIMIT 50 to limit the output to 50 results.

  • LAST--Use a LAST clause to specify a time frame for the query, for example LAST 1 HOURS.

The following example incorporates all of the clauses that are described in the list:

SELECT Statement

Use the SELECT statement to define the criteria that you use to retrieve event or flow data.

Use the SELECT statement to define the columns (fields) that you want to output from your query. You can use the SELECT statement to output data from an AQL function by using a column alias. Typically, you refer to events or flows in your SELECT statement but you can also use the SELECT statement with the GLOBALVIEW database, or any other database that you might have access to.

Use the SELECT statement to select the columns that you want to display in the query output.

A SELECT statement can include the following elements:

  • Fields from the events or flows databases

  • Custom properties from the events or flows databases

  • Functions that you use with fields to represent specific data that you want to return.

    For example, the function ASSETHOSTNAME(sourceip) searches for the host name of an asset by source IP address at a specific time.

Use an asterisk (*) to denote all columns.

Field names and SELECT and FROM statements are not case-sensitive. For example, the following query uses different cases and it parses.

select Sourceip, DATEFORMAT(starTTime,'YYYY-MM-dd HH:mm') as startTime from events WHERE username is noT Null GROUP BY sourceip ordER BY starttime lAsT 3 houRS

The following examples are queries that use SELECT statements:

  • SELECT * FROM flows

    Returns all columns from the flows database.

  • SELECT sourceip, destinationip FROM events

    Returns only the sourceip and destinationip columns from the events database.

  • SELECT sourceip, * FROM flows

    Returns the sourceip column first, which is followed by all columns from the flows database.

  • SELECT sourceip AS 'MY Source IPs' FROM events

    Returns the sourceip column as the alias or renamed column 'MY Source IPs'.

  • SELECT ASSETHOSTNAME(sourceip) AS 'Host Name', sourceip FROM events

    Returns the output of the function ASSETHOSTNAME as the column name Host Name, and the sourceip column from the events database.

WHERE Clause

Filter your AQL queries by using WHERE clauses. The WHERE clause describes the filter criteria that you apply to the query and filters the resulting view to accept only those events or flows that meet the specified condition.

You can apply the WHERE clause to add a condition to search criteria in AQL queries, which filters the search results.

A search condition 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.

You can apply the following filters when you use WHERE clause in a query:

  • Equal sign (=)

  • Not equal to symbol (<>)

  • Less than symbol (<)

  • Greater than symbol (>)

  • Less that or equal to symbol (<=)

  • Greater than or equal to symbol (>=)

  • BETWEEN between two values, for example (64 AND 512)

  • LIKE case sensitive match

  • ILIKE case insensitive match

  • IS NULL is empty

  • AND / OR combine conditions or either condition

  • TEXT SEARCH text string match

Examples Of WHERE Clauses

The following query example shows events that have a severity level of greater than nine and are from a specific category.

SELECT sourceIP, category, credibility FROM events WHERE severity > 9 AND category = 5013

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)

Return events from the events database where the text 'typot' is found.

SELECT QIDNAME(qid) AS EventName, * FROM events WHERE TEXT SEARCH 'typot'

The following query outputs events from the events database where health is included in the log source name.

SELECT logsourceid, LOGSOURCEGROUPNAME(logsourceid), LOGSOURCENAME(logsourceid) FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

The following query outputs events where the device type ID is equal to 11 (Linux Server DSM), and where the QID is equal to 44250002, which is the identifier for Cron Status.

SELECT * FROM events WHERE deviceType= '11' AND qid= '44250002'

GROUP BY Clause

Use the GROUP BY clause to aggregate your data by one or more columns. To provide meaningful results of the aggregation, usually, data aggregation is combined with aggregatefunctions on remaining columns.

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.

The results might look similar to the following output.

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:

To view the maximum number of events, use the following syntax:

To view the number of average events from a source IP, use the following syntax:

The output displays the following results:

HAVING Clause

Use the HAVING clause in a query to apply more filters to specific data by applying filters to the results after the GROUP BY clause.

The HAVING clause follows the GROUP BY clause.

You can apply the following filters when you use a HAVING clause in a query:

  • Equal sign (=)

  • Not equal to symbol (<>)

  • Less than symbol (<)

  • Greater than symbol (>)

  • Less that or equal to symbol (<=)

  • Greater than or equal to symbol (>=)

  • BETWEEN between two values, for example (64 AND 512)

  • LIKE case-sensitive match

  • ILIKE case insensitive match

  • SUM/AVG total or average values

  • MAX/MIN maximum or minimum values

Examples Of HAVING Clauses

The following query example shows results for users who triggered VPN events from more than four IP addresses (HAVING 'Count of Source IPs' > 4) in the last 24 hours.

Note:

When you type an AQL query, use single quotation marks for a string comparison, and use double quotation marks for a property value comparison.

The following query example shows results for events where the credibility (HAVING credibility > 5) is greater than five.

The following query groups results by source IP but displays only results where the magnitude (HAVING magnitude > 5) is greater than five.

ORDER BY Clause

Use the ORDER BY clause to sort the resulting view that is based on expression results. The result is sorted by ascending or descending order.

Note:

When you type an AQL query, use single quotation marks for a string comparison, and use double quotation marks for a property value comparison.

You can use the ORDER BY clause on one or more columns.

Use the GROUP BY and ORDER BY clauses in a single query.

Sort in ascending or descending order by appending the ASC or DESC keyword to the ORDER BY clause.

Examples Of ORDER BY Clauses

To query AQL to return results in descending order, use the following syntax:

To display results in ascending order, use the following syntax:

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. For example, the following query displays the most traffic intensive IP address in descending order:

Note:

When you use the GROUP BY clause with a column name or AQL function, only the first value is returned for the GROUP BY column, by default, even though other values might exist.

When you use a time field in the ORDER BY clause, use a simple datetime field, such as starttime. Using a formatted datetime field can impact the performance of the search.

LIKE Clause

Use the LIKE clause to retrieve partial string matches in the Ariel database.

You can search fields by using the LIKE clause.

The following table shows the wildcard options are supported by the Ariel Query Language (AQL).

Table 2: 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:

To match names that begin with Jo that are 3 characters long, such as, Joe or Jon, type the following query:

You can enter the wildcard option at any point in the command, as shown in the following examples.

Examples Of String Matching Keywords

The keywords, ILIKE and IMATCHES are case-insensitive versions of LIKE and MATCHES.

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.

Examples Of the Count Function

The following query returns the count of all events with credibility that is greater than or equal to 9.

The following query returns the count of assets by location and source IP address.

The following query returns the user names, source IP addresses, and count of events.

The sourceip column is returned as FIRST_sourceip.

One sourceip is returned only per username, even if another sourceip exists.

Note:

When you use the GROUP BY clause with a column name or AQL function, only the first value is returned for the GROUP BY column, by default, even though other values might exist.

Quotation Marks

In an AQL query, query terms and queried columns sometimes require single or double quotation marks so that JSA can parse the query.

The following table defines when to use single or double quotation marks.

Table 3: Type Of Quotation Marks to Use in a Query

Type of quotation marks

When to use

Single

To specify any American National Standards Institute (ANSI) VARCHAR string to SQL such as parameters for a LIKE or equals (=) operator, or any operator that expects a VARCHAR string.

SELECT * from events WHERE sourceip = ’192.0.2.0’
SELECT * from events WHERE userName LIKE ’%james%’
SELECT * from events WHERE userName = ’james’
SELECT * FROM events
WHERE INCIDR(’10.45.225.14’, sourceip)
SELECT * from events WHERE TEXT SEARCH ’my search term’

Double

Use double quotation marks for the following query items to specify table and column names that contain spaces or non-ASCII characters, and to specify custom property names that contain spaces or non-ASCII characters.

SELECT "username column" AS ’User name’ FROM events
SELECT "My custom property name"
AS ’My new alias’ FROM events

Use double quotation marks to define the name of a system object such as field, function, database, or an existing alias.

SELECT "Application Category", sourceIP,
EventCount AS ’Count of Events’
FROM events GROUP BY "Count of Events"

Use double quotation marks to specify an existing alias that has a space when you use a WHERE, GROUP BY, or ORDER BY clause 

SELECT sourceIP, destinationIP, sourcePort,
EventCount AS ’Event Count’,
category, hasidentity, username, payload, UtF8(payLoad),
QiD, QiDnAmE(qid) FROM events
WHERE (NOT (sourcePort <= 3003 OR hasidentity = ’True’))
AND (qid = 5000023 OR qid = 5000193)
AND (INCIDR(’192.0.2.0/4’, sourceIP)
OR NOT INCIDR(’192.0.2.0/4’, sourceIP)) ORDER BY "Event Count"
DESC LAST 60 MINUTES

SSELECT sourceIP, destinationIP, sourcePort, EventCount
AS ’Event Count’,
category, hasidentity, username, payload, UtF8(payLoad),
QiD, QiDnAmE(qid)
FROM events ORDER BY "Event Count"
DESC LAST 60 MINUTES

Single or double

Use single quotation marks to specify an alias for a column definition in a query.

SELECT username AS ’Name of User’, sourceip
AS ’IP Source’ FROM events

Use double quotation marks to specify an existing alias with a space when you use a WHERE, GROUP BY, or ORDER BY clause.

SELECT sourceIP AS ’Source IP Address’,
EventCount AS ’Event Count’, QiD, QiDnAmE(qid)
FROM events
GROUP BY "Source IP Address"
LAST 60 MINUTES

Copying Query Examples from the AQL Guide

If you copy and paste a query example that contains single or double quotation marks from the AQL Guide, you must retype the quotation marks to be sure that the query parses.

Related Documentation