For query assistance, please see the following Best Practices guide


I am adding a set of queries to explore information in the data lake from the XG Firewall.

For the data lake to have information from the XG Firewall you will need to have an XG FW License with Cloud Firewall reporting enabled.

Once the firewall is configured with CFR enabled and management from Central you will have a new table in the data lake XGFW_data.

To list information from the firewall you can run this simple query from the test tool

-- List ALL XG FW Tables
SELECT DISTINCT log_type, log_component, COUNT(dist_key) entries 
FROM xgfw_data 
GROUP BY log_type, log_component 
ORDER By log_type, log_component ASC

You should get a result like the following.  You have data in the data lake.  Next onto what we can do with that data.

+---------------+--------------------+-----------+
| log_type      | log_component      |   entries |
|---------------+--------------------+-----------|
| Anti-Spam     | SMTP               |         6 |
| Event         | AD SSO             |         6 |
| Event         | ATP                |         1 |
| Event         | Anti-Virus         |        32 |
| Event         | Appliance          |         4 |
| Event         | CLI                |         2 |
| Event         | Central Management |        15 |
| Event         | Gateway            |         3 |
| Event         | IPS                |         2 |
| Event         | Interface          |        65 |
| Event         | Up2Date            |         6 |
| Firewall      | Appliance Access   |     29019 |
| System Health | CPU                |       864 |
| System Health | Disk               |       861 |
| System Health | Interface          |      4305 |
| System Health | Live User          |       861 |
| System Health | Memory             |       861 |
+---------------+--------------------+-----------+

So very much like the endpoint information in the data lake the firewall data has been consolidated into a single table and to view information associated with a log component of the firewall you will want to specify it as a constraint on the where clause.  

For example to view the information in the Anti-Spam logs you can run this.

-- Anti-Spam - SMTP INFO
SELECT 
   -- Device ID DETAILS
   device_name, device_serial_id, log_type, log_component, severity,

   -- Query Details
   src_ip, src_country, src_port, protocol, policy_name, action, source_file_name, subject, sender, recipient, message_id, email_size, quarantine_reason, hits,

   -- META information Common for all queries
   dist_key, ingestion_time, asset_id, timestamp, device_model, log_id, log_subtype, log_version, customer_id

FROM xgfw_data
WHERE log_component = 'SMTP'

You should get a result like the following.

