Hello. How can we help you?

DQL CHEATSHEET


Easy to work with query samples for DQL. Use them as a starting point in building your queries.

SQLSELECT * FROM FIREWALL WHERE $DstPort=220%
DQLstream=firewall where dstport=22
 Query all firewall events for SSH traffic assuming destination port is 22
  
SQLSELECT * FROM FIREWALL WHERE $DstPort=53 AND $DstIP!='8.8.8.8'
DQLstream=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
  
SQLSELECT * FROM FIREWALL WHERE $DstPort=53 AND 
($DstIP='8.8.8.8' OR $DstIP='1.1.1.1')
DQLstream=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 53 and destination ip is 8.8.8.8 or 1.1.1.1
  
SQLSELECT * FROM FIREWALL WHERE $DstPort=23 AND $SrcCN="US"
DQLstream=firewall where dstport=23 and srccn='US'
 Query all firewall events for SSH traffic assuming standard port is 23 and source country is ”US”
  
SQLSELECT $DstCN FROM FIREWALL
DQLstream=firewall | select dstcn
 Query DstCN of all firewall events 
  
SQLSELECT $DstPort, $DstCN, COUNT(*) FROM FIREWALL Group By $DstPort, $DstCN LIMIT 10
DQLstream=firewall | groupby dstport, dstcn | limit 10
 Query top 10 firewall events and group it as per destination port and
destination country
  
SQLSELECT $DstPort, $DstCN, COUNT(*) FROM FIREWALL Group By $DstPort, $DstCN LIMIT 10
DQLstream=firewall | groupby dstport, dstcn | limit 10
 Query top 10 firewall events and group it as per destination port and
destination country
  
SQLSELECT $DstCN, $DstPort FROM FIREWALL LIMIT 2
DQLstream=firewall | groupby dstport, dstcn | last 2
 Query last two firewall events and group it as per destination port and destination country
  
SQLSELECT $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
DQLStream=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
  
SQLSELECT $SrcCN, $DstPort, COUNT(*) FROM FIREWALL WHERE $Duration=1d GROUP BY $SrcCN, $DstPort
DQLstream=firewall | groupby srccn,dstport| duration 1d
 Query all firewall events occurred in a day and group it as per source country and destination port
  
SQLSELECT * FROM FIREWALL WHERE $StartTime=2022-04-14T17:42:00 AND$EndTime=2022-06-02T18:13:32 AND $SrcIP="10.35.38.1"
DQLstream=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)
  
SQLSELECT $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
DQLStream=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)
  
SQLSELECT $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
DQLStream=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
  
SQLSELECT $DstIP, count_if($DstPort==23) AS total_count FROM FIREWALL GROUP BY $DstIP HAVING total_count > 0
DQLStream=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
  
SQLSELECT $DstCN, COUNT(DISTINCT $DstIP) AS totalcnt,COUNT(*) as total_count FROM FIREWALL GROUP BY $DstCN
DQLstream=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
  
SQLSELECT $System,LENGTH($System) AS systemlength FROM FIREWALL GROUP BY $System, systemlength
DQLStream=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’)
  
SQLSELECT * FROM CLOUDTRAIL WHERE $UserAgent LIKE '%amazonaws%'
DQLstream=cloudtrail where useragent like "%amazonaws%"
 Query all cloudtrail events to retrieve all fields that have amazonaws
  
SQLSELECT DISTINCT $DstIP FROM FIREWALL GROUP BY $DstIP 
DQLstream=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
  
SQLSELECT * FROM CLOUDTRAIL WHERE $UserAgent REGEXP 'console.*'
DQLstream=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.*”)
  
SQLSELECT * FROM CLOUDTRAIL WHERE NOT $SrcIP="11.71.11.11" AND$SourceName="AWS-CLOUDTRAIL"
DQLstream=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
  
SQLSELECT $DstCN, sum(evtlen) FROM FIREWALL GROUP BY $DstCN
DQLStream=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
  
SQLSELECT $DstCN, max(evtlen) FROM FIREWALL GROUP BY $DstCN
DQLStream=firewall | groupby dstcn | select dstcn, max (evtlen)
 Query all firewall events grouped by destination country to retrieve the maximum value in the selected group
  
SQLSELECT $DstCN, min(evtlen) FROM FIREWALL GROUP BY $DstCN
DQLStream=firewall | groupby dstcn | select dstcn, min (evtlen)
 Query all firewall events grouped by destination country to retrieve the
minimum value in the selected group
  
SQLSELECT $DstCN, avg(evtlen) FROM FIREWALL GROUP BY $DstCN
DQLStream=firewall | groupby dstcn | select dstcn, avg (evtlen)
 Query all firewall events grouped by destination country to retrieve the
average value in each group