User and Network Monitoring Query Examples
Use query examples to help you create your user and network monitoring query AQL queries.
Use the following examples to monitor your users and network, or you can edit the queries to suit your requirements.
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
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.