QRADAR-AQL
Authorized use only. Offensive reference for systems you own or are explicitly permitted to test. You are responsible for staying within the law.
AQL (Ariel Query Language) is QRadar's SQL-like query language for searching events, flows, and other data sources.
BASIC SYNTAX#
SELECT fields FROM table WHERE conditions GROUP BY fields ORDER BY field # Tables events # Security events from log sources flows # Network flow data offense # Offenses/incidents # Basic query structure SELECT * FROM events LAST 1 HOURS SELECT sourceip, destinationip FROM flows WHERE destinationport = 443 SELECT * FROM events WHERE "Event Name" LIKE '%login%'
TIME RANGES#
LAST 1 HOURS # Last hour LAST 4 HOURS # Last 4 hours LAST 24 HOURS # Last day LAST 7 DAYS # Last week LAST 30 DAYS # Last month START '2024-01-01 00:00' STOP '2024-01-02 00:00' # Specific range
COMMON FIELDS - EVENTS#
sourceip # Source IP address destinationip # Destination IP address sourceport # Source port destinationport # Destination port username # Username "Event Name" # Event name/description "Event Count" # Number of events starttime # Event timestamp devicetype # Log source type logsourcename # Log source name category # Event category magnitude # Event magnitude/severity filename # File name url # URL "HTTP Host" # HTTP hostname "HTTP Method" # GET, POST, etc. "HTTP Response Code" # HTTP status code "User Agent" # Browser/client info "Process Name" # Process name "Command Line" # Command executed
COMMON FIELDS - FLOWS#
sourceip # Source IP destinationip # Destination IP sourceport # Source port destinationport # Destination port protocol # Protocol number bytessent # Bytes sent bytesreceived # Bytes received packetsent # Packets sent packetreceived # Packets received starttime # Flow start time endtime # Flow end time application # Detected application ================================================================================
AUTHENTICATION & ACCESS MONITORING#
--Failed Login Attempts
SELECT sourceip, username, COUNT(*) as "Event Count"
FROM events
WHERE "Event Name" ILIKE '%failed%login%'
OR "Event Name" ILIKE '%authentication%failure%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, username
ORDER BY "Event Count" DESC
--Successful Logins After Failed Attempts
SELECT sourceip, username, starttime
FROM events
WHERE "Event Name" ILIKE '%successful%login%'
AND sourceip IN (
SELECT sourceip FROM events
WHERE "Event Name" ILIKE '%failed%login%'
AND starttime > LAST 1 HOURS
)
AND starttime > LAST 24 HOURS
--After-Hours Activity
SELECT username, sourceip, COUNT(*) as "Event Count"
FROM events
WHERE HOUROFDAY(starttime) NOT BETWEEN 8 AND 18
AND DAYOFWEEK(starttime) BETWEEN 2 AND 6
AND starttime > LAST 7 DAYS
GROUP BY username, sourceip
HAVING "Event Count" > 50
--Multiple Source IPs from Single User
SELECT username, COUNT(DISTINCT sourceip) as "Unique IPs"
FROM events
WHERE username IS NOT NULL
AND starttime > LAST 4 HOURS
GROUP BY username
HAVING COUNT(DISTINCT sourceip) > 3
--Account Lockouts
SELECT sourceip, username, COUNT(*) as "Lockout Count"
FROM events
WHERE "Event Name" ILIKE '%lockout%'
OR "Event Name" ILIKE '%locked%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, username
ORDER BY "Lockout Count" DESC
--Privileged Account Usage
SELECT username, sourceip, "Event Name", COUNT(*) as "Event Count"
FROM events
WHERE username IN ('Administrator', 'admin', 'root', 'sa')
AND starttime > LAST 24 HOURS
GROUP BY username, sourceip, "Event Name"
ORDER BY "Event Count" DESC
================================================================================
NETWORK TRAFFIC ANALYSIS#
--High Volume Traffic Sources SELECT sourceip, destinationip, SUM(bytessent) as "Total Bytes" FROM flows WHERE starttime > LAST 4 HOURS GROUP BY sourceip, destinationip HAVING SUM(bytessent) > 100000000 ORDER BY "Total Bytes" DESC --Unusual Port Activity SELECT sourceip, destinationport, COUNT(*) as "Event Count" FROM events WHERE destinationport NOT IN (21,22,23,25,53,80,110,143,443,993,995,3389) AND starttime > LAST 24 HOURS GROUP BY sourceip, destinationport ORDER BY "Event Count" DESC --Beaconing Detection (Regular Interval Connections) SELECT sourceip, destinationip, COUNT(*) as "Connections" FROM flows WHERE starttime > LAST 24 HOURS GROUP BY sourceip, destinationip HAVING COUNT(*) > 100 ORDER BY "Connections" DESC --Top Talkers by Bandwidth SELECT sourceip, SUM(bytessent + bytesreceived) as "Total Bytes" FROM flows WHERE starttime > LAST 24 HOURS GROUP BY sourceip ORDER BY "Total Bytes" DESC LIMIT 20 --External Communication SELECT sourceip, destinationip, destinationport, SUM(bytessent) as "Bytes Out" FROM flows WHERE destinationip NOT LIKE '10.%' AND destinationip NOT LIKE '192.168.%' AND destinationip NOT LIKE '172.16.%' AND destinationip NOT LIKE '172.17.%' AND destinationip NOT LIKE '172.18.%' AND destinationip NOT LIKE '172.19.%' AND destinationip NOT LIKE '172.2_.%' AND destinationip NOT LIKE '172.30.%' AND destinationip NOT LIKE '172.31.%' AND starttime > LAST 24 HOURS GROUP BY sourceip, destinationip, destinationport ORDER BY "Bytes Out" DESC ================================================================================
FIREWALL & PORT ANALYSIS#
--Blocked/Denied Traffic
SELECT sourceip, destinationip, destinationport, COUNT(*) as "Event Count"
FROM events
WHERE "Event Name" ILIKE '%block%'
OR "Event Name" ILIKE '%deny%'
OR "Event Name" ILIKE '%drop%'
AND destinationport IS NOT NULL
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, destinationport
ORDER BY "Event Count" DESC
--Port Scanning Detection
SELECT sourceip,
COUNT(DISTINCT destinationport) as "Unique Ports",
COUNT(DISTINCT destinationip) as "Unique Targets"
FROM events
WHERE starttime > LAST 1 HOURS
GROUP BY sourceip
HAVING COUNT(DISTINCT destinationport) > 20
OR COUNT(DISTINCT destinationip) > 10
ORDER BY "Unique Ports" DESC
--Uncommon Port Communications
SELECT sourceip, destinationip, destinationport, protocol, COUNT(*) as "Event Count"
FROM events
WHERE destinationport NOT IN (20,21,22,23,25,53,80,110,135,139,143,443,445,993,995,1433,3389,5985,5986)
AND ("Event Name" ILIKE '%accept%' OR "Event Name" ILIKE '%allow%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, destinationport, protocol
ORDER BY "Event Count" DESC
--Firewall Rule Hits Analysis
SELECT "Rule Name", sourceip, destinationip, destinationport, COUNT(*) as "Hits"
FROM events
WHERE devicetype = 'Firewall'
AND starttime > LAST 24 HOURS
GROUP BY "Rule Name", sourceip, destinationip, destinationport
ORDER BY "Hits" DESC
LIMIT 50
--Outbound Connection Attempts on Suspicious Ports
SELECT sourceip, destinationip, destinationport, COUNT(*) as "Attempts"
FROM flows
WHERE destinationport IN (4444, 5555, 6666, 7777, 8888, 9999, 1337, 31337)
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, destinationport
ORDER BY "Attempts" DESC
================================================================================
URL & WEB CONNECTION ANALYSIS#
--Suspicious URL Patterns
SELECT sourceip, url, "HTTP Method", "HTTP Response Code", COUNT(*) as "Count"
FROM events
WHERE devicetype IN ('Web Server', 'Proxy')
AND (url ILIKE '%admin%' OR url ILIKE '%login%' OR url ILIKE '%config%'
OR url ILIKE '%.jsp' OR url ILIKE '%.php' OR url ILIKE '%.asp'
OR url ILIKE '%shell%' OR url ILIKE '%cmd%' OR url ILIKE '%exec%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, url, "HTTP Method", "HTTP Response Code"
ORDER BY "Count" DESC
--External Domain Connections
SELECT sourceip, "HTTP Host", COUNT(DISTINCT url) as "Unique URLs", COUNT(*) as "Total"
FROM events
WHERE devicetype = 'Proxy'
AND "HTTP Host" NOT ILIKE '%.company.com'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "HTTP Host"
HAVING COUNT(*) > 10
ORDER BY "Total" DESC
--SQL Injection Attempts
SELECT sourceip, url, "User Agent", COUNT(*) as "Attempts"
FROM events
WHERE (url ILIKE '%union%select%'
OR url ILIKE '%drop%table%'
OR url ILIKE '%insert%into%'
OR url ILIKE '%1=1%'
OR url ILIKE '%or%1=1%'
OR url ILIKE '%''%'
OR url ILIKE '%<%script%'
OR url ILIKE '%--%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, url, "User Agent"
ORDER BY "Attempts" DESC
--Executable File Downloads
SELECT sourceip, url, "HTTP Response Code", bytesreceived, "User Agent"
FROM events
WHERE devicetype = 'Proxy'
AND (url ILIKE '%.exe' OR url ILIKE '%.zip' OR url ILIKE '%.rar'
OR url ILIKE '%.bat' OR url ILIKE '%.ps1' OR url ILIKE '%.dll'
OR url ILIKE '%.msi' OR url ILIKE '%.vbs' OR url ILIKE '%.js')
AND bytesreceived > 1000000
AND starttime > LAST 24 HOURS
ORDER BY bytesreceived DESC
--XSS Attempt Detection
SELECT sourceip, url, "User Agent", COUNT(*) as "Attempts"
FROM events
WHERE (url ILIKE '%<script%'
OR url ILIKE '%javascript:%'
OR url ILIKE '%onerror=%'
OR url ILIKE '%onload=%'
OR url ILIKE '%eval(%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, url, "User Agent"
ORDER BY "Attempts" DESC
================================================================================
MALWARE & SECURITY EVENTS#
--Antivirus/Malware Detections
SELECT sourceip, "Event Name", filename, COUNT(*) as "Detections"
FROM events
WHERE ("Event Name" ILIKE '%virus%'
OR "Event Name" ILIKE '%malware%'
OR "Event Name" ILIKE '%trojan%'
OR "Event Name" ILIKE '%ransomware%'
OR "Event Name" ILIKE '%worm%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "Event Name", filename
ORDER BY "Detections" DESC
--Suspicious Process Executions
SELECT sourceip, filename, "Process Name", username, starttime
FROM events
WHERE filename ILIKE '%.exe'
AND ("Process Name" ILIKE '%powershell%'
OR "Process Name" ILIKE '%cmd%'
OR "Process Name" ILIKE '%wscript%'
OR "Process Name" ILIKE '%cscript%'
OR "Process Name" ILIKE '%mshta%')
AND starttime > LAST 4 HOURS
--PowerShell Suspicious Activity
SELECT sourceip, username, "Command Line", "Process Name", COUNT(*) as "Count"
FROM events
WHERE ("Process Name" ILIKE '%powershell%' OR "Command Line" ILIKE '%powershell%')
AND ("Command Line" ILIKE '%invoke%'
OR "Command Line" ILIKE '%download%'
OR "Command Line" ILIKE '%base64%'
OR "Command Line" ILIKE '%encoded%'
OR "Command Line" ILIKE '%-enc%'
OR "Command Line" ILIKE '%bypass%'
OR "Command Line" ILIKE '%hidden%'
OR "Command Line" ILIKE '%iex%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, username, "Command Line", "Process Name"
--Scheduled Task Creation
SELECT sourceip, username, "Command Line", starttime
FROM events
WHERE ("Event Name" ILIKE '%scheduled%task%'
OR "Command Line" ILIKE '%schtasks%'
OR "Command Line" ILIKE '%at.exe%')
AND starttime > LAST 24 HOURS
ORDER BY starttime DESC
================================================================================
DNS ANALYSIS#
--DNS Tunneling Detection
SELECT sourceip, "DNS Query", COUNT(*) as "Query Count",
AVG(LENGTH("DNS Query")) as "Avg Query Length"
FROM events
WHERE "Event Name" ILIKE '%DNS%query%'
AND starttime > LAST 4 HOURS
GROUP BY sourceip, "DNS Query"
HAVING COUNT(*) > 100 OR AVG(LENGTH("DNS Query")) > 50
ORDER BY "Query Count" DESC
--Suspicious Domain Queries
SELECT sourceip, "DNS Query", COUNT(*) as "Query Count"
FROM events
WHERE "Event Name" ILIKE '%DNS%query%'
AND ("DNS Query" ILIKE '%.tk'
OR "DNS Query" ILIKE '%.ml'
OR "DNS Query" ILIKE '%.ga'
OR "DNS Query" ILIKE '%.cf'
OR "DNS Query" ILIKE '%duckdns%'
OR "DNS Query" ILIKE '%ngrok%'
OR "DNS Query" ILIKE '%pastebin%'
OR "DNS Query" ILIKE '%bit.ly%'
OR "DNS Query" ILIKE '%raw.githubusercontent%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "DNS Query"
ORDER BY "Query Count" DESC
--DNS Query Volume by Host
SELECT sourceip, COUNT(*) as "Total Queries",
COUNT(DISTINCT "DNS Query") as "Unique Domains"
FROM events
WHERE "Event Name" ILIKE '%DNS%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip
ORDER BY "Total Queries" DESC
LIMIT 20
--NXDOMAIN Responses (Failed DNS Lookups)
SELECT sourceip, "DNS Query", COUNT(*) as "Failed Queries"
FROM events
WHERE "Event Name" ILIKE '%NXDOMAIN%'
OR "Event Name" ILIKE '%DNS%fail%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "DNS Query"
HAVING COUNT(*) > 10
ORDER BY "Failed Queries" DESC
================================================================================
LATERAL MOVEMENT DETECTION#
--Admin Share Access (C$, ADMIN$, IPC$)
SELECT sourceip, destinationip, username, "Share Name", COUNT(*) as "Access Count"
FROM events
WHERE "Event Name" ILIKE '%share%access%'
AND ("Share Name" ILIKE '%ADMIN$%'
OR "Share Name" ILIKE '%C$%'
OR "Share Name" ILIKE '%IPC$%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, username, "Share Name"
ORDER BY "Access Count" DESC
--RDP Brute Force Detection
SELECT sourceip, destinationip, username, COUNT(*) as "Failed Attempts"
FROM events
WHERE destinationport = 3389
AND "Event Name" ILIKE '%fail%'
AND starttime > LAST 4 HOURS
GROUP BY sourceip, destinationip, username
HAVING COUNT(*) > 10
ORDER BY "Failed Attempts" DESC
--Privilege Escalation Events
SELECT sourceip, username, "Event Name", starttime
FROM events
WHERE "Event Name" ILIKE '%privilege%'
OR "Event Name" ILIKE '%elevated%'
OR "Event Name" ILIKE '%admin%added%'
OR "Event Name" ILIKE '%sudo%'
AND starttime > LAST 24 HOURS
ORDER BY starttime DESC
--PsExec/Remote Execution Detection
SELECT sourceip, destinationip, username, "Process Name", COUNT(*) as "Count"
FROM events
WHERE ("Process Name" ILIKE '%psexec%'
OR "Process Name" ILIKE '%wmic%'
OR "Process Name" ILIKE '%winrm%'
OR destinationport IN (445, 135, 5985, 5986))
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, username, "Process Name"
ORDER BY "Count" DESC
--Pass-the-Hash Detection
SELECT sourceip, destinationip, username, "Event Name", COUNT(*) as "Count"
FROM events
WHERE "Event Name" ILIKE '%NTLM%'
OR "Event Name" ILIKE '%pass%hash%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, username, "Event Name"
ORDER BY "Count" DESC
================================================================================
DATA EXFILTRATION DETECTION#
--Large Outbound Data Transfers
SELECT sourceip, destinationip, SUM(bytessent) as "Bytes Sent"
FROM flows
WHERE destinationip NOT LIKE '10.%'
AND destinationip NOT LIKE '192.168.%'
AND destinationip NOT LIKE '172.%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip
HAVING SUM(bytessent) > 50000000
ORDER BY "Bytes Sent" DESC
--Long Duration Connections
SELECT sourceip, destinationip, destinationport,
MIN(starttime) as "First Seen",
MAX(endtime) as "Last Seen"
FROM flows
WHERE starttime > LAST 7 DAYS
GROUP BY sourceip, destinationip, destinationport
ORDER BY MAX(endtime) - MIN(starttime) DESC
LIMIT 50
--USB Device Activity
SELECT sourceip, username, "Device Name", "Event Name", starttime
FROM events
WHERE "Event Name" ILIKE '%USB%'
OR "Event Name" ILIKE '%removable%'
AND starttime > LAST 24 HOURS
ORDER BY starttime DESC
--Cloud Storage Access
SELECT sourceip, username, url, "HTTP Host", COUNT(*) as "Access Count"
FROM events
WHERE ("HTTP Host" ILIKE '%dropbox%'
OR "HTTP Host" ILIKE '%drive.google%'
OR "HTTP Host" ILIKE '%onedrive%'
OR "HTTP Host" ILIKE '%box.com%'
OR "HTTP Host" ILIKE '%mega.nz%'
OR "HTTP Host" ILIKE '%wetransfer%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, username, url, "HTTP Host"
ORDER BY "Access Count" DESC
================================================================================
PROXY & SSL ANALYSIS#
--Proxy Connection Errors
SELECT sourceip, "HTTP Host", "HTTP Response Code", COUNT(*) as "Error Count"
FROM events
WHERE devicetype = 'Proxy'
AND ("HTTP Response Code" >= 400
OR "Event Name" ILIKE '%error%'
OR "Event Name" ILIKE '%timeout%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "HTTP Host", "HTTP Response Code"
ORDER BY "Error Count" DESC
--Proxy Bypass Attempts (Direct Internet Access)
SELECT sourceip, destinationip, destinationport, COUNT(*) as "Connections"
FROM flows
WHERE destinationport IN (80, 443, 8080, 8443)
AND destinationip NOT LIKE '10.%'
AND destinationip NOT LIKE '192.168.%'
AND destinationip NOT LIKE '172.%'
AND starttime > LAST 4 HOURS
GROUP BY sourceip, destinationip, destinationport
ORDER BY "Connections" DESC
--SSL/TLS Certificate Issues
SELECT sourceip, "HTTP Host", "Event Name", COUNT(*) as "Issues"
FROM events
WHERE ("Event Name" ILIKE '%ssl%' OR "Event Name" ILIKE '%tls%' OR "Event Name" ILIKE '%certificate%')
AND ("Event Name" ILIKE '%error%'
OR "Event Name" ILIKE '%invalid%'
OR "Event Name" ILIKE '%expired%'
OR "Event Name" ILIKE '%untrusted%'
OR "Event Name" ILIKE '%mismatch%')
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "HTTP Host", "Event Name"
ORDER BY "Issues" DESC
--User Agent Analysis
SELECT "User Agent", COUNT(DISTINCT sourceip) as "Unique IPs", COUNT(*) as "Requests"
FROM events
WHERE devicetype = 'Proxy'
AND "User Agent" IS NOT NULL
AND starttime > LAST 24 HOURS
GROUP BY "User Agent"
ORDER BY "Requests" DESC
LIMIT 50
================================================================================
HTTP RESPONSE CODE ANALYSIS#
--HTTP Status Code Distribution
SELECT "HTTP Response Code",
COUNT(*) as "Count",
CASE
WHEN "HTTP Response Code" BETWEEN 200 AND 299 THEN 'Success'
WHEN "HTTP Response Code" BETWEEN 300 AND 399 THEN 'Redirect'
WHEN "HTTP Response Code" BETWEEN 400 AND 499 THEN 'Client Error'
WHEN "HTTP Response Code" BETWEEN 500 AND 599 THEN 'Server Error'
ELSE 'Other'
END as "Category"
FROM events
WHERE devicetype IN ('Web Server', 'Proxy')
AND "HTTP Response Code" IS NOT NULL
AND starttime > LAST 24 HOURS
GROUP BY "HTTP Response Code"
ORDER BY "Count" DESC
--Client Errors by Source
SELECT sourceip, "HTTP Response Code", "HTTP Host", COUNT(*) as "Errors"
FROM events
WHERE "HTTP Response Code" BETWEEN 400 AND 499
AND starttime > LAST 24 HOURS
GROUP BY sourceip, "HTTP Response Code", "HTTP Host"
HAVING COUNT(*) > 10
ORDER BY "Errors" DESC
--Server Errors
SELECT "HTTP Host", "HTTP Response Code", url, COUNT(*) as "Errors"
FROM events
WHERE "HTTP Response Code" BETWEEN 500 AND 599
AND starttime > LAST 24 HOURS
GROUP BY "HTTP Host", "HTTP Response Code", url
ORDER BY "Errors" DESC
--401/403 Access Denied Analysis
SELECT sourceip, url, "HTTP Response Code", COUNT(*) as "Denied Count"
FROM events
WHERE "HTTP Response Code" IN (401, 403)
AND starttime > LAST 24 HOURS
GROUP BY sourceip, url, "HTTP Response Code"
ORDER BY "Denied Count" DESC
================================================================================
NETWORK TOPOLOGY & MAPPING#
--Service Port Distribution
SELECT destinationport,
CASE destinationport
WHEN 21 THEN 'FTP'
WHEN 22 THEN 'SSH'
WHEN 23 THEN 'Telnet'
WHEN 25 THEN 'SMTP'
WHEN 53 THEN 'DNS'
WHEN 80 THEN 'HTTP'
WHEN 110 THEN 'POP3'
WHEN 135 THEN 'RPC'
WHEN 139 THEN 'NetBIOS'
WHEN 143 THEN 'IMAP'
WHEN 389 THEN 'LDAP'
WHEN 443 THEN 'HTTPS'
WHEN 445 THEN 'SMB'
WHEN 993 THEN 'IMAPS'
WHEN 995 THEN 'POP3S'
WHEN 1433 THEN 'MSSQL'
WHEN 1521 THEN 'Oracle'
WHEN 3306 THEN 'MySQL'
WHEN 3389 THEN 'RDP'
WHEN 5432 THEN 'PostgreSQL'
WHEN 5985 THEN 'WinRM-HTTP'
WHEN 5986 THEN 'WinRM-HTTPS'
WHEN 8080 THEN 'HTTP-Proxy'
ELSE 'Other'
END as "Service",
COUNT(DISTINCT sourceip) as "Sources",
COUNT(DISTINCT destinationip) as "Destinations",
COUNT(*) as "Connections"
FROM flows
WHERE starttime > LAST 24 HOURS
GROUP BY destinationport
ORDER BY "Connections" DESC
--Cross-Segment Communication
SELECT sourceip, destinationip, destinationport, COUNT(*) as "Connections"
FROM flows
WHERE starttime > LAST 24 HOURS
AND destinationport IN (22, 135, 139, 389, 445, 1433, 3389, 5985, 5986)
GROUP BY sourceip, destinationip, destinationport
ORDER BY "Connections" DESC
--Internal to External Communication
SELECT sourceip, destinationip, destinationport, SUM(bytessent) as "Bytes Out"
FROM flows
WHERE (sourceip LIKE '10.%' OR sourceip LIKE '192.168.%' OR sourceip LIKE '172.%')
AND destinationip NOT LIKE '10.%'
AND destinationip NOT LIKE '192.168.%'
AND destinationip NOT LIKE '172.%'
AND starttime > LAST 24 HOURS
GROUP BY sourceip, destinationip, destinationport
ORDER BY "Bytes Out" DESC
================================================================================
QUICK REFERENCE#
OPERATORS#
= Equal != Not equal < Less than > Greater than <= Less than or equal >= Greater than or equal LIKE Pattern match (case sensitive) ILIKE Pattern match (case insensitive) IN Value in list NOT IN Value not in list BETWEEN Range IS NULL Null check IS NOT NULL Not null check AND Logical AND OR Logical OR
WILDCARDS#
% Match any characters (use with LIKE/ILIKE) _ Match single character
AGGREGATES#
COUNT(*) Count all rows COUNT(DISTINCT field) Count unique values SUM(field) Sum values AVG(field) Average MIN(field) Minimum MAX(field) Maximum
TIME FUNCTIONS#
HOUROFDAY(starttime) Hour (0-23) DAYOFWEEK(starttime) Day (1=Sun, 7=Sat) DAYOFMONTH(starttime) Day of month MONTH(starttime) Month (1-12) YEAR(starttime) Year
COMMON PORTS#
21 FTP 445 SMB 22 SSH 993 IMAPS 23 Telnet 995 POP3S 25 SMTP 1433 MSSQL 53 DNS 1521 Oracle 80 HTTP 3306 MySQL 110 POP3 3389 RDP 135 RPC 5432 PostgreSQL 139 NetBIOS 5985 WinRM-HTTP 143 IMAP 5986 WinRM-HTTPS 389 LDAP 8080 HTTP-Proxy 443 HTTPS 8443 HTTPS-Alt
PRIVATE IP RANGES#
10.0.0.0/8 10.x.x.x 172.16.0.0/12 172.16-31.x.x 192.168.0.0/16 192.168.x.x