cancel
Showing results for 
Search instead for 
Did you mean: 

Summarize Archived Data

Elio_C
Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

 

Regards. Gertjan

View solution in original post

3 REPLIES 3

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

 

Regards. Gertjan

Elio_C
Level 6

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) 

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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)

Regards. Gertjan