Video Screencast Help

Need a SQL Query to find out SIS information for a particular vault store based on a date range

Created: 24 May 2013 • Updated: 05 Jun 2013 | 7 comments
modemis's picture
This issue has been solved. See solution.

Hello all.

Enterprise Vault reporting includes a SIS report on a vault store group but I need a report for a particular vault store based on a date range.  We have just started using EMC Isilon device and wanted to find out how much EV SIS is benefiting us on that device. So we need a query to give us the SIS information for a particular vault store based on a date range.  We would really appeciate it.

Thank you in advance.

Operating Systems:

Comments 7 CommentsJump to latest comment

modemis's picture

No, works just like any other NTFS device.  Sharing is enabled on Vault Store Group level.

Thank you.

JesusWept3's picture

This is the best I could do for now, its definitely not exact and honestly would take a lot of work to get a true accurate reflection as to how SIS is being performed overall

DECLARE @startDate dateTime
DECLARE @endDate   dateTime

SET @startDate = '2012-01-01 00:00:00.000'
SET @endDate   = '2013-01-01 00:00:00.000'

USE EVVSYourVaultStore_01
      (SELECT COUNT(idTransaction)
       FROM   Saveset
       WHERE  ArchivedDate >= @startDate 
         AND  ArchivedDate <= @endDate) "Total Archived Items",
      (SELECT SUM(itemSize)/1024
       FROM   Saveset
       WHERE  ArchivedDate >= @startDate
         AND  ArchivedDate <= @endDate) "Total Size (MB)",
      (SELECT COUNT(sis.SISPartIdentity) 
       FROM   saveset_sispart sis, Saveset s
       WHERE  s.SavesetIdentity = sis.SavesetIdentity 
         AND  s.ArchivedDate >= @startDate
         AND  s.ArchivedDate <= @endDate) "SIS Parts Referenced",
      (SELECT COUNT(SISPartIdentity)
       FROM   SISPart 
       WHERE  ArchivedDateUTC >= @startDate
         AND  ArchivedDateUTC <= @endDate) "Total SIS Parts"
modemis's picture

Thank you JW3.  I will try it out and reply back with results.

modemis's picture

Hi JW3.  I ran the above query from dates 04/01/2013 to 05/28/2013 on one of the Journal vault store db's and received the following output.  Don't really understand the output though.

Total Archived Items     Total Size (MB)     SIS Parts Referenced    Total SIS Parts

15882416                      1531134                 6925957                        4045594


Total Archived Items Total Size (MB) SIS Parts Referenced Total SIS Parts

Total Archived Items Total Size (MB) SIS Parts Referenced Total SIS Parts

JesusWept3's picture

Total Archived Items
Individual Emails archived from Exchanged, does not include attachments
So if you archive one item with ten attachments, this counts as one item
So you archived 15.8m individual emails from your Exchange Journal Mailbox

Total Size (MB)
Accumulated Size in MB of the items archived in Enterprise Vault, this is not the original size in Exchange, but the size after compression etc in Enterprise Vault.
So 15.8M items turns in to 1.45TB of archived data.
This total size however does not account for the fact that some items have been Single Instanced.
So if you have a 1MB PDF that already exists in EV, the database will record the item size as 1MB, instead of 30K.

SIS Parts Referenced
A SIS Part is typically an Attachment (such as MS Word Document, Excel Spreadsheets, PDF files etc)
So SIS Parts referenced means that in the 58 day range, you have emails that altogether reference 6.9m attachments.

If you have an email sent to 10 people, and they all include 5 attachments
You would see 50 SIS Part references, as those ten email all link to the same 5 SIS'd attachments

The 6.9 million referenced items could refer to SIS parts outside of that date range or other vault stores in the same vault store group

Total SIS Parts
This means the total amount of items archived that have NEW attachments that do not exist in the EV Fingerprint database, so you have 4 million items that were larger than 32k, did not already exist, so EV shared them out so in the future if items get archived with the same attachment, they can link to these new Sharable parts.

modemis's picture

Thank you very much for the explanation.  So I'm guessing that we really cannot get how much storage we saved with SIS for a vault store unless a complex query is written.  Our objective was to try and find out how much disk space we saved by utilizing EV SIS within a certain amount of time for a vault store or vault store group.

I think the above query will still help us so thank you for taking the time to write it for us.