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 | +---------------+--------------------+------------+-----------------+-------------+-----------+---------------+---