cancel
Showing results for 
Search instead for 
Did you mean: 

Identify Retention Category of items for specific users

Scott_Riser
Level 4
Partner Accredited

I have a customer who went a little overboard with their use of EVPM and applied several Folder Level Retention Categories that they now want to change but they're unsure of who they applied it to and what the exact Retention Categories were. I found the following SQL Query but it provides a listing of ALL users, is there any way of narrowing the search down to specific users or better yet, a specific Provisioning Group?

SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName

If I'm missing another possible option, please speak up. I would also like to see if anyone has any ideas on how to retro-actively modify the retention categories (I'm thinking of simply modifying the Retention Category within the SaveSet Table of the Vault Database). I look forward to everyone'se responses and thank everyone ahead of time.

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

OK So it wasn't on this forum, but it was for another customer, and it was a complex set up
The steps were as follows

1. Create a sql query to output the users details and the count of items for each retention category the user has
2. Export the results to an excel spread sheet
3. Wait another day or two then run the same query
4. Export the results to an excel spread sheet
5. Create a new database with several tables
6. Import the Excel spread sheets to seperate tables
7. Run SQL Scripts to compare Retention Category counts for each user


So for instance you may see

Day 1:
JesusWept2, 3 Year Retention, 400 items
JesusWept2, 7 Year Retention, 200 items

Day 2:
JesusWept2, 3 Year Retention, 400 items
JesusWept2, 7 Year Retention, 700 items


so that point the SQL Query would make the determination that the 3 year retention is dormant where as the 7 year retention has grown and there for is most likely the active retention category.

The issue is though if you genuinely have retention folders set up, which a lot of customers do, you may have people sorting hundreds of items in to different folders for different retentions, to which this all becomes a little pointless.

Regardless as to *how* its currently set up, you know two things
1: How it *should* be set up
2: That something definitely has gone wrong

Knowing that, you have nothing to worry about by zapping and removing all the EVPM settings you have made and just resetting it to how it should be, i don't think analyzing the extent of the damage currently will gain you much

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

6 REPLIES 6

JesusWept3
Level 6
Partner Accredited Certified

SQL Unfortunately will not tell you what particular folders have what retention category, the output of the query you are seeing is the default retention category assigned to them from the provisioning task, so if you get a result and you see that they have say a "3 Years" retention category being listed, that does not guarantee their entire mailbox is set to three years, it could be that they have slapped another retention category on inbox.

The only way to be able to tell the retention category is on the client themselves.

Your best option is to zap every single user, undoing the EVPM permissions, then enabling them in a more suitable manner

https://www.linkedin.com/in/alex-allen-turl-07370146

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

Agreed with JW2, reset the retention categories and then it might be possible to use Update Moved Items to update the retention category.  Although I am not 100% if it will work since the items aren't moved and just the retention has been changed.

Scott_Riser
Level 4
Partner Accredited

We're talking thousands of emails across dozens of users on multiple Exchange servers; there's absolutely no way of querying for the messages? Retention Category is a column in the SaveSet table, there's got to be a way of associating that with the corresponding items.

JesusWept3
Level 6
Partner Accredited Certified

I wrote a script ages ago for andrewB i think, let me see if i can find it, it makes an assumption on the active RetentionCategory but is not a guarantee

https://www.linkedin.com/in/alex-allen-turl-07370146

JesusWept3
Level 6
Partner Accredited Certified

OK So it wasn't on this forum, but it was for another customer, and it was a complex set up
The steps were as follows

1. Create a sql query to output the users details and the count of items for each retention category the user has
2. Export the results to an excel spread sheet
3. Wait another day or two then run the same query
4. Export the results to an excel spread sheet
5. Create a new database with several tables
6. Import the Excel spread sheets to seperate tables
7. Run SQL Scripts to compare Retention Category counts for each user


So for instance you may see

Day 1:
JesusWept2, 3 Year Retention, 400 items
JesusWept2, 7 Year Retention, 200 items

Day 2:
JesusWept2, 3 Year Retention, 400 items
JesusWept2, 7 Year Retention, 700 items


so that point the SQL Query would make the determination that the 3 year retention is dormant where as the 7 year retention has grown and there for is most likely the active retention category.

The issue is though if you genuinely have retention folders set up, which a lot of customers do, you may have people sorting hundreds of items in to different folders for different retentions, to which this all becomes a little pointless.

Regardless as to *how* its currently set up, you know two things
1: How it *should* be set up
2: That something definitely has gone wrong

Knowing that, you have nothing to worry about by zapping and removing all the EVPM settings you have made and just resetting it to how it should be, i don't think analyzing the extent of the damage currently will gain you much

https://www.linkedin.com/in/alex-allen-turl-07370146

Scott_Riser
Level 4
Partner Accredited

The real crux of the matter is that the emails that are bound by the erroneous Retention Categories are going to expire and from a compliancy standpoint we can't let that happen. So if we can't identify the items that have been archived with erroneous Retention Categories that means we can't change them. Zapping the mailboxes and re-enabling them won't accomplish what we need because it won't apply the Categories retroactively to previously archived items. I'm all for killing the EVPM scripts and have recommended as much but that doesn't fix the compliancy issue. This is a really tight situation and short of manipulating the database, I don't know if it can be saved.