Deleting Cognos reports that contain PI

Making sure the user is no longer in Connections

Before investigating whether any Community Metrics reports contain the user's name, confirm that the user's identity was pseudonymised in the Connections applications themselves, by doing the following steps:

1. Obtain the UUID of the user who requested erasure of their personal data by querying the EMPLOYEE table in Profiles. The UUID resides in the row for that user, in the PROF_GUID column. For example, if you know the user's original email address, your query would look like this:

   select PROF_GUID from EMPINST.EMPLOYEE where PROF_MAIL = 'user_name@example.com'

2. Now, in the Metrics database, use the UUID to query the table F_TRX_USER to ensure that the user's name was changed to a pseudonym:

   select NAME, USER_STATE from METRICS.F_TRX_USERS where UUID = 'user_UUID'

3. Do one of the following:
   • If no result is found, the user was never collected by the Metrics application and no further action is required.
   • If the result is a name that does not reveal the user's identity (pseudonym) and a user state of 1 (inactive user), the user was effectively pseudonymised in Connections applications and you can proceed to the next task of finding the metrics reports.
   • If the result is the user's real name or the user state is 0 (active user), ask the Administrative User to change the name before proceeding to the next task. How to change the name is described in Managing user requests to erase PI.

Finding the metrics reports that contain the user's name

Even when only a user's pseudonym remains in the Metrics application, Community Metrics reports might still contain their real name. Follow this procedure to find out if any reports contain the name of the user in question.

1. Copy the following SQL code, and then replace all four instances of the sample value for UUID with that actual UUID string for your user.

   select UUID as COMMUNITY_UUID, TITLE, SUM(LASTD7) as LASTD7, SUM(LASTW4) as LASTW4, SUM(LASTQ1) as LASTQ1, SUM(LASTM12) as LASTM12
    from (
     select RANKORDER, USER_ID, COMMUNITY_ID, 0 as LASTD7, 0 as LASTW4, 0 as LASTQ1, 1 as LASTM12
     from
       (Select RANKORDER, USER_ID, COMMUNITY_ID
       from
         (Select
           ROW_NUMBER() OVER ( partition by COMMUNITY_ID ORDER BY "VALUE" DESC) AS RANKORDER,
           USER_ID,
           COMMUNITY_ID,
           "VALUE"
         from
           METRICS.USER_VALUE
         where
           TIME_RANGE_TYPE= 'M'
           and TIME_RANGE=12
           and COMMUNITY_ID <> '0'
         ) FOO
       where
         RANKORDER < 11
       ) TOP10,
       METRICS.F_TRX_USERS
     where
       USER_ID =  ID
       and UUID = uuid_of_user_to_be_removed
     union
     select RANKORDER, USER_ID, COMMUNITY_ID, 0 as LASTD7, 1 as LASTW4, 0 as LASTQ1, 0 as LASTM12
     from
       (Select RANKORDER, USER_ID, COMMUNITY_ID
       from
         (Select
           ROW_NUMBER() OVER ( partition by COMMUNITY_ID ORDER BY "VALUE" DESC) AS RANKORDER,
           USER_ID,
           COMMUNITY_ID,
           "VALUE"
         from
           METRICS.USER_VALUE
         where
           TIME_RANGE_TYPE= 'W'
           and TIME_RANGE=4
           and COMMUNITY_ID <> '0'
         ) FOO
       where
         RANKORDER < 11
       ) TOP10,
       METRICS.F_TRX_USERS
     where
       USER_ID =  ID
       and UUID = uuid_of_user_to_be_removed
     union
     select RANKORDER, USER_ID, COMMUNITY_ID, 1 as LASTD7, 0 as LASTW4, 0 as LASTQ1, 0 as LASTM12
     from
       (Select RANKORDER, USER_ID, COMMUNITY_ID
       from
         (Select
           ROW_NUMBER() OVER ( partition by COMMUNITY_ID ORDER BY "VALUE" DESC) AS RANKORDER,
           USER_ID,
           COMMUNITY_ID,
           "VALUE"
         from
           METRICS.USER_VALUE
         where
           TIME_RANGE_TYPE= 'D'
           and TIME_RANGE=7
           and COMMUNITY_ID <> '0'
         ) FOO
       where
         RANKORDER < 11
       ) TOP10,
       METRICS.F_TRX_USERS
     where
       USER_ID =  ID
       and UUID = uuid_of_user_to_be_removed
     union
     select RANKORDER, USER_ID, COMMUNITY_ID, 0 as LASTD7, 0 as LASTW4, 1 as LASTQ1, 0 as LASTM12
     from
       (Select RANKORDER, USER_ID, COMMUNITY_ID
       from
         (Select
           ROW_NUMBER() OVER ( partition by COMMUNITY_ID ORDER BY "VALUE" DESC) AS RANKORDER,
           USER_ID,
           COMMUNITY_ID,
           "VALUE"
         from
           METRICS.USER_VALUE
         where
           TIME_RANGE_TYPE= 'Q'
           and TIME_RANGE=1
           and COMMUNITY_ID <> '0'
         ) FOO
       where
         RANKORDER < 11
       ) TOP10,
       METRICS.F_TRX_USERS
     where
       USER_ID =  ID
       and UUID = uuid_of_user_to_be_removed
   ) COMM_TOP,
   METRICS.D_COMMUNITY
   where COMMUNITY_ID = ID
   group by UUID, TITLE

2. Run the SQL in the Metrics database.
3. If the query returns any reports, use the following procedure to delete those reports.

Deleting reports that contain the user's name

1. From the results table in the preceding task, obtain the UUID for the first community in the COMMUNITY_UUID column.
2. Log in to the Cognos management page as the Cognos administrator, with the following URL: http://your_server/cognos/servlet/dispatch/ext
3. Navigate to Public Folders ‎> IBMConnectionsMetrics ‎> StaticReports‬, and continue to navigate through the subfolders, according to the first letter, second letter, and finally the full community UUID.
4. Take the following actions for the subfolder for each language:
   1. In the row for the community UUID, if the value in the LASTD7 column is 1 and you see any reports named "overview_all_D7" or "people_all_system_D7," select each report and click the Delete button in the toolbar.
   2. Repeat step a for the LASTW4, LASTQ1, LASTM12 columns. Use a date range post fix to match the report names, for example, LASTW4 mappings overview_all_W4 and people_all_system_W4.
5. Repeat the above steps for the next community in the COMMUNITY_UUID column.

   The next time that the community owner views the reports, the ones that you deleted will be refreshed without the name of the user in question.