06-24-2015 02:18 AM
Hi,
Is there a way to see or audit ALL searches made in Discovery Accelerator (I am using 10.0.4)? I have had a look in the SQL database for DA, but that only seems to show what searches were accepted/saved with a case.
What I would like is to be able to see every search that staff are doing, not just the ones that have been accepted/saved.
regards
Jim
Solved! Go to Solution.
06-24-2015 05:34 AM
This shows searches that were rejected, by changing the StatusID you can modify what you get back.
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
06-24-2015 05:34 AM
This shows searches that were rejected, by changing the StatusID you can modify what you get back.
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
07-10-2015 06:18 AM
Thankyou Tony, that has in fact worked brilliantly
regards
JIm