Compliance Accelerator searches SQL query

[goatboy]

Hi

I want to pull details of specific unaccepted searches in CA. Specifcally I want the email address of the From and To recipients.

I have this query which gives me everything except email addresses. Is this data stored in the CA databease? Thanks

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 100000 [SearchItemID]
      ,[SearchVaultID]
      ,[SearchID]
      ,[CaseID]
      ,[VaultID]
      ,[KVSVaultEntryID]
      ,[KVSSaveSetID]
      ,[MarkingID]
      ,[Author]
      ,[Subject]
      ,[MailDate]
      ,[NumAttachments]
      ,[PrincipalID]
      ,[extension]
      ,[url]
      ,[size]
      ,[Direction]
      ,[Type]
      ,[Recipient]
      ,[CaptureType]
      ,[CaptureDate]
      ,[ReviewerID]
      ,[IncludeInSample]
      ,[ItemLocationID]
      ,[RecipientTruncated]
      ,[ModifiedDate]
      ,[PolicyAction]
      ,[PolicyXML]
      ,[PolicySummary]
      ,[PolicySummaryTruncated]
      ,[FullHash]
      ,[HashPart1]
      ,[IsDuplicate]
  FROM [Evcomplianceaccelerator].[dbo].[tblSearchItems]
  
  where CaseID = 31394 and SearchID = 70

[EV_Ajay]

Hi ,

In above query result the Field "Author" is nothing but "From" field.

Field "Recipient" is nothing but "To" Field of email.

[goatboy]

So it it possible to get the actual email addresses from the CA database? When I run the query above, it just shows Display Names, not the email addresses.

thanks

[Kenneth_Adams]

Hello, goatboy;

Are you still having this issue?  The reason that I ask is I just checked my 11.0.1 Beta lab to see that I have a combination of Display Name and SMTP addresses in the Author and Recipient fields.  The contents of these fields are dependent upon the information indexed on the captured items.  We prefer using the Display Name as this is easier information to read and most customers want that information displayed.  Where we can't obtain a Display Name, we'll use the SMTP address.

FYI, the tblSearchResults table contains search captured items that have not been accepted into the review set.  The tblIntDiscoveredItems table contains the search and sampling captured items that are in the review set.  The Author and Recipient fields in this table contain the same information format - default of Display Name with alternate of SMTP address when no Display Name is available.

Now, if you had DA with Analytics enabled on the case, a table named tblContacts_# (where # is the Case ID) where a have a listing of all e-mail recipients and authors with their SMTP addresses.  So, we only provide the SMTP address AND Display Name information in DA with Analytics enabled.  We can only provide SMTP address information in CA if no Display Name can be associated with the address.

Regards,

Ken

[goatboy · Accepted solution]

Thanks for looking into it. I managed to get what I wanted by getting the Saveset IDs of all the messages I was interested in, then extracted them in batch using EVSVR as .msg files. Then parsed the .msg files. See here for details of the extraction process: https://www-secure.symantec.com/connect/forums/evsvr-extract-multiple-files-batch-mode

[Kenneth_Adams]

Nice.  Thank you for sharing what you did to resolve your issue.