Forum Discussion

goatboy's avatar
goatboy
Level 6
11 years ago

Compliance Accelerator searches SQL query

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

Compliance Accelerator
Information Governance
Tip-How to
Windows Server (2003-2008)
  • EV_Ajay's avatar
    EV_Ajay
    Level 6
    11 years ago

    Hi ,

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

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

     

  • goatboy's avatar
    goatboy
    Level 6
    11 years ago

    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's avatar
    Kenneth_Adams
    Level 6
    10 years ago

    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