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.
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
|
|
|
|
|
|---|---|---|---|---|
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
SELECTstatement to select fields from events or flows. For example, select all fields from events or flows by typing:SELECT * FROM events, orSELECT * FROM flows
Use the following clauses to filter and manipulate the data that is returned by the SELECT statement:
WHERE--Use the
WHEREclause to insert a condition that filters the output, for example,WHERE logsourceid='65'.
GROUP BY--Use the
GROUP BYclause to group the results by one or more columns that you specify in the query, for example,GROUP BY logsourceid.
HAVING--Use the
HAVINGclause to specify a condition after theGROUP BYclause, for example,HAVING MAG > 3.
ORDER BY--Use the
ORDER BYclause 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
LIMITclause to limit the number of results that are returned to a specific number, for exampleLIMIT 50to 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 sourceip, destinationip, username FROM events WHERE username = ’test name’ GROUP by sourceip, destinationip ORDER BY sourceip DESC LIMIT 10 LAST 2 DAYS
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 flowsReturns all columns from the flows database.
SELECT sourceip, destinationip FROM eventsReturns only the
sourceipanddestinationipcolumns from the events database.SELECT sourceip, * FROM flowsReturns the
sourceipcolumn first, which is followed by all columns from the flows database.SELECT sourceip AS 'MY Source IPs' FROM eventsReturns the
sourceipcolumn as the alias or renamed column 'MY Source IPs'.SELECT ASSETHOSTNAME(sourceip) AS 'Host Name', sourceip FROM eventsReturns the output of the function
ASSETHOSTNAMEas the column nameHost Name, and thesourceipcolumn 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 (
>=)BETWEENbetween two values, for example (64 AND 512)LIKEcase sensitive matchILIKEcase insensitive matchIS NULLis emptyAND / ORcombine conditions or either conditionTEXT SEARCHtext 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.
SELECT sourceIP, SUM(sourceBytes) FROM flows where sourceBytes > 1000000 GROUP BY sourceIP
The results might look similar to the following output.
----------------------------------- | sourceIP | SUM_sourceBytes | ----------------------------------- | 192.0.2.0 | 4282590.0 | | 198.51.100.0 | 4902509.0 | | 203.0.113.0 | 2802715.0 | | 203.0.113.1 | 3313370.0 | | 198.51.100.1 | 2467183.0 | | 198.51.100.2 | 8325356.0 | | 203.0.113.2 | 1629768.0 | -----------------------------------
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:
------------------------------ | sourceIP | sourceBytes | ------------------------------ | 192.0.2.0 | 1448629 | | 198.51.100.0 | 2412426 | | 203.0.113.0 | 1793095 | | 203.0.113.1 | 1449148 | | 198.51.100.1 | 1097523 | | 198.51.100.2 | 4096834 | | 192.0.2.1 | 2833961 | | 198.51.100.3 | 2490083 | | 203.0.113.2 | 1629768 | | 203.0.113.3 | 1009620 | | 198.51.100.4 | 1369660 | | 203.0.113.4 | 1864222 | | 198.51.100.5 | 4228522 | ------------------------------
To view the maximum number of events, use the following syntax:
SELECT MAX(eventCount) FROM events
To view the number of average events from a source IP, use the following syntax:
SELECT AVG(eventCount), PROTOCOLNAME(protocolid) FROM events GROUP BY sourceIP
The output displays the following results:
--------------------------------- | sourceIP | protocol | --------------------------------- | 192.0.2.0 | TCP.tcp.ip | | 198.51.100.0 | UDP.udp.ip | | 203.0.113.0 | UDP.udp.ip | | 203.0.113.1 | UDP.udp.ip | | 198.51.100.1 | TCP.tcp.ip | | 198.51.100.2 | TCP.tcp.ip | | 192.0.2.1 | TCP.tcp.ip | | 198.51.100.3 | ICMP.icmp.ip | ---------------------------------
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 (
>=)BETWEENbetween two values, for example (64 AND 512)LIKEcase-sensitive matchILIKEcase insensitive matchSUM/AVGtotal or average valuesMAX/MINmaximum 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.
SELECT username, UNIQUECOUNT(sourceip) AS ’Count of Source IPs’ FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE ’%vpn%’ AND username IS NOT NULL GROUP BY username HAVING "Count of Source IPs" > 4 LAST 24 HOURS
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.
SELECT username, sourceip, credibility FROM events GROUP BY sourceip HAVING credibility > 5 LAST 1 HOURS
The following query groups results by source IP but displays
only results where the magnitude (HAVING magnitude >
5) is greater than five.
SELECT sourceIP, magnitude FROM events GROUP BY sourceIP HAVING magnitude > 5
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.
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:
SELECT sourceBytes, sourceIP FROM flows WHERE sourceBytes > 1000000 ORDER BY sourceBytes DESC
To display results in ascending order, use the following syntax:
SELECT sourceBytes, sourceIP FROM flows WHERE sourceBytes > 1000000 ORDER BY sourceBytes ASC
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:
SELECT sourceIP, SUM(sourceBytes) FROM flows GROUP BY sourceIP ORDER BY SUM(sourceBytes) DESC
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).
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:
SELECT * FROM events WHERE userName LIKE ’Jo%’
To match names that begin with Jo that are 3 characters long, such as, Joe or Jon, type the following query:
SELECT * FROM events WHERE userName LIKE ’Jo_’
You can enter the wildcard option at any point in the command, as shown in the following examples.
SELECT * FROM flows WHERE sourcePayload LIKE ’%xyz’ SELECT * FROM events WHERE UTF8 (payload) LIKE ’%xyz%’ SELECT * FROM events WHERE UTF8 (payload) LIKE ’_yz’
Examples Of String Matching Keywords
The keywords, ILIKE and IMATCHES are case-insensitive versions of LIKE and MATCHES.
SELECT qidname(qid) as test FROM events WHERE test LIKE ’Information%’ SELECT qidname(qid) as test FROM events WHERE test ILIKE ’inForMatiOn%’ SELECT qidname(qid) as test FROM events WHERE test MATCHES ’.*Information.*’ SELECT qidname(qid) as test FROM events WHERE test IMATCHES ’.*Information.*’
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.
SELECT COUNT(*) FROM events WHERE credibility >= 9
The following query returns the count of assets by location and source IP address.
SELECT ASSETPROPERTY(’Location’,sourceip) AS location, COUNT(*) FROM events GROUP BY location LAST 1 days
The following query returns the user names, source IP addresses, and count of events.
SELECT username, sourceip, COUNT(*) FROM events GROUP BY username LAST 600 minutes
The sourceip column is returned as FIRST_sourceip.
One sourceip is returned only per username, even if another sourceip exists.
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.
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.