DQL Cheat Sheet
  • 4 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

DQL Cheat Sheet

  • Dark
    Light
  • PDF

The DQL cheat sheet provides you with the most commonly used DQL and SQL use cases for your reference:

Use Case SQL DQL
Query all firewall events for SSH traffic assuming destination port is 22 SELECT * FROM FIREWALL WHERE $DstPort=22 stream=firewall where dstport=22
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' 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 8.8.8.8 or 1.1.1.1 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' or dstip='1.1.1.1')
Query all firewall events for SSH traffic assuming standard port is 23 and source country is ”US” SELECT * FROM FIREWALL WHERE $DstPort=23 AND $SrcCN="US" stream=firewall where dstport=23 and srccn='US'
Query DstCN of all firewall events SELECT $DstCN FROM FIREWALL stream=firewall|select dstcn
Query all firewall events and group it as per destination country SELECT $DstCN, COUNT(*) FROM FIREWALL GROUP BY $DstCN stream=firewall | groupby dstcn
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 last two 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 all firewall events that are grouped by destination ip address and determine the percentage and ratio of a destination port 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 occurred in a day and group it as per source country and 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 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 * 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 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) 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 and get the percentage of destination port 23 greater than 0 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 to retrieve the total count of events against the destination ip address 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 fetch the total count of destination Ip addresses grouped by country 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 grouped by user to retrieve all the length of the field value (in this case it is ‘System’) SELECT $System,LENGTH($System) AS systemlength FROM FIREWALL GROUP BY $System, systemlength Stream=FIREWALL | groupby user | select user, length(system)
Query all cloudtrail events to retrieve all fields that have amazonaws SELECT * FROM CLOUDTRAIL WHERE $UserAgent LIKE '%amazonaws%' stream=cloudtrail where useragent like "%amazonaws%"
Query all firewall events grouped by destination ip address to retrieve all destination ip address with distinct values and remove all duplicate values SELECT DISTINCT $DstIP FROM FIREWALL GROUP BY $DstIP stream=firewall |groupby dstip |select distinct(dstip)
Query all cloudtrail events to retrieve the values that match the specified regular expression pattern. (in this case useragent,”console.*”) SELECT * FROM CLOUDTRAIL WHERE $UserAgent REGEXP 'console.*' stream=cloudtrail where rlike(useragent, ‘console.*’)
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 * 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 firewall events grouped by destination country to retrieve the total sum of all or distinct values in an expression 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 maximum value in the selected group 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 minimum 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 average value in each group SELECT $DstCN, avg(evtlen) FROM FIREWALL GROUP BY $DstCN Stream=firewall | groupby dstcn | select dstcn, avg (evtlen)

Was this article helpful?