Video Screencast Help

Report for last week's archived items

Created: 18 Sep 2012 • Updated: 18 Sep 2012 | 8 comments
This issue has been solved. See solution.

Hi,

We are using EV 10.0.1 Exchange mailbox archiving

Is there any way to create a report using SQL query for 

No of items archived for last week (or for a specific date range)
Total size of these items

Many Thanks

Comments 8 CommentsJump to latest comment

TonySterling's picture

You can use this for your daily rate and add them up.

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

If you want to use custom dates change the where clause to something like this.

where archiveddate between '2012-05-01' and '2012-07-31'

SOLUTION
ia01's picture

Hi Tony,

The total size that the query display is that after Single Instanced or before?

Thanks

TonySterling's picture

Hiya,

This query isn't taking SIS or compression into account.  This is for how much was archived.

Regards,

John Santana's picture

Tony,

SELECT "Archived Date" = LEFT (CONVERT (VARCHAR, archiveddate, 20), 10), 
       "Daily Rate" = Count (*), 
       "Size" = Sum (itemsize) / 1024 
FROM   saveset 
WHERE  archiveddate > Dateadd("dd", -6, Getdate ()) 
GROUP  BY LEFT(CONVERT (VARCHAR, archiveddate, 20), 10) 
ORDER  BY "archived date" DESC 

does the size is in Kilobytes ?

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

TonySterling's picture

Hey John,

That puts it into Megabytes.

 

John Santana's picture

  "Size in MB" = Sum (itemsize) / 1024  --> in MB

  "Size in GB" = Sum (itemsize) / 1024 / 1024 --> in GB

thank you for the clarification :-)

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

ia01's picture

Awesome! Thanks very much Tony! Really appreciate your help!