Sample SQL Query Text

Applies To: WatchGuard Advanced Reporting Tool

WatchGuard EPDR and WatchGuard EDR collect information and send it to the Advanced Visualization Tool, which organizes it into data tables. Each line of a table is an event monitored by WatchGuard EPDR or WatchGuard EDR.

The tables contain a series of specific fields, as well as common fields that appear in all of the tables and provide information such as when the event occurred, the computer where it was detected, the computer IP address, etc.

This topic provides recommendations on the data to monitor and sample SQL query text to filter the data in data tables.

To use sample SQL query text, in the Advanced Visualization Tool:

  1. Select Data SearchData search icon.
  2. Select the appropriate table for the time period you want.
    For example, to create a query to show remote desktop connections detected to or from an external IP address, select the oem.panda.paps.socket table.

Screen shot of the data table date selection dialog box

  1. In the toolbar, click Query Code Editor Query editor icon.
  2. Clear the existing query from the editor text box.
  3. Paste the sample code in the text box.
  4. Click Run.

See the relevant section for sample code:

Remote Desktop Connection (Port 3389) Detected To or From External IP

Remote Desktop Services that are open without adequate security measures are at a high risk of attack. Attackers take advantage of this through Brute Force attacks or they enter the network with stolen credentials. Many ransomware attacks start through open Remote Desktop Services.

We recommend that you set security measures to prevent attacks through these services.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket

where localPort = 3389,

ispublic(remoteIP)

Top 5 Data Volume Received by Applications in Bytes (1 Week)

When you keep track of traffic consumed by each application, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.processnetbytes

Sample Code

from oem.panda.paps.processnetbytes where endswith(path,".exe")

group every 30m by path

every 0

select peek(path, re("\\\\(\\w+.\\w+)$"), 0) as executable

select sum(bytesReceived) as bytesReceived,

bytesReceived > 1073741824 as `+1G`,

bytesReceived > 2147483648 as `+2G`,

bytesReceived > 3221225472 as `+3G`,

bytesReceived > 4294967296 as `+4G`,

bytesReceived > 5368709120 as `+5G`,

bytesReceived > 6442450944 as `+6G`,

bytesReceived > 7516192768 as `+7G`,

bytesReceived > 8589934592 as `+8G`,

bytesReceived > 9663676416 as `+9G`,

bytesReceived > 10737418240 as `+10G`

Top 5 Data Volume Sent by Applications in Bytes (1 Week)

When you keep track of traffic sent by application, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.processnetbytes

Sample Code

from oem.panda.paps.processnetbytes where endswith(path,".exe")

group every 30m by path

every 0

select peek(path, re("\\\\(\\w+.\\w+)$"), 0) as executable

select sum(bytesSent) as bytesSent,

bytesSent > 1073741824 as `+1G`,

bytesSent > 2147483648 as `+2G`,

bytesSent > 3221225472 as `+3G`,

bytesSent > 4294967296 as `+4G`,

bytesSent > 5368709120 as `+5G`,

bytesSent > 6442450944 as `+6G`,

bytesSent > 7516192768 as `+7G`,

bytesSent > 8589934592 as `+8G`,

bytesSent > 9663676416 as `+9G`,

bytesSent > 10737418240 as `+10G`

Top 5 Data Volume Received by Machine in Bytes (1 Week)

When you keep track of downloaded traffic by application, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.processnetbytes

Sample Code

from oem.panda.paps.processnetbytes

group every 30m by machineName

every 0

select sum(bytesReceived) as bytesReceived,

bytesReceived > 1073741824 as `+1G`,

bytesReceived > 2147483648 as `+2G`,

bytesReceived > 3221225472 as `+3G`,

bytesReceived > 4294967296 as `+4G`,

bytesReceived > 5368709120 as `+5G`,

bytesReceived > 6442450944 as `+6G`,

bytesReceived > 7516192768 as `+7G`,

bytesReceived > 8589934592 as `+8G`,

bytesReceived > 9663676416 as `+9G`,

bytesReceived > 10737418240 as `+10G`

Top 5 Data Volume Sent by Machine in Bytes (1 Week)

