Exporting QRadar Logs to FortiSIEM

The following document provides a procedure for exporting logs stored in QRadar SIEM into FortiSIEM.

Overview

The export process works as follows:

  1. The export script requires Python 3.9 or higher and can run on any server, including the FortiSIEM Collector itself. However, it is recommended to use a separate Linux server to reduce the load on the FortiSIEM Collectors.
  2. The script connects to the QRadar SIEM via API on port 443 and collects raw logs along with the Reporting Device IP or Host Name and the time at which the log was received in QRadar.
  3. The script then sends the logs to a FortiSIEM Collector via HTTPS POST.
  4. The Collector parses the logs as if they were directly received from the external device and stores them in FortiSIEM.

Prerequisites

  1. FortiSIEM 7.2.2 or later is needed. If you are running an older FortiSIEM version of 7.2.1 or above, then contact Fortinet for a Collector patch.
  2. Ensure both the QRadar SIEM and the FortiSIEM Collector are reachable via TCP 443 from the server running the python script.
  3. The raw logs in QRadar SIEM must be in the same format as supported by FortiSIEM, else parsing will fail, and the logs will be stored in FortiSIEM as Unknown Event Type. Refer to the FortiSIEM External Systems Configuration Guide for supported log formats for various vendors. To overcome formatting issues, you might like to write a parser first before beginning the ingestion process. Note that the raw logs can still be searched using keywords.

Resource Considerations

  1. Ensure both the QRadar Instance and the FortiSIEM Collector is reachable via TCP 443 from the machine running the python script.
  2. The QRadar instance should be properly sized for processing Ariel search queries from the Linux Server running the export script.
  3. Fortinet recommends a large collector with at least 16vCPU, 32GB RAM to handle the bursty EPS during the export process.
  4. The FortiSIEM Supervisor and Worker instances should be sufficiently sized to handle the bursty EPS during the export process. Refer to the appropriate FortiSIEM Sizing Guide from the FortiSIEM Documentation Library.
  5. The Linux Server running the export script should be sufficiently sized with a large number of vCPU so it can have an increased thread worker count on the script. Fortinet recommends at least 16vCPU, 32GB RAM.

Planning the Export

To create the optimal way to export your data, take the following actions.

  1. Identify how many days of data you want to export from QRadar.
  2. Break up those days into test chunks.
    1. Complete the first week in 1 day chunks (run the script on a one day interval).
    2. Complete subsequent chunks of data 1 week at a time.
  3. Observe execution time on 1 day chunk of exported data, after the script runs, and identify how long it took from the output.
  4. Observe the CPU and memory utilization of the following instances. Increase vCPU or memory if better performance is required.
    1. QRadar Instance
    2. FortiSIEM Collector
    3. Supervisor and Worker health as seen from FortiSIEM GUI
    4. The host running the export script

 

How Time Range is Handled

The script breaks up the time range into chunks of minutes as defined by the --interval-min argument, which defaults to 1 minute chunks. This ensures that incredibly large queries can be evenly spread for each worker thread until the entire dataset is pushed to FortiSIEM.

Note: Even small chunks of time like 30 minutes can be an incredibly large dataset if the QRadar instance was ingesting 50K-100K events per second during the time range queried.

 

Example 1: High EPS - Small Query Intervals

The date range is 7 days, and interval-min is set to 1 minute. The script will break the time range into separate 1 minute API calls, or 1,440 queries per day x 7 days = 10,080 API (Ariel search queries) calls. These will be evenly spread over the number of worker threads (default, if not specified, is 5 worker threads).

The QRadar Instance will experience 5 concurrent Ariel search queries for 1 minute of data each. As each of these finish, a new worker thread issues another 1 minute query until all the data has been retrieved.

 

Example 2: Low EPS - Larger Query Intervals

The date range is 7 days, and interval-min is set to 10 minutes. The script will break the time range into separate 10 minute API calls, or 144 queries per day x 7 days = 1,008 API (Ariel search queries) calls. These will be evenly spread over the number of worker threads (default, if not specified, is 5 worker threads).

Export Procedure

The export procedure is comprised of 5 general steps.

Step 1: Generate QRadar API Key for Log Export

Reference: https://www.ibm.com/docs/en/qsip/7.5?topic=services-adding-authorized-service

  1. Login to your QRadar Instance.
  2. Navigate to Admin > User Management > Authorized Services.
  3. Click Add, and enter the following information.
    1. In the Authorized Service Label field enter a name such as "FortiSIEM_Export".
    2. From the Security Profile list, select Admin.
    3. From the User Role list, select Admin.
    4. From Expiry Settings, uncheck any existing expiry settings to allow no expiry.
    5. Click Save.

A pop up will display the API key. Record this information.

Step 2: Configure QRadar Payload Truncation of Raw Logs

QRadar by default, truncates raw logs in storage (TCP syslog default 4,096, UDP syslog default 1,024), destroying part of the raw event. In order to adequately export and re-parse the syslog messages in FortiSIEM, the original non-truncated/unmodified raw event is needed.

