Reference data query examples

Use AQL queries to get data from reference sets, reference maps, or reference tables. You can create and populate reference data by using rules to populate reference sets, by using external threat feeds, for example, LDAP Threat Intelligence App, or by using imported data files for your reference set.

Tip: For information on how to use quotation marks in AQL queries, see Quotation marks.

Use the following examples to help you create queries to extract data from your reference data.

Use reference tables to get external metadata for user names that show up in events

SELECT
REFERENCETABLE('user_data','FullName',username) AS 'Full Name',
REFERENCETABLE('user_data','Location',username) AS 'Location',
REFERENCETABLE('user_data','Manager',username) AS 'Manager',
UNIQUECOUNT(username) AS 'Userid Count',
UNIQUECOUNT(sourceip) AS 'Source IP Count',
COUNT(*) AS 'Event Count'
FROM events
WHERE qidname(qid)ILIKE '%logon%'
GROUP BY "Full Name", "Location", "Manager"
LAST 1 days

Use the reference table to get external data such as the full name, location, and manager name for users who logged in to the network in the last 24 hours.

Get the global user IDs for users in events who are flagged for suspicious activity

SELECT
REFERENCEMAP('GlobalID_Mapping',username) AS 'Global ID', 
REFERENCETABLE('user_data','FullName', 'Global ID') AS 'Full Name',
UNIQUECOUNT(username),
COUNT(*) AS 'Event count'
FROM events
WHERE RULENAME(creEventlist) 
ILIKE '%suspicious%'
GROUP BY "Global ID"
LAST 2 days

In this example, individual users have multiple accounts across the network. The organization requires a single view of a user's activity. Use reference data to map local user IDs to a global ID. The query returns the user accounts that are used by a global ID for events that are flagged as suspicious.

Use a reference map lookup to extract global user names for user names that are returned in events

SELECT
QIDNAME(qid) as 'Event name', 
starttime AS Time,
sourceip AS 'Source IP', 
destinationip AS 'Destination IP',
username AS 'Event Username',
REFERENCEMAP('GlobalID_Mapping', username) AS 'Global User'
FROM events
WHERE "Global User" = 'John Ariel'
LAST 1 days

Use the reference map to look up the global user names for user names that are returned in events. Use the WHERE clause to return only events for the global user John Ariel. John Ariel might have a few different user names but these user names are mapped to a global user, for example, in an external identity mapping system, you can map a global user to several user names used by the same global user.

Monitoring high network utilization by users

SELECT
LONG(REFERENCETABLE('PeerGroupStats', 'average', 
REFERENCEMAP('PeerGroup',username)))
AS PGave,
LONG(REFERENCETABLE('PeerGroupStats', 'stdev', 
REFERENCEMAP('PeerGroup',username)))
AS PGstd, 
SUM(sourcebytes+destinationbytes) AS UserTotal
FROM flows
WHERE flowtype = 'L2R'
GROUP BY UserTotal 
HAVING UserTotal > (PGAve+ 3*PGStd)

Returns user names where the flow utilization is three times greater than the average user.

You need a reference set to store network utilization of peers by user name and total bytes.

Threat ratings and categories

SELECT 
REFERENCETABLE('ip_threat_data','Category',destinationip) 
AS 'Threat Category',
REFERENCETABLE('ip_threat_data','Rating', destinationip) 
AS 'Threat Rating',
UNIQUECOUNT(sourceip) 
AS 'Source IP Count',
UNIQUECOUNT(destinationip) 
AS 'Destination IP Count' 
FROM events 
GROUP BY "Threat Category", "Threat Rating" LAST 24 HOURS

Returns the threat category and the threat rating.

You can look up reference table threat data and include it in your searches.

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.