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 by typing the following command:

   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 autodiscovered 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';"

   
   The output of this command looks something like this:

   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_id1> and <property_id2> are property IDs from the output of step 2:

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

      
      For example, the VirusName duplicate property the output included two property IDs: DEFAULTCUSTOMEVENT9 and 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';"

      
      The output of this command looks something like this:

      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';"

      
      The output of this command looks something like this:

      count |            ?column?
      -------+--------------------------------
           1 | ariel_property_json_expression
           0 | ariel_property_expression

4. Determine which duplicated properties need to be removed.
   • If one of the properties in the output of the command in step 2 has autodiscovered = t but the others do not, select that property.
   • If both of the properties in the output of the command in step 2 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 in the output of the command in step 2 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", and its expression count is less than the expression count of DEFAULTCUSTOMEVENT9, so select VIRUSNAME_FACADE.
     
     Note: If you have trouble determining which property to select, contact IBM Support for assistance.
5. Update the selected property expressions from step 4 to have only one property name. Enter the following command, where <unselected_id> is that ID that you did not select in step 4, <selected_id> is the ID that you selected in step 4, and <ariel_expression> is the column with an expression count for the <selected_id> in the output of step 3:

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

   
   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';"

6. 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';"