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