The Searches table does not lis the Search duration. This can be obtained by looking at the Search duration for each Index Volume. The query is a bit involved as it is designed to active as well as Accepted Searches. It is also designed to run against a singel SearchID but could be modified to loop against all Accepted Searches:
DECLARE @SearchID nvarchar(10) SET @SearchID = AA ---Change the SearchID here
IF ((SELECT StatusID FROM tblIntSearches WHERE SearchID = @SearchID) = 7)
BEGIN
PRINT ''
PRINT 'Begin output from tblSearchVaultsArchived for SearchID ' + @SearchID + ' in Review'
PRINT ''
SELECT tis.Name AS 'Search Name'
, ts1.Name AS 'Search State'
, tv.KVSVaultName AS 'Archive Name'
, tsva.StatusID AS 'Archive Search StatusID'
, ts2.Name AS 'Archive Search Status'
, tv.KVSVaultEntryID
, tsva.IndexVolumeSetID AS 'Accelerator Index Volume ID'
, ISNULL(tivs.EVIndexVolumeSetID,-1) AS 'EV Index Volume ID'
, ISNULL(tivs.FirstItemSeqNum, -1) AS 'First Item Sequence Number'
, tsva.Info AS 'Information'
, tsva.NumHits AS 'Number of Hits'
, tsva.StartDate AS 'Index Volume Search StartDate', tsva.EndDate AS 'Index Volume Search EndDate'
, CAST(CONVERT(nvarchar(10), DATEDIFF(DD, tsva.StartDate, tsva.EndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsva.StartDate, tsva.EndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
, tv.VaultServerID, tvs1.VaultServer 'Vault Server Name'
, tv.VaultStoreID, tvs2.Name 'Vault Store Name'
, tv.IndexServerID, tis2.IndexServiceEntryID
FROM tblSearchVaultsArchived tsva
JOIN tblIntSearches tis ON tsva.SearchID = tis.SearchID
JOIN tblStatus ts1 ON tis.StatusID = ts1.StatusID
JOIN tblStatus ts2 ON tsva.StatusID = ts2.StatusID
FULL OUTER JOIN tblVaults tv ON tsva.VaultID = tv.VaultID
JOIN tblVaultServers tvs1 ON tv.VaultServerID = tvs1.VaultServerID
JOIN tblVaultStore tvs2 ON tv.VaultStoreID = tvs2.VaultStoreID
FULL OUTER JOIN tblIndexVolumeSet tivs ON tsva.IndexVolumeSetID = tivs.IndexVolumeSetID
FULL OUTER JOIN tblIndexService tis2 ON tv.IndexServerID = tis2.IndexServerID
WHERE tsva.SearchID = @SearchID
--AND tsva.StatusID <> 13 ---Un-comment this line to show only those Archives that have not Finished
ORDER BY ts2.Name, tv.KVSVaultName
DECLARE @tsvaMinStartDate datetime
DECLARE @tsvaMaxEndDate datetime
SET @tsvaMinStartDate = (SELECT MIN(StartDate) FROM tblSearchVaultsArchived WHERE SearchID = @SearchID)
SET @tsvaMaxEndDate = (SELECT MAX(EndDate) FROM tblSearchVaultsArchived WHERE SearchID = @SearchID)
SELECT DISTINCT @tsvaMinStartDate AS 'Index Search StartDate'
, @tsvaMaxEndDate AS 'Index Search EndDate'
, CAST(CONVERT(nvarchar(10), DATEDIFF(DD, @tsvaMinStartDate, @tsvaMaxEndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvaMinStartDate, @tsvaMaxEndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
PRINT ''
PRINT 'End output from tblSearchVaultsArchived for SearchID ' + @SearchID + ' in Review'
PRINT ''
END
ELSE BEGIN
PRINT ''
PRINT 'Begin output from tblSearchVault for SearchID ' + @SearchID + ' in progress'
PRINT ''
SELECT tis.Name AS 'Search Name'
, ts3.Name AS 'Search State'
, tv.KVSVaultName AS 'Archive Name'
, tsv.StatusID AS 'Archive Search StatusID'
, ts4.Name AS 'Archive Search Status'
, tv.KVSVaultEntryID
, ISNULL(tsv.IndexVolumeSetID, -1) AS 'Accelerator Index Volume ID'
, ISNULL(tivs.EVIndexVolumeSetID,-1) AS 'EV Index Volume ID'
, ISNULL(tivs.FirstItemSeqNum, -1) AS 'First Item Sequence Number'
, tsv.Info AS 'Information'
, tsv.NumHits AS 'Number of Hits'
, tsv.StartDate AS 'Index Volume Search StartDate', tsv.EndDate AS 'Index Volume Search EndDate'
, CAST(CONVERT(nvarchar(10), DATEDIFF(DD, tsv.StartDate, tsv.EndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, tsv.StartDate, tsv.EndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
, tv.VaultServerID, tvs1.VaultServer 'Vault Server Name'
, tv.VaultStoreID, tvs2.Name 'Vault Store Name'
, tv.IndexServerID, tis2.IndexServiceEntryID
FROM tblSearchVault tsv
JOIN tblIntSearches tis ON tsv.SearchID = tis.SearchID
JOIN tblStatus ts3 ON tis.StatusID = ts3.StatusID
JOIN tblStatus ts4 ON tsv.StatusID = ts4.StatusID
FULL OUTER JOIN tblVaults tv ON tsv.VaultID = tv.VaultID
JOIN tblVaultServers tvs1 ON tv.VaultServerID = tvs1.VaultServerID
JOIN tblVaultStore tvs2 ON tv.VaultStoreID = tvs2.VaultStoreID
FULL OUTER JOIN tblIndexVolumeSet tivs ON tsv.IndexVolumeSetID = tivs.IndexVolumeSetID
FULL OUTER JOIN tblIndexService tis2 ON tv.IndexServerID = tis2.IndexServerID
WHERE tsv.SearchID = @SearchID
--AND tsv.StatusID <> 13 ---Un-comment this line to show only those Archives that have not Finished
ORDER BY ts4.Name, tv.KVSVaultName
DECLARE @tsvMinStartDate datetime
DECLARE @tsvMaxEndDate datetime
SET @tsvMinStartDate = (SELECT MIN(StartDate) FROM tblSearchVault WHERE SearchID = @SearchID)
SET @tsvMaxEndDate = (SELECT MAX(EndDate) FROM tblSearchVault WHERE SearchID = @SearchID)
SELECT DISTINCT @tsvMinStartDate AS 'Index Search StartDate'
, @tsvMaxEndDate AS 'Index Search EndDate'
, CAST(CONVERT(nvarchar(10), DATEDIFF(DD, @tsvMinStartDate, @tsvMaxEndDate)) AS nvarchar(10)) + ' days, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000/3600000 AS nvarchar(10)) + ' hrs, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000%3600000/60000 AS nvarchar(10)) + ' mins, ' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000%3600000%60000/1000 AS nvarchar(10)) + '.' + CAST(CONVERT(nvarchar(10),(DateDiff(millisecond, @tsvMinStartDate, @tsvMaxEndDate)))%86400000%3600000%60000%1000 AS nvarchar(10)) + ' secs' AS 'Index Volume Search Time'
FROM tblSearchVault tsv
JOIN tblIntSearches tis ON tsv.SearchID = tis.SearchID
PRINT ''
PRINT 'End output from tblSearchVault for SearchID ' + @SearchID + ' in progress'
PRINT ''
END
Hope this helps.
Kind Regards,
Mohamed Nemazie