When you keep track of uploaded traffic by application, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.processnetbytes

Sample Code

from oem.panda.paps.processnetbytes

group every 30m by machineName

every 0

select sum(bytesSent) as bytesSent,

bytesSent > 1073741824 as `+1G`,

bytesSent > 2147483648 as `+2G`,

bytesSent > 3221225472 as `+3G`,

bytesSent > 4294967296 as `+4G`,

bytesSent > 5368709120 as `+5G`,

bytesSent > 6442450944 as `+6G`,

bytesSent > 7516192768 as `+7G`,

bytesSent > 8589934592 as `+8G`,

bytesSent > 9663676416 as `+9G`,

bytesSent > 10737418240 as `+10G`

Top 5 Data Volume Sent by User in Bytes (1 Week)

When you keep track of uploaded traffic by user, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.processnetbytes

Sample Code

from oem.panda.paps.processnetbytes

group every 30m by user

every 0

select sum(bytesSent) as bytesSent,

bytesSent > 1073741824 as `+1G`,

bytesSent > 2147483648 as `+2G`,

bytesSent > 3221225472 as `+3G`,

bytesSent > 4294967296 as `+4G`,

bytesSent > 5368709120 as `+5G`,

bytesSent > 6442450944 as `+6G`,

bytesSent > 7516192768 as `+7G`,

bytesSent > 8589934592 as `+8G`,

bytesSent > 9663676416 as `+9G`,

bytesSent > 10737418240 as `+10G`

Top 5 Data Volume Received by User in Bytes (1 Week)

When you keep track of downloaded traffic by user, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.processnetbytes

Sample Code

from oem.panda.paps.processnetbytes

group every 30m by user

every 0

select sum(bytesReceived) as bytesReceived,

bytesReceived > 1073741824 as `+1G`,

bytesReceived > 2147483648 as `+2G`,

bytesReceived > 3221225472 as `+3G`,

bytesReceived > 4294967296 as `+4G`,

bytesReceived > 5368709120 as `+5G`,

bytesReceived > 6442450944 as `+6G`,

bytesReceived > 7516192768 as `+7G`,

bytesReceived > 8589934592 as `+8G`,

bytesReceived > 9663676416 as `+9G`,

bytesReceived > 10737418240 as `+10G`

Top 5 TCP Communication Ports Used to Download from External IPs

When you keep track of TCP ports used for download from external IP addresses, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket

where ispublic(remoteIP)

group every 30m by protocol, localPort, direction

every 0

select count() as count,

count > 100 as `+100_times`,

count > 500 as `+500_times`,

count > 1000 as `+1000_times`,

count > 2000 as `+2000_times`,

count > 5000 as `+5000_times`,

count > 10000 as `+10000_times`

where protocol = "TCP"

where direction = "Down"

Top 5 TCP Communication Ports Used to Upload to External IPs

When you keep track of TCP ports used to upload to external IP addresses, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

We recommend that you monitor and set active alerts if processes consume more data than normal. This helps you to identify problems and act if needed. For more information, see Create Alerts in the Advanced Visualization Tool.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket

where ispublic(remoteIP)

group every 30m by protocol, localPort, direction

every 0

select count() as count,

count > 100 as `+100_times`,

count > 500 as `+500_times`,

count > 1000 as `+1000_times`,

count > 2000 as `+2000_times`,

count > 5000 as `+5000_times`,

count > 10000 as `+10000_times`

where protocol = "TCP"

where direction = "Up"

Top 5 UDP Communication Ports Used to Download from External IPs

When you keep track of UDP ports used to download from external IP addresses, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket

where ispublic(remoteIP)

group every 30m by protocol, localPort, direction

every 0

select count() as count,

count > 100 as `+100_times`,

count > 500 as `+500_times`,

count > 1000 as `+1000_times`,

count > 2000 as `+2000_times`,

count > 5000 as `+5000_times`,

count > 10000 as `+10000_times`

where protocol = "UDP"

where direction = "Down"

Top 5 UDP Communication Ports Used to Upload to External IPs

