Unable to Export Large Amount of Reporting Data

Article:TECH149839  |  Created: 2010-06-04  |  Updated: 2012-11-27  |  Article URL http://www.symantec.com/docs/TECH149839
Article Type
Technical Solution


Issue



In PGP Universal Server systems with very large databases, attempting to export a very large data set can take a very long time, or may appear to fail to complete the operation. An exception may appear in the Administrative logs.

 

 


Solution



The following Export operations, initiated through the Administrative interface, may fail with very large data sets:

Exporting PDF delivery receipts: 

  • Reporting > Overview > Export Data > PDF Messenger Certified Deliver Receipts
  • Consumers > Users > Options > Export Delivery Receipts for All

 

Exporting WDE Activity: 

  • Reporting > Overview > Export Data > WDE Activity
  • Consumers > Devices > Options >Export All WDE Activity

To work around these problems it is necessary to log in to the server's command line interface and execute a query against the database. The following sections describe the queries that can be used.

Accessing the PGP Universal Server command line for read-only purposes (such as to view settings, services, logs, processes, disk space, query the database, etc) is supported. However, performing configuration modifications or customizations via the command line may void your Symantec Technical Support agreement unless the following procedures are followed.

Any changes made to the PGP Universal Server via the command line must be:
 

  • Authorized in writing by Symantec Technical Support or published as an approved and documented process on the Symantec Knowledge Base.
  • Implemented by a Symantec Partner, reseller or Symantec Technical Support.
  • Summarized and documented in a text file in /var/lib/ovid/customization on the PGP Universal Server itself.

Changes made through the command line may not persist through reboots and may be incompatible with future releases. Symantec Technical Support may also require reverting any custom configurations on the PGP Universal Server back to a default state when troubleshooting new issues. 

 

Exporting All PDF Delivery Receipts

To generate a report of all PDF Messenger delivery receipts, use the following query:

SELECT received, subject, sender_address, primary_email_address, send_date, read_date
FROM delivery_receipt, boomerang_user
WHERE boomerang_user_uuid = boomerang_user.uuid


 

Exporting All WDE Activity

 

To generate a report of all PGP WDE activity, use the following query:
 

		SELECT DISTINCT ON (iu.uuid, cm.uuid,
		cd.uuid)  c.name AS "User Display Name", iuns.user_name AS "User Name",
		iu.primary_email_address AS "Primary Email Address", iu.desktop_lastseen
		AS "User Desktop Last Seen", c.last_access AS "User Last Access",
		cm.mac_address AS "MAC Address", cm.domain AS "Domain", cm.hostname as "Host
		Name", cm.ip_address AS "IP Address", cm.client_os AS "Computer OS",
		cm.pgp_desktop_version AS "PGP Desktop Version",  cm.server_last_seen
		AS "Computer Last Seen", cd.device_id AS "Disk ID", cd.type AS "Disk Type",
		cd.partition_id AS "Partition ID", cd.display_name AS "Disk Display Name",
		cd.capacity AS "Disk Capacity",  cd.encrypted_capacity AS "Encrypted Capacity",
		cd.status AS "Disk Status", cd.status_message AS "Status Message",
		cd.server_status_as_of AS "Last Status Change"  FROM internal_user iu  LEFT
		OUTER JOIN  (SELECT un.internal_user_uuid, un.user_name  FROM
		internal_user_name un JOIN  (SELECT iun.internal_user_uuid, MIN(iun.user_name)
		as minname  FROM internal_user_name iun  GROUP BY iun.internal_user_uuid) names
		ON names.minname = un.user_name  AND names.internal_user_uuid =
		un.internal_user_uuid) as iuns  ON iu.uuid = iuns.internal_user_uuid  JOIN
		consumer c ON c.uuid = iu.consumer_uuid  JOIN internal_user_client_machine iucm
		ON iu.uuid = iucm.internal_user_uuid  JOIN client_machine cm ON
		iucm.client_machine_uuid = cm.uuid  JOIN client_machine_client_device cmcd ON
		cm.uuid = cmcd.client_machine_uuid  JOIN client_device cd ON
		cmcd.client_device_uuid = cd.uuid;

 

To export to a csv file you can use the command:

		psql -U ovidr -d oviddb -c 'COPY (SELECT DISTINCT ON (iu.uuid, cm.uuid,
		cd.uuid)  c.name AS "User Display Name", iuns.user_name AS "User Name",
		iu.primary_email_address AS "Primary Email Address", iu.desktop_lastseen
		AS "User Desktop Last Seen", c.last_access AS "User Last Access",
		cm.mac_address AS "MAC Address", cm.domain AS "Domain", cm.hostname as "Host
		Name", cm.ip_address AS "IP Address", cm.client_os AS "Computer OS",
		cm.pgp_desktop_version AS "PGP Desktop Version",  cm.server_last_seen
		AS "Computer Last Seen", cd.device_id AS "Disk ID", cd.type AS "Disk Type",
		cd.partition_id AS "Partition ID", cd.display_name AS "Disk Display Name",
		cd.capacity AS "Disk Capacity",  cd.encrypted_capacity AS "Encrypted Capacity",
		cd.status AS "Disk Status", cd.status_message AS "Status Message",
		cd.server_status_as_of AS "Last Status Change"  FROM internal_user iu  LEFT
		OUTER JOIN  (SELECT un.internal_user_uuid, un.user_name  FROM
		internal_user_name un JOIN  (SELECT iun.internal_user_uuid, MIN(iun.user_name)
		as minname  FROM internal_user_name iun  GROUP BY iun.internal_user_uuid) names
		ON names.minname = un.user_name  AND names.internal_user_uuid =
		un.internal_user_uuid) as iuns  ON iu.uuid = iuns.internal_user_uuid  JOIN
		consumer c ON c.uuid = iu.consumer_uuid  JOIN internal_user_client_machine iucm
		ON iu.uuid = iucm.internal_user_uuid  JOIN client_machine cm ON
		iucm.client_machine_uuid = cm.uuid  JOIN client_machine_client_device cmcd ON
		cm.uuid = cmcd.client_machine_uuid  JOIN client_device cd ON
		cmcd.client_device_uuid = cd.uuid) TO stdout WITH CSV HEADER;' > /var/log/ovid/wde_activity_export.csv

Note: The issue is fixed in PGP Universal Server 3.2.1 version. You can download this version from http://fileconnect.symantec.com/ 

 



 



Legacy ID



2078


Article URL http://www.symantec.com/docs/TECH149839


Terms of use for this information are found in Legal Notices