02-09-2015 02:40 AM
Hi,
I'm trying to make a case for storage expiry. One particular thing I'm after is a way to summarise the archived data by date, sent/received date, something like a count for specific periods (months/years). Size info would be nice but not a show stopper. It's hopefully a onetime thing.
Something like this would be perfect:
Period Archived Items Size GB
June 2010 2,000,000 150 GB
July 2010 1,800,000 130 GB
August 2010 1,700,000 124 GB
Any Reports/Tools I can use?
Any SQL queries I can run? There is an 'IDDateTime' in the saveset table, can I use this to count archived items? or which table/columns could I query?
Any help greatly appreciated,
Thanks
Solved! Go to Solution.
02-09-2015 03:19 AM
Hi,
I use the below. Run against the Vault Store. Change the dateadd("YY", -1, getdate ()) to -5 to get it for 5 years, same applies for months.
--Yearly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("YY", -1, getdate ())
group by left (convert (varchar, archiveddate,20),4)
order by "Archived Date" desc
--Monthly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("mm", -5, getdate ())
group by left (convert (varchar, archiveddate,20),7)
order by "Archived Date" desc
This is for EV10, should also work for older versions.
02-09-2015 03:19 AM
Hi,
I use the below. Run against the Vault Store. Change the dateadd("YY", -1, getdate ()) to -5 to get it for 5 years, same applies for months.
--Yearly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("YY", -1, getdate ())
group by left (convert (varchar, archiveddate,20),4)
order by "Archived Date" desc
--Monthly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("mm", -5, getdate ())
group by left (convert (varchar, archiveddate,20),7)
order by "Archived Date" desc
This is for EV10, should also work for older versions.
02-09-2015 05:07 AM
Excellent and many thanks GertjanA
Quick modify to use the sent/received date (IDDateTime, I hope) and it's exactly what I was after
SELECT "Sent/Received Date" = left (convert (varchar, IdDateTime,20),7),"Archived Items" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024 FROM saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity WHERE IdDateTime > dateadd("mm", -252, getdate ()) GROUP BY left (convert (varchar, IdDateTime,20),7) ORDER BY "Sent/Received Date" DESC
(Guess I have lots of malformed date emails. This returns lots of crazy years in the future, browsing some of the dates there are also lots of single 1900, 1912, 1970, 1980 etc.. emails too)
02-09-2015 05:26 AM
In regards to the dates, I have the same. these usually are spam-mails, or malformed mails. I have some mails from the future... (2024)