To change the QRadar syslog payload length setting, take the following steps from your QRadar Instance.

  1. Navigate to Admin > System Configuration > System Settings.
  2. Click on the Advanced icon.
    1. Set Max TCP Syslog Payload Length to "24000".
    2. Set Max UDP Syslog Payload Length to "24000".
    3. Click Save.
  3. From the Admin tab, click Advanced > Deploy Full Configuration > Continue.
    Note: This will not affect existing logs that were truncated, only new logs coming in, so we hope this setting was already set prior to export.

Step 3: Set up API Upload Credential in FortiSIEM Collector

Take the following steps:

  1. Login to the FortiSIEM Collector via SSH.
  2. Define a username and password and run the following command as user root:

    htpasswd -b /etc/httpd/accounts/passwds <user> <password>

Step 4: Run Export Script

The following script (qradar_export.py) will connect to QRadar SIEM via API, collect the logs and send them to FortiSIEM Collector via HTTPS POST.

Notes:

  1. It is strongly recommended that you run the qradar_export.py script in a screen or tmux command on Linux, so it can effectively run for long periods in the background. Tmux is a Linux program that allows running a script in a background process. For really long operations, you can launch a tmux session first, then run the script, and then leave it running in the background. If you skip this step, and your SSH session times out, the script will stop executing.
    For a tutorial on tmux, see https://www.redhat.com/sysadmin/introduction-tmux-linux
  2. This is a one-time script, so scheduling via crond is not recommended for any reason.
  3. Go through the Prerequisites, Resource Considerations and Planning the Export sections above before running the script.
  4. The script requires Python 3.9 or later. Required python modules: requests and urllib3. To get these modules, run the following command.
    pip install requests urllib3
    These modules are already installed on FortiSIEM Collectors.
  5. The script resides in the following location on any FortiSIEM v7.2.2 or later installation:
    /opt/phoenix/bin/qradar_export.py

 

The Script:

qradar_export.py --qradar-host <host IP address> --api-token '<API token>' \

--start-date '<yyyy-mm-dd hh:mm:ss>' --end-date '<yyyy-mm-dd hh:mm:ss>' \

--fsm-collector '<FortiSIEM Collector FQDN/IP>' \

--fsm-user '<FortiSIEM Collector username>' \

--fsm-password '<FortiSIEM Collector password>' \

--no-verify-ssl --num-threads <Number of threads> --interval-min <minute time interval>

Note: Remember that the --start-date and --end-date time range is expectedin the QRadar instance server local time.

 

Example Usage

python qradar_export.py --qradar-host '1.1.1.1' \

--api-token 'xxxxx-xxxxxxx-xxxxxx-xxxxx' \

--start-date '2024-06-20 00:00:00' --end-date '2024-06-20 23:59:59' \

--fsm-collector '2.2.2.2' --fsm-user 'myuser' --fsm-password 'mypass' --no-verify-ssl

Script Arguments

Required

Description

--api-token

Yes

The QRadar API token collected in Step 1.

--fsm-collector

No

Target FortiSIEM Collector FQDN or IP address. Defaults to 127.0.0.1.

--fsm-password

Yes

FortiSIEM Collector API Password defined in Step 3.

--fsm-user

Yes

FortiSIEM Collector API Username defined in Step 3.

--interval-min

No

Time chunk interval in minutes that each thread handles. The time range is broken into query chunks of 1 minute by default.

--no-verify-ssl

No

If specified, this will disable SSL verification. Otherwise, all network connections will attempt SSL verification.

--num-threads

No

The number of allowed concurrent API calls to QRadar. The default number is 5. The following formula is recommended for the number of threads.
2 x number of cores.

--qradar-host

Yes

The QRadar host IP address.
--start-date Yes The start date for the query in YYYY-MM-DD format or YYYY-MM-DD HH:MM:SS - QRadar server local timezone expected. See How Time Range is Handled for more information.
--end-date Yes The end date for the query in YYYY-MM-DD format or YYYY-MM-DD HH:MM:SS - QRadar server local timezone expected. A large time range, for example 30 days, can be used, but it is recommended to initially use a smaller time range, such as a single day to check its performance. See How Time Range is Handled for more information.

Examples

The first example assumes a RHEL based system (RHEL, Rocky Linux, Alma Linux), and the second example runs the script directly on a FortiSIEM Collector. You can run the Python script in any environment however.

 

Example 1: Running the Script on a Third Party Linux Machine with Python 3.9 Installed as the Default Interpreter

pip install requests urllib3

sudo dnf install tmux -y

tmux

python qradar_export.py --qradar-host '1.1.1.1' --api-token 'xxxxx-xxxxxxx-xxxxxx-xxxxx' --start-date '2024-06-20 00:00:00' --end-date '2024-06-20 23:59:59' --fsm-collector '2.2.2.2' --fsm-user 'myuser' --fsm-password 'mypass' --no-verify-ssl –num-threads 16

 

