Enterprise Vault data report and category ?

[John_Santana]

Hi People,

How can I find and generate the data report as follows:

1. How long we can go back / retrieve from the EV (what's the oldest data that is stored in EV) ?
2. How can I show / categorize data size by year to determine the purge policy to free up disk space ?
 

I cannot find it in the EV console.

Any idea and suggestion even in the SQL script would be greatly appreciated.

Thanks !

[TonySterling · Accepted solution]

Here are a couple queries to use for month\year, just change the number after mm or YY.

--Monthly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count (*),"Size" = sum (itemsize)/1024
from saveset
where archiveddate > dateadd("mm", -12, getdate ())
group by left(convert (varchar, archiveddate,20),7)
order by "Archived Date" Desc

--Yearly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"Size" = sum (itemsize)/1024
from saveset
where archiveddate > dateadd("YY", -3, getdate ())
group by left(convert (varchar, archiveddate,20),4)
order by "Archived Date" Desc

For the oldest archived item see this post: https://www-secure.symantec.com/connect/forums/determine-oldest-items-vault#comment-6583751.  It has this:

use <name of vault store db>

select top 10 * from Saveset

order by IdDateTime asc

[Rob_Wilcox1]

I, personally, wouldn't expire 1 years worth of data at a time, especially if the amount is 'several TB'.  Better for it to be in smaller chunks.

[John_Santana]

Exactly Tony, That is why I need to know how many GB or TB I can save by deleting the item in certain period of time (eg. reducing the EV retention period into just the past 3 years).

Because from the script below:

SELECT TOP(1) ss.iddatetime, 

                SS.archiveddate, 

                SS.idtransaction, 

                ss.indexseqno, 

                AP.archivepointidentity, 

                ME.mbxalias, 

                ME.defaultvaultid 

FROM   saveset SS 

       JOIN archivepoint AP 

         ON SS.archivepointidentity = AP.archivepointidentity 

       JOIN enterprisevaultdirectory.dbo.exchangemailboxentry ME 

         ON AP.archivepointid = ME.defaultvaultid 

ORDER  BY ss.iddatetime ASC

the oldest Item that is in the EV VaultStore is dated back to 1998

[John_Santana]

Thanks for the script Tony,

I'd like to see it categorize by year, so in this case to see it how many GB / TB I can save by deleting or purging that year.

The script:

SELECT CONVERT(VARCHAR(7), idDateTime, 120) "Sent Dates",

       COUNT(IdDateTime) "No. Items Archived"

FROM   Saveset

GROUP BY CONVERT(VARCHAR(7), idDateTime, 120)

ORDER BY CONVERT(VARCHAR(7), idDateTime, 120)

can only shows item counts not in GB or TB size.

[TonySterling]

Did you see my yearly rate script in the first post?

--Yearly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"Size" = sum (itemsize)/1024
from saveset
where archiveddate > dateadd("YY", -3, getdate ())
group by left(convert (varchar, archiveddate,20),4)
order by "Archived Date" Desc

[John_Santana]

Ah yes :-), what I mean is that in MB or in GB ?

[TonySterling]

Should be MB.  If you add another /1024 like below to have it show GB.

--Yearly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"Size" = sum (itemsize)/1024/1024
from saveset
where archiveddate > dateadd("YY", -3, getdate ())
group by left(convert (varchar, archiveddate,20),4)
order by "Archived Date" Desc

[John_Santana]

Thanks Tony, that does make sense after all.

[John_Santana]

Rob, at the moment I don't know what to do apart from enabling the expiry period policy and then enabling the archival deletion to free up some disk space.

I'm not sure as to how and offloading to a tape will works with EV, how does the users get notified when the data is on tape drive or no longer in EV ?

[Rob_Wilcox1]

Hi John,

Tony's queries will tell you about the amount of data involved.

If you use a EV Migrator eg Migrator to Netbackup then users will still have seamless access to the data.  When they try to recall something, it'll go through EV to NBU, to tape/disk/etc and back.  Of course depending *where* exactly the item is stored may mean it times out for the user, but, they'll be told that, and if they try again 'soon' afterwards the data will likely be there by then.

[TonySterling]

I would highly caution against use the EV to NBU migrator.  Personally, I never recommend it.  I have just seen too many issues and there is no easy "Exit Strategy" once you deploy it.

If you do decide to deploy I would make sure your indexes have an overly cautious backup plan.  Rebuilding indexes for items on tape will be a major PITA!

Just my 2p. 

[Rob_Wilcox1]

I agree :)

[John_Santana]

Many thanks to all who responded to this thread, so in this case there is no way to retrieve the data once it is written off to tape andthen purged ?

My backup software is HP Data Protector and EMC Avamar, I guess both of them don't have the capability to mix with EV Migrator.

[John_Santana]

Tony, is that data result for both FSA and Exchange Server archival ?

[TonySterling]

Do you have more than one vault store or are both FSA and Exchange in the same vault store?

You need to run the query against each Vault Store Database you have.  It does not need to run against the Fingerprint DB.

[John_Santana]

Tony, I can see only one EVVaultStore DB in the SQL box, so does this means that it is just one EV Vault Store for Email and files ?

[TonySterling]

That is what it sounds like.  Unless you have another SQL Server.  You can verify by looking in the Vault Admin Console.

[John_Santana]

I've got what I need now Tony, many thanks for your assistance here :)