User and network monitoring query examples

Find users who used the VPN to access the network from three or more IP addresses in a 24-hour period

SELECT username, 
UNIQUECOUNT(sourceip) 
AS 'Source IP count' 
FROM events 
WHERE LOGSOURCENAME(logsourceid) 
ILIKE '%VPN%' 
AND username IS NOT NULL 
GROUP BY username 
HAVING "Source IP count" >= 3
ORDER BY "Source IP count" 
DESC 
LAST 24 HOURS

This query outputs the username and Source IP count columns.

The username column returns the names of users who used the VPN to access the network from three or more IP addresses in the last 24 hours.

Find users who used the VPN from more that one geographic location in 24 hours

SELECT username, UNIQUECOUNT(geographiclocation) 
AS 'Count of locations' 
FROM events 
WHERE LOGSOURCENAME(logsourceid) 
ILIKE '%VPN%' 
AND geographiclocation <> 'other location' 
AND username 
IS NOT NULL 
GROUP BY username 
HAVING "Count of locations" > 1 
ORDER BY "Count of locations" 
DESC 
LAST 3 DAYS

This query outputs the username and Count of locations columns.

The username column returns the names of users who used the VPN from more than one location that is not called 'other location' in the last 24 hours.

Monitoring local to remote flow traffic by network

SELECT sourceip, 
LONG(SUM(sourcebytes+destinationbytes)) 
AS TotalBytes 
FROM flows 
WHERE flowdirection= 'L2R' 
AND NETWORKNAME(sourceip) 
ILIKE 'servers' 
GROUP BY sourceip
ORDER BY TotalBytes

This query outputs the sourceip and TotalBytes columns.

The TotalBytes column returns the sum of the source and destination bytes that crosses from local to remote.

Monitoring remote to local flow traffic by network

SELECT sourceip, 
LONG(SUM(sourcebytes+destinationbytes)) 
AS TotalBytes 
FROM flows 
WHERE flowdirection= 'R2L' 
AND NETWORKNAME(sourceip) 
ILIKE 'servers' 
GROUP BY sourceip
ORDER BY TotalBytes

This query outputs the sourceip and TotalBytes columns.

The TotalBytes column returns the sum of the source and destination bytes from remote to local.

Application usage by application name, users, and flows traffic

SELECT sourceip 
AS Source_IP, 
FIRST(destinationip) 
AS Destination_IP, 
APPLICATIONNAME(applicationid) 
AS Application, 
DATEFORMAT(lastpackettime, 'dd-MM-yyyy hh:m:ss') 
AS 'Start Time', 
FIRST(sourcebytes) 
AS Source_Bytes, 
ASSETUSER(sourceip, NOW()) AS Src_Asset_User 
FROM flows 
GROUP BY Source_IP 
ORDER BY Source_Bytes DESC

This query outputs data about your asset users, application names, and flow data. Use this query to report specific user activity or application usage, or to build a variation of this query to achieve your desired results.

Location of assets

SELECT ASSETPROPERTY('Location',sourceip) 
AS asset_location, 
COUNT(*) 
FROM events 
GROUP BY asset_location 
LAST 1 days

This query outputs the asset_location and count columns.

The asset location column returns the location of the assets.

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.