QRadar: Duplicate custom property names can block upgrade

Resolving The Problem

1. On your Console, confirm that multiple properties with the same property name exist:

   psql -U qradar -c "select propertyname,count(propertyname) from ariel_regex_property group by propertyname having count(propertyname) > 1;"

2. For each duplicate property found in step 1, check if any of the properties are auto discovered or deprecated. Enter the following command, where <duplicate_property> is the name of a duplicate property from the output of step 1:

   psql -U qradar -c "select id,propertyname,autodiscovered,deprecated from ariel_regex_property where propertyname='<duplicate_property>';"

   For example, if the output from step 1 included the VirusName property, type:

   psql -U qradar -c "select id,propertyname from ariel_regex_property where propertyname = 'VirusName';"
   
   id          | propertyname | autodiscovered | deprecated
   ---------------------+--------------+----------------+------------
   DEFAULTCUSTOMEVENT9 | VirusName    | f              | f
   VIRUSNAME_FACADE    | VirusName    | f              | t

3. For each property ID value in the output from the commands that you ran in step 2:
   1. Find the number of expressions and the expression table to which the property belongs. Enter the following command, where <property_id> is the property ID from the output of step 2:

      psql -U qradar -c "select count(*),'ariel_property_expression' from ariel_property_expression where ap_id = '<property_id>' union select count(*), 'ariel_property_json_expression' from ariel_property_json_expression where ap_id = '<property_id>' union select count(*), 'ariel_property_aql_expression' from ariel_property_aql_expression where ap_id = '<property_id>';"

      
      For example, the VirusName duplicate property the output included two property IDs:
      DEFAULTCUSTOMEVENT9
VIRUSNAME_FACADE 
      Run the following command for the DEFAULTCUSTOMEVENT9 ID:

      psql -U qradar -c "select count(*),'ariel_property_expression' from ariel_property_expression where ap_id = 'DEFAULTCUSTOMEVENT9' union select count(*), 'ariel_property_json_expression' from ariel_property_json_expression where ap_id = 'DEFAULTCUSTOMEVENT9';"
      
      count |            ?column?
      -------+--------------------------------
           0 | ariel_property_json_expression
          52 | ariel_property_expression

   2. Then, run this command for the VIRUSNAME_FACADE ID:

      psql -U qradar -c "select count(*),'ariel_property_expression' from ariel_property_expression where ap_id = 'VIRUSNAME_FACADE' union select count(*), 'ariel_property_json_expression' from ariel_property_json_expression where ap_id = 'VIRUSNAME_FACADE' union select count(*), 'ariel_property_aql_expression' from ariel_property_aql_expression where ap_id = 'VIRUSNAME_FACADE';"
      
      count |            ?column?
      -------+--------------------------------
           1 | ariel_property_json_expression
           0 | ariel_property_expression

4. Determine which duplicated properties need to be removed from the step 2 command output:
   • If one of the properties has autodiscovered = t but the others do not, select that property.
   • If both of the properties have autodiscovered = t, select the property with the lowest expression count in the output of the commands in step 3.
   • If one of the properties has deprecated = t or the ID includes "FACADE", select that property only if its expression count is less than or equal to the expression count of the other properties.
     For example, in the output from step 2 neither property has autodiscovered = t. The VIRUSNAME_FACADE property has both deprecated = t and a property name that includes "FACADE". The expression count is less than the expression count of DEFAULTCUSTOMEVENT9, so select VIRUSNAME_FACADE.
     Note: If you have trouble with which property to select, contact IBM Support for assistance.
5. Update the selected property expressions from step 4 to have only one property name:

   psql -U qradar -c "update <ariel_expression> set ap_id = '<unselected_id>' where ap_id = '<selected_id>';"

   Note: change the variables as follows:
       ​Change <unselected_id> to the ID not selected in step 4
       Change <selected_id> to the ID selected in step 4
       Change <ariel_expression> to the column with an expression count for the <selected_id> in the output of step 3
6. For example, we selected VIRUSNAME_FACADE and did not select DEFAULTCUSTOMEVENT9, and the VIRUSNAME_FACADE column with an expression count is ariel_property_json_expression, so type:

   psql -U qradar -c "update ariel_property_json_expression set ap_id = 'DEFAULTCUSTOMEVENT9' where ap_id = 'VIRUSNAME_FACADE';"

7. Delete the selected property from step 4 by typing the following command, where <selected_id> is the ID that you selected in step 4 and <duplicate_property> is the duplicate property found in step 1:

   psql -U qradar -c "delete from ariel_regex_property where id = '<selected_id>' and propertyname = '<duplicate_property>';"

   
   For example, to delete the VIRUSNAME_FACADE ID from the VirusName property, type:

   psql -U qradar -c "delete from ariel_regex_property where id = 'VIRUSNAME_FACADE' and propertyname = 'VirusName';"