DA SQL Query for Searches

[Thiyagu]

Hi,

I was going through this post by Scanner,

http://98.129.119.162/connect/de/articles/enterprise-vault-useful-sql-queries

it has some really useful SQL queries.

I remember seeing a DA SQL query for searches.

When i ran that query it used to give me all the searches conducted on a particular customer DB and list the person who started the search,

date windows

search criteria and no of hits etc.

does any one have this query? , i remember seeing it somewhere, but not able to search it now :( , wish i would have save that somewhere.

thanks

Thiyagu

[TonySterling]

I had a query that did that but can't seem to locate it. 

You can actually do one better and create a report:

https://www-secure.symantec.com/connect/articles/creating-custom-report-symantec-enterprise-vault-discovery-accelerator-80-and-higher-part-1

https://www-secure.symantec.com/connect/articles/creating-custom-report-symantec-enterprise-vault-discovery-accelerator-80-and-higher-part-2

[Thiyagu]

i have look at those articles, but we dont have sql reporting ,

i was hoping to directly query the sql tables and generate a report.

[Liam_Finn1]

Try Something like this. It does not show the search terms but it does gove you the rest of the data you are looking for. With a bit of playing i'm sur you can figure out how to add the search terms.

Please note that the search terms are stored in DA in XML format so unless you want to just paste an XML string as the search terms additional parsing will be needed to display them

Liam

SELECT DISTINCT vsh.SearchID, vsh.CreateDate, sv.PrincipalName, vsh.DateModified, vsh.ModifiedBy, vsh.ModifiedByID, vsh.SearchName AS 'Search Name', vs.StatusName, vsh.CaseID, vlc.Name AS 'Case Name'

FROM view_searchHistory vsh

                LEFT JOIN dbo.view_LegalCase vlc

                                ON vsh.CaseID = vlc.CaseID

                LEFT JOIN dbo.view_searches vs

                                ON vsh.SearchID = vs.SearchID

                LEFT JOIN dbo.security_view sv

                                ON vs.CreatedByID = sv.PrincipalID

WHERE vsh.DateModified >= DATEADD(m, -1, GETDATE()) and vlc.Name NOT LIKE '%System Case%'

--ORDER BY searchID

UNION ALL

SELECT DISTINCT vs.SearchID, vs.CreateDate, sv.PrincipalName, vs.ModifiedDate AS 'DateModified', sv.PrincipalName AS 'ModifiedBy', vs.ModifiedByID, vs.Name AS 'Search Name', vs.StatusName, vs.CaseID, vlc.Name AS 'Case Name'

FROM view_searches vs

                LEFT JOIN security_view sv

                                ON vs.CreatedByID = sv.PrincipalID

                LEFT JOIN view_LegalCase vlc

                                ON vs.CaseID = vlc.CaseID

WHERE vs.ModifiedDate >= DATEADD(m, -1, GETDATE())

                AND (StatusName <> 'Deleted' OR StatusName LIKE 'Search')

--ORDER BY vs.SearchID

ORDER BY DateModified

[Liam_Finn1]

Thiyagu

If you have the solution to your question please mark the appropiate posting as the solution by clicking on MARK AS SOLUTION at the bottom of the posting

[Thiyagu]

thanks Scanner001

thanks for the query that is pretty much what i want (except for the search team,) which i will try to add it myself and then post back that finished query here.

thanks for you help.

cheers

thiyagu

[TonySterling · Accepted solution]

You could try this:

SELECT SearchID,
            CaseID,
            NumHits,
            PrincipalName AS ModifiedBy,
            tblIntSearches.Name AS SearchName,
            tblIntSearches.ModifiedDate AS DateModified,
            CreateDate,
            tblStatus.[Name] AS SearchType,
            CreationType.[Name] AS CreationType,
            SampleResultSize,
            NativeQuery,
            NativeLegacyQuery,
            XMLText
FROM tblIntSearches
      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]
      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
WHERE tblIntSearches.[StatusID] = 858 AND PrincipalName IS NOT NULL
UNION ALL
SELECT  TypeID AS SearchID,
            tblAudit.CaseID,
            NumHits,
            PrincipalName AS ModifiedBy,
            tblIntSearches.Name AS SearchName,
            AuditDate AS DateModified,
            CreateDate,
            tblStatus.[Name] AS SearchType,
            CreationType.[Name] AS CreationType,
            SampleResultSize,
            NativeQuery,
            NativeLegacyQuery,
            XMLText
FROM tblIntSearches
      INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID
            AND tblIntSearches.StatusID = 858
            AND tblAudit.AuditTypeID = 1052
      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]
      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
ORDER BY CreateDate DESC