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 environmentfor 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 thatwere active but have since been closed, removing the holds. Many of the holds overlap, as well. We have successfully tested expiry in a near-duplicateQA 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: Asimplecount 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) Ameans by which at least a subset of physical savesets could belocated and presented After digging around the forums for quite some time, I've tested versions ofJesusWept3'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 thatJesusWept3knows 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 anotherhours.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