DA11, Looking for an SQL Query for all searches run with how long each search took
DA11, Looking for an SQL Query for all searches run with how long each search took. We can get the searches - no issue - can only seem to pull creation and modifed dates - but looking for time stamp for duration of each search. use [Database] 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 Name 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
Seeking SQL query returning per-user count of (non-legal hold) items to be expired from EV
Hi all, Long-time listener, first-time caller. Our customer is looking to turn on expiry in their environment for the first time. They use EV in a non-traditional manner, so of 32MM+ items, upwards of 2/3 (maybe even 9/10) are on legal hold via DA. Effectively the only items not on legal hold are from cases that were active but have since been closed, removing the holds. Many of the holds overlap, as well. We have successfully tested expiry in a near-duplicate QA EV environment and removed about 2MM items without incident. After presenting that result, the customer's IT team has requested that we add an additional step to present to legal before advancing into Production. They'd like as granular a report as possible of what will be removed so they can confirm the data to be expired should be expired. Criteria are below: A simple count of items to be removed per user would be the bare minimum. A better solution would include retention categories The perfect query would provide: Granular item-by-item reporting (for at least some users) A means by which at least a subset of physical savesets could be located and presented After digging around the forums for quite some time, I've tested versions of JesusWept3's query (which looks like it should do exactly what I need) but: In my lab (which does not have DA) I need to remove any references to the holdsaveset table or else it returns no results I'm considering adding DA to my lab to test it, but I have little doubt that JesusWept3 knows his stuff. I'm confident that it will work. In the QA environment, looking at just one of three vault stores, I stopped it after about 36 hours of execution, made some modifications and was unable to make it work more efficiently. I started it again and it has still not completed running after another hours. Even if it does conclude after, say, 48 hours, this is not likely to be an acceptable option to management. Please let me know if you have any tweaks, experience or advice to offer