+---------------+--------------------+------------+-----------------+-------------+-----------+---------------+------------+------------+---------------+----------+--------------------------------------------+------------------------------------------------------------------+---------------------------+---------------------------+-----------------------------+--------------+---------------------+--------+--------------------------------------------------------+--------------------------+--------------------------------------+----------------------+----------------+--------------+---------------+---------------+--------------------------------------+
| device_name   | device_serial_id   | log_type   | log_component   | severity    | src_ip    | src_country   |   src_port | protocol   | policy_name   | action   | source_file_name                           | subject                                                          | sender                    | recipient                 | message_id                  |   email_size | quarantine_reason   |   hits | dist_key                                               | ingestion_time           | asset_id                             | timestamp            | device_model   |       log_id | log_subtype   |   log_version | customer_id                          |
|---------------+--------------------+------------+-----------------+-------------+-----------+---------------+------------+------------+---------------+----------+--------------------------------------------+------------------------------------------------------------------+---------------------------+---------------------------+-----------------------------+--------------+---------------------+--------+--------------------------------------------------------+--------------------------+--------------------------------------+----------------------+----------------+--------------+---------------+---------------+--------------------------------------|
|               | C01001JVB4DH41B    | Anti-Spam  | SMTP            | Information | 127.0.0.1 |               |      51532 | TCP        | None          | QUEUED   | G58kERjEUR_xgfw-data-non-dashboard-logs.gz | Weekly backup for SFVUNL C01001JVB4DH41B                         | kackerman.email@gmail.com | kackerman.email@gmail.com | 1kbcoA-00087O-2m-1604811606 |      1113015 | Other               |      1 | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f-2020-11-08T09:00Z | 2020-11-08T09:07:43.414Z | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f | 2020-11-08T05:00:06Z | SFVUNL         | 041101618035 | Allowed       |             1 | b288d41b-53bb-64ae-5a67-1bc1507d5198 |
|               | C01001JVB4DH41B    | Anti-Spam  | SMTP            | Information |           |               |            | TCP        | None          | QUEUED   | BbhYIdLbXL_xgfw-data-non-dashboard-logs.gz | *ALERT* Sophos XG Firewall - New firmware ready for installation | kackerman.email@gmail.com | kackerman.email@gmail.com | nB9LnC-VgXlEZ-v7-1605096583 |        19831 | Other               |      1 | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f-2020-11-11T16:00Z | 2020-11-11T16:25:01.972Z | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f | 2020-11-11T12:09:48Z | SFVUNL         | 041101618035 | Allowed       |             1 | b288d41b-53bb-64ae-5a67-1bc1507d5198 |
|               | C01001JVB4DH41B    | Anti-Spam  | SMTP            | Information |           |               |            | TCP        | None          | QUEUED   | Qrwn4t39GN_xgfw-data-non-dashboard-logs.gz | *ALERT* Sophos XG Firewall - Gateway status                      | kackerman.email@gmail.com | kackerman.email@gmail.com | Z9Pbhk-aNi2xj-ry-1605260418 |        19796 | Other               |      1 | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f-2020-11-13T13:00Z | 2020-11-13T13:54:09.403Z | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f | 2020-11-13T09:40:18Z | SFVUNL         | 041101618035 | Allowed       |             1 | b288d41b-53bb-64ae-5a67-1bc1507d5198 |
|               | C01001JVB4DH41B    | Anti-Spam  | SMTP            | Information |           |               |            | TCP        | None          | QUEUED   | Qrwn4t39GN_xgfw-data-non-dashboard-logs.gz | *ALERT* Sophos XG Firewall - New firmware ready for installation | kackerman.email@gmail.com | kackerman.email@gmail.com | LhYmHo-8ASFCN-vV-1605260418 |        19831 | Other               |      1 | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f-2020-11-13T13:00Z | 2020-11-13T13:54:09.403Z | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f | 2020-11-13T09:40:18Z | SFVUNL         | 041101618035 | Allowed       |             1 | b288d41b-53bb-64ae-5a67-1bc1507d5198 |
|               | C01001JVB4DH41B    | Anti-Spam  | SMTP            | Information |           |               |            | TCP        | None          | QUEUED   | Qrwn4t39GN_xgfw-data-non-dashboard-logs.gz | *ALERT* Sophos XG Firewall - New firmware ready for installation | kackerman.email@gmail.com | kackerman.email@gmail.com | gIgmgK-AnpA76-WN-1605260427 |        19831 | Other               |      1 | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f-2020-11-13T13:00Z | 2020-11-13T13:54:09.403Z | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f | 2020-11-13T09:40:27Z | SFVUNL         | 041101618035 | Allowed       |             1 | b288d41b-53bb-64ae-5a67-1bc1507d5198 |
|               | C01001JVB4DH41B    | Anti-Spam  | SMTP            | Information |           |               |            | TCP        | None          | QUEUED   | Qrwn4t39GN_xgfw-data-non-dashboard-logs.gz | *ALERT* Sophos XG Firewall - Gateway status                      | kackerman.email@gmail.com | kackerman.email@gmail.com | JJVGto-kPi4DH-Zt-1605260447 |        19796 | Other               |      1 | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f-2020-11-13T13:00Z | 2020-11-13T13:54:09.403Z | 8a2ef13b-3fbf-4a38-a428-0cf3b2b59f8f | 2020-11-13T09:40:48Z | SFVUNL         | 041101618035 | Allowed       |             1 | b288d41b-53bb-64ae-5a67-1bc1507d5198 |
+---------------+--------------------+------------+-----------------+-------------+-----------+---------------+---