When you keep track of UDP ports used to upload to external IP addresses, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket

where ispublic(remoteIP)

group every 30m by protocol, localPort, direction

every 0

select count() as count,

count > 100 as `+100_times`,

count > 500 as `+500_times`,

count > 1000 as `+1000_times`,

count > 2000 as `+2000_times`,

count > 5000 as `+5000_times`,

count > 10000 as `+10000_times`

where protocol = "UDP"

where direction = "Up"

Top 10 Countries and Ports (Download 1 Week)

When you keep track of the top countries and ports used for downloads, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket where ispublic(remoteIP)

select mmcountry(remoteIP) as CC

where isnotnull(CC)

group every 30m by CC, localPort, protocol, direction

every 0

select count() as count,

count > 100 as `+100`,

count > 300 as `+300`,

count > 500 as `+500`,

count > 800 as `+800`,

count > 1000 as `+1000`,

count > 1500 as `+1500`,

count > 2000 as `+2000`,

count > 5000 as `+5000`,

count > 10000 as `+10000`,

count > 15000 as `+15000`,

count > 20000 as `+20000`

where direction = "Down"

Top 10 Countries and Ports (Upload 1 Week)

When you keep track of the top countries and ports used for uploads, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.socket

Sample Code

from oem.panda.paps.socket where ispublic(remoteIP)

select mmcountry(remoteIP) as CC

where isnotnull(CC)

group every 30m by CC, localPort, protocol, direction

every 0

select count() as count,

count > 1 as `+1`,

count > 50 as `+50`,

count > 100 as `+100`,

count > 300 as `+300`,

count > 500 as `+500`,

count > 800 as `+800`,

count > 1000 as `+1000`,

count > 1500 as `+1500`,

count > 2000 as `+2000`,

count > 5000 as `+5000`,

count > 10000 as `+10000`,

count > 15000 as `+15000`,

count > 20000 as `+20000`

where direction = "Up"

Total Download Count of Executable Files (.EXE in 1 Week)

When you keep track of the top executable files that users download, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.urldownload

Sample Code

from oem.panda.paps.urldownload

where endswith(url, ".exe")

group every 30m

every 0

select count() as count

Total Download Count of Compressed Format Files (.ZIP, .RAR , .7Z in 1 Week)

When you keep track of the top compressed files that users download, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.urldownload

Sample Code

from oem.panda.paps.urldownload

where has(url, ".zip", ".rar", ".7z")

group every 30m

every 0

select count() as count

Total Download Count of Office Documents (.DOC* , .XLS* , .PPT* , .OCT)

When you keep track of the top Office document file types that users download, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.urldownload

Sample Code

from oem.panda.paps.urldownload

where has(url, ".doc*", ".xls*", ".ppt*" , ".oct")

group every 30m

every 0

select count() as count

Torrent Activity Detected

When you keep track of user Torrent activity, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.urldownload

Sample Code

from oem.panda.paps.urldownload

where url -> "torrent"

group every 30m

every 0

select count() as count

Malware and Potential Unwanted Programs (PUP) in Numbers (1 Week)

When you keep track of the malware and PUPs alerts each week, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.alert

Sample Code

from oem.panda.paps.alert

group every 30m by alertType, executionStatus

every 0

select count() as count

Vulnerable Applications or Outdated Software Executed

When you keep track of the vulnerable applications or outdated software that users use, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.ops

Sample Code

from oem.panda.paps.ops where isnotnull(ocsVer), endswith(childPath, ".exe") select subs(childPath, re(".*\\\\"), template("")) as executablename, lower(executablename) as executablename2, split(executablename2, ".exe", 0) as executable

group every 30m

every 0

select count() as count

Number of Possible Vulnerable Applications Detected

When you keep track of the possible vulnerable applications used, it helps you to quickly identify misuse, application errors, possible data exfiltration, and more.

Table

oem.panda.paps.vulnerableappsfound

Sample Code

from oem.panda.paps.vulnerableappsfound

group every 30m by companyName

every 0

select count() as count

Related Topics

Data Search in the Advanced Visualization Tool

Create a Search Query