Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 

Introduction to AQL with Sample Queries

 

You use Ariel Query Language (AQL) to get information about events and flows from JSA that you can't get from tables or graphs on the JSA tabs.

Use AQL queries in the Advanced Search box on the Log Activity or Network Activity tabs, or in some apps in JSA to graph dashboard items.

Use the following syntax order when you use AQL:

  • SQL Structure

  • [SELECT *, column_name, column_name]

  • [FROM table_name]

  • [WHERE search clauses]

  • [GROUP BY column_reference*]

  • [HAVING clause]

  • [ORDER BY column_reference*]

  • [LIMIT numeric_value]

  • [TIMEFRAME]

Note

When you use a GROUP BY or ORDER BY clause to sort information, you can reference column_names from your existing SELECT statement only.

Note

By default, if the TIMEFRAME value is not specified, the query runs against the last five minutes of Ariel data.

Remember to use single quotation marks to specify literal values or variables and use double quotation marks for column names that contain spaces or non-ASCII characters:

Single quotation marks

Use single quotation marks when you reference the beginning and end of a string, as shown in these examples:

username LIKE ’%User%'

sourceCIDR= ’192.0.2.0’

TEXT SEARCH = ’VPN Authenticated user'

QIDNAME(qid) AS 'Event Name'

Double quotation marks

Use double quotation marks when column names contain spaces or non-ASCII characters, as shown in these examples:

Custom property names with spaces, such as “Account Security ID”.

Values that have non-ASCII characters.

Simple AQL Queries

Table 1: Simple AQL Queries

Basic AQL Commands

Comments

SELECT * FROM events LAST 10 MINUTES

Returns all the fields from the events table that were sent in the last 10 minutes..

SELECT sourceip,destinationip FROM events LAST 24 HOURS

Returns the sourceip and destinationip from the events table that were sent in the last 24 hours.

SELECT * FROM events START ’2017 01 01 9:00:00’ STOP ’2017 01 01 10:20:00’

Returns all the fields from the events table during that time interval.

SELECT * FROM events limit 5 LAST 24 HOURS

Returns all the fields in the events table during the last 24 hours, with output limited to five results.

SELECT * FROM events ORDER BY magnitude DESC LAST 24 HOURS

Returns all the fields in the events table sent in the last 24 hours, sorting the output from highest to lowest magnitude.

SELECT * FROM events WHERE magnitude >= 3 LAST 24 HOURS

Returns all the fields in the events table that have a magnitude that is less than three from the last 24 hours.

SELECT * FROM events WHERE sourceip = ’192.0.2.0’ AND destinationip = ’198.51.100.0’ START ’2017 01 01 9:00:00’ STOP ’2017 01 01 10:20:00’

Returns all the fields in the events table that have the specified source IP and destination IP within the specified time period.

SELECT * FROM events WHERE INCIDR(’192.0.2.0/24’, sourceip)

Returns all the fields in the events table where the source IP address is within the specified CIDR IP range.

SELECT * FROM events WHERE username LIKE ’%roul%’

Returns all the fields in the events table where the user name contains the example string. The percentage symbols (%) indicate that the user name can match a string of zero or more characters.

SELECT * FROM events WHERE username ILIKE ’%ROUL%’

Returns all the fields in the events table where the user name contains the example string, and the results are case-insensitive. The percentage symbols (%) indicate that the user name can match a string of zero or more characters.

SELECT sourceip,category,credibility FROM events WHERE (severity > 3 AND category = 5018)OR (severity < 3 AND credibility > 8)

Returns the sourceip, category, and credibility fields from the events table with specific severity levels, a specific category, and a specific credibility level. The AND clause allows for multiple strings of types of results that you want to have.

SELECT * FROM events WHERE TEXT SEARCH ’firewall’

Returns all the fields from the events table that have the specified text in the output.

SELECT * FROM events WHERE username ISNOT NULL

Returns all the fields in the events table where the username value is not null.