(Optional) You can detach from a tmux session and let it run by pressing Ctrl + b, then releasing those keys, then pressing the letter ‘d’.

 

To view tmux sessions running in the background, enter the following command.

tmux ls

 

To reattach to a tmux session running in the background, enter the following command.

tmux attach-session -t <session_name_or_number>

 

To exit a tmux session, closing it down, while attached to the session, enter the following command and hit enter.

exit

 

Example 2: Running the Script on the FortiSIEM Collector Itself

The Collector runs the script that connects to the remote QRadar instance, and POSTS results to itself by running the following commands.

sudo dnf install tmux -y

tmux

python3.9 qradar_export.py --qradar-host '1.1.1.1' --api-token 'xxxxx-xxxxxxx-xxxxxx-xxxxx' --start-date '2024-06-20 00:00:00' --end-date '2024-06-20 23:59:59' --fsm-collector '127.0.0.1' --fsm-user 'myuser' --fsm-password 'mypass' --no-verify-ssl –num-threads 16

 

(Optional) You can detach from a tmux session and let it run by pressing Ctrl + b, then releasing those keys, then pressing the letter ‘d’.

 

To view tmux sessions running in the background, enter the following command.

tmux ls

 

To reattach to a tmux session running in the background, enter the following command.

tmux attach-session -t <session_name_or_number>

 

To exit a tmux session, closing it down, while attached to the session, simply enter the following command and hit enter.

exit

Step 5: Confirm Ingested Data in FortiSIEM

When the script begins running, you should almost immediately start to see a stream of log events coming into FortiSIEM. To check, take the following steps.

  1. Login to FortiSIEM GUI.
  2. Navigate to Analytics.
  3. Run a historical query for the data time range the script is actively exporting.
  4. Examine the events in that time frame, and ensure the reporting IP (equivalent to QRadar Sending IP, or log source identifier (if IP address)), and ensure these are logs from the QRadar instance).
  5. For an easier overview, you can run an aggregate query here over the same time range. Click the "Group By and Display Fields" icon, and add/include the following Attribute columns:
    • Reporting IP
    • Reporting Device
    • COUNT(Matched Events)

    and click Apply & Run.

You should see a breakdown of all the reporting devices (log sources in QRadar terminology), including those exported from QRadar. You can match this up to the QRadar log sources list to ensure your external log sources are getting migrated.

Export Procedure for Service Provider Deployments

If you are planning to export QRadar logs from a Service Provider Deployment into FortiSIEM, then follow the steps in Export Procedure with the minor modifications outlined below. The concept of Tenant in QRadar is identical to Organization in FortiSIEM.

  1. Deploy a FortiSIEM in Service Provider mode and create all the Organizations corresponding to the Tenants in QRadar. For each Organization, define, deploy, and register the Collector(s). Note that the Organization name in FortiSIEM does not need to exactly match the Tenant name in QRadar. After the data is imported to FortiSIEM, events will have the Organization name defined in FortiSIEM.
    You must repeat the following steps 2-6 for each Organization (Tenant).
  2. Perform Export Procedure Step 1, but create the API key with the tenant/domain restricted role and security profile for that tenant application.
    Note: Firefox is the recommended browser for the QRadar UI, as elements are missing in Chrome.
  3. Identify the FortiSIEM Collector to receive logs for that Organization (Tenant).
  4. Perform Export Procedure Step 3 for that Collector.
  5. Perform Export Procedure Step 4 with that Collector in the argument in the script. The Collector will assign the received logs to that Organization.
  6. Confirm that the data is ingested as in Export Procedure Step 5.

Validating Events Ingested

When the export script has finished for a given time interval, it will print the total number of events that were processed AND a replication of the entire query that you can run in QRadar to compare the total events.

 

Example Script Output:

Script started at (UTC): 2024-07-25 22:30:41.764494+00:00

Script ended at (UTC): 2024-07-25 22:31:25.681156+00:00

Total execution time: 0:00:43.916662

Processed 166,299 events total, you can compare to the total result count returned by running the same query in QRadar

SELECT starttime,logsourceid,logsourceidentifier,sourceip,utf8(payload) FROM events WHERE logsourceidentifier IS NOT NULL AND logsourceidentifier != '127.0.0.1' START '2024-07-24 00:00:00' STOP '2024-07-24 00:10:00'

 

To compare, login to the QRadar Instance, navigate to Log Activity, and run the query that appeared at the end of the QRadar export. From our Example Script Output above, this query would be: 

SELECT starttime,logsourceid,logsourceidentifier,sourceip,utf8(payload) FROM events WHERE logsourceidentifier IS NOT NULL AND logsourceidentifier != '127.0.0.1' START '2024-07-24 00:00:00' STOP '2024-07-24 00:10:00'

 

Notice the Total Results count in the screenshot, which should match the script output. In our example, the script output and query both display 166,299 events.