Seeking SQL query returning per-user count of (non-legal hold) items to be expired from EV

Question

Hi all,Long-time listener, first-time caller.Our customer is looking to turn on expiry in their environment&nbsp;for the first time.&nbsp;They use EV in a non-traditional manner, so of 32MM+ items, upwards of 2/3 (maybe even 9/10)&nbsp;are on legal hold via DA. Effectively the only items not on legal hold are from cases that&nbsp;were active but have since been closed, removing the holds. Many of the holds overlap, as well.&nbsp;We have successfully tested expiry in a near-duplicate&nbsp;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&nbsp;simple&nbsp;count of items to be removed per user would be the bare minimum.A better solution would include retention categoriesThe perfect query would provide:Granular item-by-item reporting (for at least some users)A&nbsp;means by which at least a subset of physical savesets could be&nbsp;located and presented&nbsp;After digging around the forums for quite some time, I've tested versions of&nbsp;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&nbsp;I'm considering adding DA to my lab to test it, but I have little doubt that&nbsp;JesusWept3&nbsp;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&nbsp;hours.&nbsp;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

chrislangevin · Answer

I know you're looking for a SQL query, but have you considered using the index for this?
The ndte property represents the number of days until expiry for an item. You can run a search for all items that currently qualify for expiry by searching
ndte:&lt;0
One advantage here is that it is easy to see the complete item-by-item details and previews using EV Search, as opposed to trying to scrounge what item properties are available in SQL (not many).
The other advantage is that if your item is subject to a Retention Plan, then the Retention Category listed on its Saveset record may not be the actual Retention Category against which its eligibility for expiry is calculated. The ndte index property takes this Retention Plan possibility into account and is therefore more accurate (provided your indexes are current, of course).
You can get environment-wide results by running the same search in Discovery Accelerator.
If you prefer to center the search around a specific date instead of number-of-days-until, you can also use the edat property to do that kind of search.
--Chris

chrislangevin · Answer

Dan,
Based on this further information, then it does seem we will need to use SQL to get this information. I ran it by our DA support staff and they suggested you open a DA support case and they will assist you with it. Best of luck.
&nbsp;
--Chris

jesuswept3 · Answer

Just as a matter of interest, which query is it because I think I've written eight iterations of it over the years

andrewb · Answer

I didn't see this pointed out specifically so I figured it's worth chiming in. JesusWept3's&nbsp;script works in the lab where we have 1000 archived items and 150 on hold. However, in the QA&nbsp;environment with the customer's data, we have 30 million items with 24 million on hold (and multiple holds with lots of overlap) the query has been running for 48 hours and still hasn't completed. The QA has no other activity going on.

dan_l · Answer

ChrisLangevin wrote:I know you're looking for a SQL query, but have you considered using the index for this?Hi Chris! First, thanks so much for your reply! I hadn't considered either of the properties you mentioned (SQL seemed a better fit off the top of my head) but am of course not opposed to anything that provides the end results meet the customer's needs. I should mention that prior to the review of these results, the customer has asked that all items with non-default retention categories be changed (via SQL update statement) to the default retention category, after which the indexes are being rebuilt.The ndte property represents the number of days until expiry for an item. You can run a search for all items that currently qualify for expiry by searchingndte:&lt;0One advantage here is that it is easy to see the complete item-by-item details and previews using EV Search, as opposed to trying to scrounge what item properties are available in SQL (not many).The other advantage is that if your item is subject to a Retention Plan, then the Retention Category listed on its Saveset record may not be the actual Retention Category against which its eligibility for expiry is calculated. The ndte index property takes this Retention Plan possibility into account and is therefore more accurate (provided your indexes are current, of course).Since this is EV11, not EV12, Retention Plans are not going to be a factor. I'm wondering, though, if this language about retention plans would apply to Legal Holds. If a given item was subject to expiry but also blocked from expiry by DA Legal Holds, would the user's item(s) show as expirable using either ndte or edat?You can get environment-wide results by running the same search in Discovery Accelerator.If you prefer to center the search around a specific date instead of number-of-days-until, you can also use the edat property to do that kind of search.DA seems like the better option, except for the fact that the transition from Prod DA into QA DA has left some searches unable to conclude the way one would hope (for example, one case has 18MM items and two weeks later, no legal holds have applied).Again, many thanks for the quick reply!

Forum Discussion

Seeking SQL query returning per-user count of (non-legal hold) items to be expired from EV

Related Content

nbcheck returned exit status 255

nbcheck returned exit status 127 during client install

Agentless Vmware restore return error

Archive Usage Limit per users!

SQL Query to find archived messages & archive item size per mailbox user