08-07-2014 05:24 AM
I am looking for a report that lists all active searches for each department. Does this exist?
Thank you.
Solved! Go to Solution.
08-08-2014 12:14 PM
I think this change should get those:
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
08-07-2014 07:26 AM
I had a look here:
Article:HOWTO95818 | | | Created: 2014-05-07 | | | Updated: 2014-06-06 | | | Article URL http://www.symantec.com/docs/HOWTO95818 |
But don't think there is a report that list out searches.
08-07-2014 07:31 AM
I created this for DA but it might work, you could try it and let me know if it bombs out. I don't have my lab up just now so if you let me know if it doesn't work I will see about fixing it up for CA.
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
08-08-2014 09:46 AM
Hello and thank you for this query.
It appears this logic returns completed searches but not active searches. Is there a tblIntSearches.[StatusID] number specific for searches currently scheduled?
Thank you.
08-08-2014 12:14 PM
I think this change should get those:
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
08-21-2014 03:43 PM
Hey mate,
Did this work for you?
TS