Zeek 로그를 기반으로 가능한 위협헌팅 대시보드를 다수 만든 앱
상당히 참고할만한 쿼리문이 많았다. 이에 쿼리문 상당수를 추출해 정리했다.
DNS Insights
Anomalies :: DGA Pattern in Requests
description : insights on the dns query requests with potential DGA (domain generating algorithm) pattern
Domain Name Entropy [ Based on Shannon Algorithm ]
index=zeek sourcetype=*dns* NOT query IN (*arpa,*.local)
| eval queryLength=len(query)
| stats count by queryLength, query
| where queryLength>20
| eval list="mozilla"
| `ut_parse_extended(query, list)`
| `ut_shannon(query)`
| eval ut_shannon1 = ut_shannon
| eval subdomain1= mvindex(split(query,"."),0)
| `ut_shannon(subdomain1)`
| eval ut_shannon2 = ut_shannon
| table ut_domain query subdomain1 queryLength ut_shannon1 ut_shannon2
| eval ut_shannon1= round(ut_shannon1,2)
| eval ut_shannon2= round(ut_shannon2,2)
| sort - ut_shannon1 ut_shannon2
| rename query as "DNS Query" ut_shannon1 as "Entropy Score for full query" subdomain1 as "DNS Subdomain" ut_shannon2 as "Entropy Score for Sub-Domain"
| fields - ut_domain queryLength
SQL
복사
index=zeek sourcetype=*dns* NOT query IN (*arpa,*.local) query="*.*"
NOT [|inputlookup whitelist.csv |fields src_ip]
NOT [|inputlookup random_dns.csv |fields query]
NOT [|inputlookup query_fam_used.csv |fields query]
| eval queryLength=len(query)
| stats count by queryLength, query
| eval list="mozilla"
| `ut_parse_extended(query, list)`
| `ut_shannon(query)`
| eval ut_shannon1 = ut_shannon
| eval subdomain1= mvindex(split(query,"."),0)
| `ut_shannon(subdomain1)`
| eval ut_shannon2 = ut_shannon
| table ut_domain query subdomain1 queryLength ut_shannon1 ut_shannon2
| eval ut_shannon1= round(ut_shannon1,2)
| eval ut_shannon2= round(ut_shannon2,2)
| sort - ut_shannon1 ut_shannon2
| rename query as "DNS Query" ut_shannon1 as "Entropy Score for full query" subdomain1 as "DNS Subdomain" ut_shannon2 as "Entropy Score for Sub-Domain"
| fields - ut_domain queryLength
SQL
복사
Lengthy Domain name requests
index=zeek sourcetype=bro:dns:json NOT query IN (*arpa,*.local)
| eval queryLength=len(query)
| stats count by queryLength, query
| where queryLength>20
| eval list="mozilla"
| `ut_parse_extended(query, list)`
| search NOT
[| inputlookup subdomainlookup.csv
| rename domain as ut_domain ]
| sort -queryLength, count
| table query queryLength count
| rename query as "DNS Query" queryLength as Length count as Occurrence
SQL
복사
Anomalies :: Excessive Sub-Domains
description : insights on high number of sub-domain related to main domain name [Potential data exfiltration ]
Excessive Subdomains in the DNS requests [Potential Data Exfil]
eventtype=zeek_idx_sourcetype NOT query IN (*.arpa)
| eval list="mozilla"
| `ut_parse_extended(query, list)`
| stats dc(ut_subdomain) as qty BY ut_domain
| where qty > 2
| search NOT
[| inputlookup subdomainlookup.csv
| rename domain as ut_domain ]
| head 25
| rename ut_domain as "Domain Name"
| sort - qty
SQL
복사
Anomalies :: Newly Seen Domain Names
description : insights on the newly requested domain names within the past 24hrs against historical DNS requests (7 days/14 days/ 30 days /etc.)
Newly seen Domain Name in requests
index=zeek sourcetype=*dns NOT query IN (*arpa,*.local) earliest=$tokenbaseline$ latest=now()
| eval list="mozilla"
| `ut_parse_extended(query, list)`
| stats earliest(_time) AS "firstseen" latest(_time) AS "lastseen" BY ut_domain
| eval newlyseen=if(firstseen >= relative_time(now(), "-1d@d"), 1, 0)
| table ut_domain lastseen firstseen newlyseen
| convert ctime(firstseen) ctime(lastseen)
| rename ut_domain as "Domain Name" firstseen as "First Seen" lastseen as "Last Seen"
| where newlyseen=1
| fields - newlyseen "First Seen" "Last Seen"
SQL
복사
Anomalies :: NXDomain in Respones
description : insights on the 'NXDOMAIN' flagged dns response
Filter based on TLD
Response distribution (Note : Filter Applied Here)
index=zeek sourcetype=*dns* NOT query IN (*.arpa,*-guest,local) query IN (*.*) query =*
| stats count by rcode_name
SQL
복사
NXDOMAIN : Source IP
eventtype=zeek_idx_sourcetype sourcetype="*dns*" NOT query IN (ISATAP,WORKGROUP) query=*$tokenTLD$ rcode_name IN (NXDOMAIN)
| bin span=1d _time
| table _time id.orig_h id.orig_p id.resp_h id.resp_p query query_type rcode_name
| timechart span=1d count by id.orig_h
SQL
복사
Top Level Domain : NXDOMIN
index=zeek sourcetype=bro:dns:json NOT query IN (*.arpa,*-guest,local) query IN (*.*) NOT rcode_name = NOERROR
| eval list="mozilla"
| `ut_parse_extended(query, list)`
| stats dc(ut_subdomain) as qty BY ut_domain
| rename ut_domain as "Domain Name"
| sort - qty
SQL
복사
Sub Domain : NXDOMAIN (Note : Filter Applied Here)
index=zeek sourcetype=bro:dns:json NOT query IN (*.arpa,*-guest,local) query IN (*.*) NOT rcode_name = NOERROR query =*$tokenTLD$
| stats count by query
| sort -count
SQL
복사
Analytics :: LFO/MFO Frequency
description : insights on frequency based analysis / stack counting for the domain names
Top Requested Domains (known domain excluded)
index=zeek sourcetype="*dns*"
| eventstats count AS total
| eval list="mozilla"
| lookup ut_parse_extended_lookup url as query list as list
| spath input=ut_subdomain_parts
| fields - ut_subdomain_parts
| stats count by ut_domain total
| eval percentage=round((count/total)*100,4)
| sort percentage
| search NOT ut_domain IN (none,*.arpa,google.com,kaspersky.com,microsoft.com)
| head 20
| table ut_domain percentage
SQL
복사
Least Requested Domains (LFO : Least Frequency of Occurrence)
index=zeek sourcetype="*dns*"
| eventstats count AS total
| eval list="mozilla"
| lookup ut_parse_extended_lookup url as query list as list
| spath input=ut_subdomain_parts
| fields - ut_subdomain_parts
| stats count by ut_domain total
| eval percentage=round((count/total)*100,1)
| sort +percentage
| search NOT ut_domain IN (none,*.arpa)
| head 20
| table ut_domain percentage
| rename ut_domain as "Domain Name" percentage as "%"
| sort "%"
SQL
복사
Top Requested Domains (MFO: : Most Frequency of Occurrence) (known domain excluded)
index=zeek sourcetype="*dns*"
| eventstats count AS total
| eval list="mozilla"
| lookup ut_parse_extended_lookup url as query list as list
| spath input=ut_subdomain_parts
| fields - ut_subdomain_parts
| stats count by ut_domain total
| eval percentage=round((count/total)*100,1)
| sort - percentage
| search NOT ut_domain IN (none,*.arpa)
| head 20
| table ut_domain percentage
| rename ut_domain as "Domain Name" percentage as "%"
SQL
복사
File Insights
Anomalies :: Watering-Hole Pattern
description : insights on watering-hole pattern in file download
File Downloads [ only Executable]
index=zeek sourcetype="*http*" resp_mime_types IN ("application/x-dosexec","application/x-debian-package","application/x-gzip","application/msword","application/zip")
| eval a=host
| eval web_url=mvindex(a,-1)
| eval uri_filename=mvindex(split(url,"/"),-1)
| search uri_filename=*.*
| table uid user_agent uri method web_url resp_mime_types uri_filename id.orig_h
| stats values(id.orig_h) as source dc(id.orig_h) as count by uri_filename
| rename uri_filename as "File Name"
| where count>1
SQL
복사
DHCP Insights
Anomalies :: MAC Spoofing Attempts
description : insights on MAC address spoofing
Potential MAC Address Spoofing Attempts
same machine with more than one mac address is may be due to running VM inside the endpoint
index=zeek sourcetype=*dhcp*
| table _time host_name client_addr mac msg_types*
| stats values(mac) as "MAC Address" dc(mac) as dc_count by host_name
| where dc_count>1
| rename host_name as Hostname
| fields "MAC Address" Hostname
SQL
복사
Anomalies :: Suspicious Hostnames
description : insights on the suspicious endpoints requesting for dhcp IP address
Weird Hostnames observed in DHCP requests
Computer Hostname pattern other than normal naming convention used in an Enterprise
index=zeek sourcetype=*dhcp*
| dedup host_name
| search NOT host_name IN (*-*,".")
| eval host_name=upper(host_name)
| rename mac as "MAC Address" host_name as Hostname
| table Hostname "MAC Address"
SQL
복사
SSH Insights
External :: SSH Outgoing to Internet
description : insights on the SSH outgoing connection from internal to external
Connection to External [ with data transfer]
index=zeek sourcetype IN (*conn*) id.resp_p =22 NOT id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.orig_p id.resp_h id.resp_p sourcetype orig_bytes resp_bytes conn_state
| join uid type=left
[ search eventtype=zeek_idx_sourcetype sourcetype IN (*ssh*) NOT id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.resp_h id.resp_p auth_success cipher_alg client server direction host_key_alg host_key kex_alg mac_alg version auth_attempts
| table _time uid auth_success auth_attempts direction client server]
| eval total_bytes=orig_bytes+ resp_bytes
| search total_bytes > 0
| fields - sourcetype id.orig_p id.resp_p total_bytes server
| fillnull value="-"
SQL
복사
Connection to External [ without data transfer]
index=zeek IN (*conn*) id.resp_p =22 NOT id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.orig_p id.resp_h id.resp_p sourcetype orig_bytes resp_bytes conn_state
| join uid type=left
[ search eventtype=zeek_idx_sourcetype sourcetype IN (bro:ssh:json) NOT id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.resp_h id.resp_p auth_success cipher_alg client server direction host_key_alg host_key kex_alg mac_alg version auth_attempts
| table _time uid auth_success auth_attempts direction client server]
| eval total_bytes=orig_bytes+ resp_bytes
| fillnull value=0 total_bytes
| search total_bytes=0
| fields - sourcetype id.orig_p id.resp_p total_bytes server
| fillnull value="-"
SQL
복사
External :: SSH Incoming from Internet
description : insights on the SSH outgoing connection from internet to internal
Connections with Data Transfer [ From Internet ]
index=zeek sourcetype IN (*conn*) id.resp_p =22 id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) NOT id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.orig_p id.resp_h id.resp_p sourcetype orig_bytes resp_bytes conn_state
| join uid type=left
[ search eventtype=zeek_idx_sourcetype sourcetype IN (*ssh*) id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) NOT id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.resp_h id.resp_p auth_success cipher_alg client server direction host_key_alg host_key kex_alg mac_alg version auth_attempts
| table _time uid auth_success auth_attempts direction client server]
| eval total_bytes=orig_bytes+ resp_bytes
| search total_bytes > 0
| fields - sourcetype id.orig_p id.resp_p total_bytes
| fillnull value="-"
SQL
복사
Connections without Data Transfer [ From Internet ]
index=zeek sourcetype IN (*conn*) id.resp_p =22 id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) NOT id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.orig_p id.resp_h id.resp_p sourcetype orig_bytes resp_bytes conn_state
| join uid type=left
[ search eventtype=zeek_idx_sourcetype sourcetype IN (*ssh*) id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) NOT id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.resp_h id.resp_p auth_success cipher_alg client server direction host_key_alg host_key kex_alg mac_alg version auth_attempts
| table _time uid auth_success auth_attempts direction client server]
| eval total_bytes=orig_bytes+ resp_bytes
| fillnull value=0 total_bytes
| search total_bytes=0
| fields - sourcetype id.orig_p id.resp_p total_bytes
| fillnull value="-"
SQL
복사
SSH Access [ From Internet ]
index=zeek sourcetype IN (*conn*) id.resp_p =22 id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) NOT id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.orig_p id.resp_h id.resp_p sourcetype orig_bytes resp_bytes conn_state
| join uid type=left
[ search eventtype=zeek_idx_sourcetype sourcetype IN (*ssh*) id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) NOT id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| table _time uid id.orig_h id.resp_h id.resp_p auth_success cipher_alg client server direction host_key_alg host_key kex_alg mac_alg version auth_attempts
| table _time uid auth_success auth_attempts direction client server]
| eval total_bytes=orig_bytes+ resp_bytes
| search total_bytes > 0
| fields - sourcetype id.orig_p id.resp_p total_bytes | fillnull value="-"
SQL
복사
Internal :: SSH communications
description : SSH traffic within the organization [Private |P Segments only]
Internal SSH Connections
index=zeek sourcetype IN (*conn*) id.resp_p =22 id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) id.orig_h=* id.resp_h=*
| table _time uid id.orig_h id.orig_p id.resp_h id.resp_p sourcetype orig_bytes resp_bytes conn_state
| join uid type=left
[ search eventtype=zeek_idx_sourcetype sourcetype IN (*ssh*) id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) id.orig_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16) id.orig_h=* id.resp_h=*
| table _time uid id.orig_h id.resp_h id.resp_p auth_success cipher_alg client server direction host_key_alg host_key kex_alg mac_alg version auth_attempts
| table _time uid auth_success auth_attempts direction client server]
| eval total_bytes=orig_bytes+ resp_bytes
| search total_bytes > 0
| fields - sourcetype id.orig_p id.resp_p total_bytes
| fillnull value="-"
SQL
복사
TLS/SSL Insights
Anomalies :: Conn using Expired Certs
description : insights on outgoing connection with expired TLS certificates
Expired Cert used in the TLS outgoing connections
index=zeek sourcetype="*ssl*" validation_status IN ("certificate has expired")
NOT dest_ip IN ("8.43.72.97","8.43.72.98")
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| fillnull value=unknown
| dedup server_name
| iplocation dest_ip
| search NOT server_name IN (*apple*)
| table server_name dest_ip validation_status issuer Country
| fields - issuer
| rename server_name as "Domain Name" dest_ip as "IP Address"
SQL
복사
Anomalies :: Conn using Self Signed Certs
description : TLS self signed certificate usage on outgoing traffic
Outgoing Connections [ Non Web Ports ]
index=zeek sourcetype="*ssl*" validation_status IN ("self signed certificate in certificate chain","self signed certificate") id.resp_p!=443 NOT id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| fillnull value=unknown
| dedup server_name
| iplocation dest_ip
| search NOT server_name IN (*apple*)
| table server_name dest_ip validation_status issuer Country id.resp_p
| fields - issuer
| rename server_name as "Domain Name" dest_ip as "IP Address" validation_status as cert_details | sort cert_details
SQL
복사
Internal Connections [ Non Web Ports ]
index=zeek sourcetype="*ssl*" validation_status IN ("self signed certificate in certificate chain","self signed certificate") id.resp_p!=443 id.resp_h IN (10.0.0.0/8,172.16.0.0/12,192.168.0.0/16)
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| fillnull value=unknown
| dedup server_name
| iplocation dest_ip
| search NOT server_name IN (*apple*)
| table server_name dest_ip validation_status issuer id.resp_p
| fields - issuer
| rename server_name as "Domain Name" dest_ip as "IP Address" validation_status as cert_details| sort cert_details
SQL
복사
Outgoing Connections [Web Ports ] - Geolocations (Pie Charts)
index=zeek sourcetype="*ssl*" validation_status IN ("self signed certificate in certificate chain","self signed certificate") id.resp_p=443
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| fillnull value=unknown
| dedup server_name
| iplocation dest_ip
| search NOT server_name IN (*apple*)
| table server_name dest_ip validation_status issuer Country Org id.resp_p
| fields - issuer
| rename server_name as "Domain Name" dest_ip as "IP Address"
| stats count by Country
SQL
복사
Outgoing Connections [Web Ports ]
index=zeek sourcetype="*ssl*" validation_status IN ("self signed certificate in certificate chain","self signed certificate") id.resp_p=443
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| fillnull value=unknown
| dedup server_name
| iplocation dest_ip
| search NOT server_name IN (*apple*)
| table server_name dest_ip validation_status issuer Country id.resp_p
| fields - issuer
| rename server_name as "Domain Name" dest_ip as "IP Address" validation_status as cert_details
| sort cert_details
SQL
복사
Anomalies :: Now Born Cert in traffic
description : insights on the TLS certificate aging (no of days) and the provider details
New Born TLS certificates [age < 30 days]
Let's Encrypt Certs
index=zeek sourcetype="*x509*"
| dedup certificate.serial
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| rename certificate.not_valid_after as expiry certificate.not_valid_before as issue
| eval c_time = now()
| eval days = round((c_time - issue)/86400,0)
| search days<30 Org="Let's Encrypt"
| convert ctime(issue)
| table _time c_time issue expiry days certificate.serial certificate.issuer Org
| fields - expiry _time c_time certificate.issuer issue
| sort days
SQL
복사
New Born TLS certificates [age < 30 days]
Other Certs
index=zeek sourcetype="*x509*"
| dedup certificate.serial
| rex "O=(?<Org>[^,]+)"
| eval Org= mvindex(split(mvindex(split(Org,","),0),"\\"),0)
| rename certificate.not_valid_after as expiry certificate.not_valid_before as issue
| eval c_time = now()
| eval days = round((c_time - issue)/86400,0)
| search days<30 Org="Let's Encrypt"
| convert ctime(issue)
| table _time c_time issue expiry days certificate.serial certificate.issuer Org
| fields - expiry _time c_time certificate.issuer issue
| sort days
SQL
복사
