Hello Ryan,
I am not sure the query I have works, but it is worth a try. I use it for Journal and Mailbox archiving. Run it against the Vault Store. In the Daily query, this section "dateadd("dd", -31, getdate ())" means it takes the current date, and then shows it for the last 32 days. If you need yesterday, use -2 instead of -31
--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
--Daily Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily 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("dd", -31, getdate ())
group by left (convert (varchar, archiveddate,20),10)
order by "Archived Date" desc
--Hourly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),14),"Hourly 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("hh", -24, getdate ())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc
Regards. Gertjan
