Reference
DQL CHEATSHEET
Easy to work with query samples for DQL. Use them as a starting point in building your queries.
Updated 2026-08-17
DQL CHEATSHEET
Easy to work with query samples for DQL. Use them as a starting point in building your queries.
| SQL | DQL | Description |
|---|---|---|
SELECT * FROM FIREWALL WHERE $DstPort=220% |
stream=firewall where dstport=22 |
Query all firewall events for SSH traffic assuming destination port is 22 |
SELECT * FROM FIREWALL WHERE $DstPort=53 AND $DstIP!='8.8.8.8' |
stream=firewall where dstport=53 and dstip!='8.8.8.8' |
Query all firewall events for SSH traffic assuming standard port is 53 and destination ip is not 8.8.8.8 |
SELECT * FROM FIREWALL WHERE $DstPort=53 AND ($DstIP='8.8.8.8' OR $DstIP='1.1.1.1') |
stream=firewall where dstport=53 and(dstip='8.8.8.8' ordstip='1.1.1.1') |
Query all firewall events for SSH traffic assuming standard port is 53 and destination ip is 8.8.8.8 or 1.1.1.1 |
SELECT * FROM FIREWALL WHERE $DstPort=23 AND $SrcCN="US" |
stream=firewall where dstport=23 and srccn='US' |
Query all firewall events for SSH traffic assuming standard port is 23 and source country is "US" |
SELECT $DstCN FROM FIREWALL |
stream=firewall | select dstcn |
Query DstCN of all firewall events |
SELECT $DstPort, $DstCN, COUNT(*) FROM FIREWALL Group By $DstPort, $DstCN LIMIT 10 |
stream=firewall | groupby dstport, dstcn | limit 10 |
Query top 10 firewall events and group it as per destination port and destination country |
SELECT $DstPort, $DstCN, COUNT(*) FROM FIREWALL Group By $DstPort, $DstCN LIMIT 10 |
stream=firewall | groupby dstport, dstcn | limit 10 |
Query top 10 firewall events and group it as per destination port and destination country |
SELECT $DstCN, $DstPort FROM FIREWALL LIMIT 2 |
stream=firewall | groupby dstport, dstcn | last 2 |
Query last two firewall events and group it as per destination port and destination country |
SELECT $DstIP, (CAST(SUM(CASE WHEN $DstPort=='23' THEN 1 ELSE 0 END) AS FLOAT)/CAST(COUNT(*) AS FLOAT))*100 AS percentagecount,(CAST(SUM(CASE WHEN $DstPort=='23' THEN 1 ELSE 0 END) AS FLOAT)/CAST(COUNT(*) AS FLOAT)) AS ratiocount FROM FIREWALL WHERE $Duration=1h GROUP BY $DstIP HAVING percentagecount>0 |
Stream=FIREWALL |groupby dstip |select dstip, percentage_of(dstport==23),ratio_of(dstport==23) |having percentage_of_col1>0 |
Query all firewall events that are grouped by destination ip address and determine the percentage and ratio of a destination port |
SELECT $SrcCN, $DstPort, COUNT(*) FROM FIREWALL WHERE $Duration=1d GROUP BY $SrcCN, $DstPort |
stream=firewall | groupby srccn,dstport| duration 1d |
Query all firewall events occurred in a day and group it as per source country and destination port |
SELECT * FROM FIREWALL WHERE $StartTime=2022-04-14T17:42:00 AND$EndTime=2022-06-02T18:13:32 AND $SrcIP="10.35.38.1" |
stream=firewall where srcip='10.35.38.1' | duration from 2021-05-20T00:00:00 to 2022-05-22T23:59:00 |
Query all firewall events for source ip address 10.35.38.1 occurred during the specific time period (in this case between 20th May 2021 and 22nd May 2022) |
SELECT $DstIP, CAST(SUM(CASE WHEN \$DstPort` == '23' THEN 1 ELSE 0 END) AS FLOAT)/CAST(COUNT(*) AS FLOAT) AS ratiocount FROM FIREWALL GROUP BY $DstIP HAVING ratiocount>0` |
Stream=FIREWALL | groupby dstip | select dstip, ratio_of(dstport==23) | having ratio_of_col1>0 |
Query all firewall events grouped by destination ip address to retrieve the ratio of destination port (23 in this case) |
SELECT $DstIP, (CAST(SUM(CASE WHEN $DstPort=='23' THEN 1 ELSE 0 END) AS FLOAT)/CAST(COUNT(*) AS FLOAT))*100 AS percentagecount, (CAST(SUM(CASE WHEN $DstPort=='23' THEN 1 ELSE 0 END) AS FLOAT)/CAST(COUNT(*) AS FLOAT)) AS ratiocount FROM FIREWALL WHERE $Duration=1h GROUP BY $DstIP HAVING percentagecount>0 |
Stream=FIREWALL | groupby dstip | select dstip,percentage_of(dstport==23) | having percentage_of_col1>0 |
Query all firewall events grouped by destination ip and get the percentage of destination port 23 greater than 0 |
SELECT $DstIP, count_if($DstPort==23) AS total_count FROM FIREWALL GROUP BY $DstIP HAVING total_count > 0 |
Stream=FIREWALL | groupby dstip | select dstip, count_if(dstport==23) | having count_if_col1>0 |
Query all firewall events to retrieve the total count of events against the destination ip address |
SELECT $DstCN, COUNT(DISTINCT $DstIP) AS totalcnt,COUNT(*) as total_count FROM FIREWALL GROUP BY $DstCN |
stream=firewall | groupby dstcn | select dstcn, distinct_count(dstip), COUNT(*) as total_count |
Query all firewall events to fetch the total count of destination Ip addresses grouped by country |
SELECT $System,LENGTH($System) AS systemlength FROM FIREWALL GROUP BY $System, systemlength |
Stream=FIREWALL | groupby user | select user, length(system) |
Query all firewall events grouped by user to retrieve all the length of the field value (in this case it is 'System') |
SELECT * FROM CLOUDTRAIL WHERE $UserAgent LIKE '%amazonaws%' |
stream=cloudtrail where useragent like "%amazonaws%" |
Query all cloudtrail events to retrieve all fields that have amazonaws |
SELECT DISTINCT $DstIP FROM FIREWALL GROUP BY $DstIP |
stream=firewall |groupby dstip |select distinct(dstip) |
Query all firewall events grouped by destination ip address to retrieve all destination ip address with distinct values and remove all duplicate values |
SELECT * FROM CLOUDTRAIL WHERE $UserAgent REGEXP 'console.*' |
stream=cloudtrail where rlike(useragent, 'console.*') |
Query all cloudtrail events to retrieve the values that match the specified regular expression pattern. (in this case useragent,"console.*") |
SELECT * FROM CLOUDTRAIL WHERE NOT $SrcIP="11.71.11.11" AND$SourceName="AWS-CLOUDTRAIL" |
stream=cloudtrail where sourcename='AWS-CLOUDTRAIL' and not srcip='11.71.11.11' |
Query all cloudtrail events to retrieve all fields with source name as AWS-CLOUDTRAIL and source ip address is not 11.71.11.11 |
SELECT $DstCN, sum(evtlen) FROM FIREWALL GROUP BY $DstCN |
Stream=firewall | groupby dstcn | select dstcn, sum (evtlen) |
Query all firewall events grouped by destination country to retrieve the total sum of all or distinct values in an expression |
SELECT $DstCN, max(evtlen) FROM FIREWALL GROUP BY $DstCN |
Stream=firewall | groupby dstcn | select dstcn, max (evtlen) |
Query all firewall events grouped by destination country to retrieve the maximum value in the selected group |
SELECT $DstCN, min(evtlen) FROM FIREWALL GROUP BY $DstCN |
Stream=firewall | groupby dstcn | select dstcn, min (evtlen) |
Query all firewall events grouped by destination country to retrieve the minimum value in the selected group |
SELECT $DstCN, avg(evtlen) FROM FIREWALL GROUP BY $DstCN |
Stream=firewall | groupby dstcn | select dstcn, avg (evtlen) |
Query all firewall events grouped by destination country to retrieve the average value in each group |
Related
Was this page helpful?