QRadar: "Failed to load data" error when opening the Event Mappings tab in the DSM Editor

Resolving The Problem

• Read all the steps before you apply them.
• The following procedure requires SQL knowledge, in case you don't feel comfortable with the steps or you have doubt, contact QRadar Support.
• Make sure to use the complete command before you run it.
• These steps are intended to fix this specific issue, do not modify the tables for any other purpose.

1. SSH to the QRadar console.
2. Run the following command to create a backup folder in case it doesn't exist:

   mkdir -p /store/IBM_Support/

3. Use the following command to create a backup for the tables:

   pg_dump -U qradar -t dsmevent >> /store/IBM_Support/dsmevent.sql
   pg_dump -U qradar -t qidmap >> /store/IBM_Support/qidmap.sql

4. Search for the log source type ID. Use the following command and replace <NAME> with part of the log source type name:

   psql -U qradar -c "select * from sensordevicetype where devicetypename ilike '%<NAME>%'"

   In this example, we are looking for the log source ID for the CrowdStrike Incident DSM. So we replace <NAME> with Crowd:

   psql -U qradar -c "select * from sensordevicetype where devicetypename ilike '%Crowd%'"

5. Copy the ID from the id column, for this example is 407. This number is the log source ID:
6. Use the following command to find entries where the QID is missing. Replace <ID> with the log source ID:

   psql -U qradar -c 'select d.* from dsmevent d LEFT JOIN qidmap q on d.qidmapid = q.id where q.id IS NULL and devicetypeid=<ID>'

   For this example, as the log source ID for the CrowdStrike Incident DSM is 407, we replace <ID> with 407:

   psql -U qradar -c 'select d.* from dsmevent d LEFT JOIN qidmap q on d.qidmapid = q.id where q.id IS NULL and devicetypeid=407'

   Output example:
   Based on the output there is one value with missing QID. Take note of the devicetypeid and id. For this example, the devicetypeid is 407 and the id is 671948:

   psql -U qradar -c 'select d.* from dsmevent d LEFT JOIN qidmap q on d.qidmapid = q.id where q.id IS NULL and devicetypeid=407'
   
    devicetypeid | deviceeventid | deviceeventcategory  | customevent | qidmapid | credibility | routepoint | unravelevent |   id   |   uuid                 
   --------------+---------------+----------------------+-------------+----------+-------------+------------+--------------+--------+--------------------------------------
            407 | 2000105       | CrowdStrike Incident | t           |          |           0 |            |            0 | 671948 | f02f4ac9-b43a-4a13-a1dd-9cbaba6f5b5d
   (1 row)

7. To resolve the issue, you need to remove the record with the null value. Run the following command, replace <DSM_Type_ID> with the devicetypeid and <ID> with the id. Both numbers are taken from step 6: 

   psql -U qradar -c "delete from dsmevent d where devicetypeid=<DSM_Type_ID> and id in (<ID>)"

   Command example:

   psql -U qradar -c "delete from dsmevent d where devicetypeid=407 and id in (671948)"

8. Open the DSM Editor and go to the Event Mappings tab to confirm the problem is fixed:
   
   
   Result
   The administrator is able to access again the Event Mappings tab in the DSM Editor. If you still have the issue, contact QRadar Support.