AQL search string examples

Reporting account usage

Different user communities can have different threat and usage indicators.

Use reference data to report on several user properties, for example, department, location, or manager. You can use external reference data.

The following query returns metadata information about the user from their login 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

Insight across multiple account identifiers

In this example, individual users have multiple accounts across the network. The organization requires a single view of a users activity.

Use reference data to map local user IDs to a global ID.

The following query returns the user accounts that are used by a global ID on events that are flagged as suspicious.

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 1 days

The following query shows the activities that are completed by a global ID.

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 Doe'
LAST 1 days

Identify suspicious long-term beaconing

Many threats use command and control to communicate periodically over days, weeks, and months.

Advanced searches can identify connection patterns over time. For example, you can query consistent, short, low volume, number of connections per day/week/month between IP addresses, or an IP address and geographical location.

The following query detects potential instances of hourly beaconing.

SELECT sourceip, destinationip,
UNIQUECOUNT(DATEFORMAT(starttime,'HH')) as 'different hours',
COUNT(*) as 'total flows'
FROM flows
WHERE flowdirection = 'L2R'
GROUP BY sourceip, destinationip
HAVING "different hours" > 20
AND "total flows" < 25
LAST 24 hours

The following query detects potential instances of daily beaconing.

SELECT sourceip, destinationip,
UNIQUECOUNT(DATEFORMAT(starttime,'dd'))as 'different days',
COUNT(*) as 'total flows'
FROM flows
WHERE flowdirection='L2R'
GROUP BY sourceip, destinationip
HAVING "different days" > 4
AND "total flows" < 14
LAST 7 days

The following query detects daily beaconing between a source IP and a destination IP. The beaconing times are not at the same time each day. The time lapse between beacons is short.

SELECT
sourceip,
LONG(DATEFORMAT(starttime,'hh')) as hourofday,
(AVG( hourofday*hourofday) - (AVG(hourofday)^2))as variance,
COUNT(*) as 'total flows'
FROM flows
GROUP BY sourceip, destinationip
HAVING variance < 01 and "total flows" < 10
LAST 7 days

The following query detects daily beaconing to a domain by using proxy log events. The beaconing times are not at the same time each day. The time lapse between beacons is short.

SELECT sourceip,
LONG(DATEFORMAT(starttime,'hh')) as hourofday,
(AVG(hourofday*hourofday) - (AVG(hourofday)^2)) as variance,
COUNT(*) as 'total events'
FROM events
WHERE LOGSOURCEGROUPNAME(devicegrouplist) ILIKE '%proxy%'
GROUP BY url_domain
HAVING variance < 0.1 and "total events" < 10
LAST 7 days

The url_domain property is a custom property from proxy logs.

External threat intelligence

Usage and security data that is correlated with external threat intelligence data can provide important threat indicators.

Advanced searches can cross-reference external threat intelligence indicators with other security events and usage data.

This query shows how you can profile external threat data over many days, weeks, or months to identify and prioritize the risk level of assets and accounts.

Select
REFERENCETABLE('ip_threat_data','Category',destinationip) as '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 'Category', 'Threat Rating'
LAST 1 days

Asset intelligence and configuration

Threat and usage indicators vary by asset type, operating system, vulnerability posture, server type, classification, and other parameters.

In this query, advanced searches and the asset model provide operational insight into a location.

The Assetproperty function retrieves property values from assets, which enables you to include asset data in the results.

SELECT
ASSETPROPERTY('Location',sourceip) as location,
COUNT(*) as 'event count'
FROM events
GROUP BY location
LAST 1 days

The following query shows how you can use advanced searches and user identity tracking in the asset model.

The AssetUser function retrieves the user name from the asset database.

SELECT 
APPLICATIONNAME(applicationid) as App,
ASSETUSER(sourceip, now()) as srcAssetUser,
COUNT(*) as 'Total Flows'
FROM flows
WHERE srcAssetUser IS NOT NULL
GROUP BY App, srcAssetUser
ORDER BY "Total Flows" DESC
LAST 3 HOURS

Network LOOKUP function

You can use the Network LOOKUP function to retrieve the network name that is associated with an IP address.

SELECT NETWORKNAME(sourceip) as srcnet,
NETWORKNAME(destinationip) as dstnet
FROM events

Rule LOOKUP function

You can use the Rule LOOKUP function to retrieve the name of a rule by its ID.

SELECT RULENAME(123) FROM events

The following query returns events that triggered a specific rule name.

SELECT * FROM events
WHERE RULENAME(creEventList) ILIKE '%my rule name%'

Full TEXT SEARCH

You can use the TEXT SEARCH operator to do full text searches by using the Advanced search option.

In this example, there are a number of events that contain the word "firewall" in the payload. You can search for these events by using the Quick filter option and the Advanced search option on the Log Activity tab.

Custom property

You can access custom properties for events and flows when you use the Advanced search option.

SELECT "MyWebsiteUrl", * FROM events ORDER BY "MyWebsiteUrl"