Deleting Cognos reports that contain PI
Personal information (PI) can reside in certain Community Metrics reports linked to from the Cognos management page. For example, reports on community participation can contain the names of users who participated. Perform these steps if Cognos Metrics is used in your deployment and a user requests that their PI be erased.
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:
- 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'
- 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'
- 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.
- 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
- Run the SQL in the Metrics database.
- If the query returns any reports, use the following procedure to delete those reports.
Deleting reports that contain the user's name
- From the results table in the preceding task, obtain the UUID for the first community in the COMMUNITY_UUID column.
- Log in to the Cognos management page as the Cognos administrator, with the following URL: http://your_server/cognos/servlet/dispatch/ext
- 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.
- Take the following actions for the subfolder for each language:
- 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.
- 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.
- 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.