MS IT Usage and Storage Reporting Tool Released to Codeplex

As Joel and Mike point out in there latest blogposts.. Cory Burns published a MS, up until now, internal tool on CodePlex to monitor usage and storage of SharePoint environments:

I am pleased to announce I have finished version 2.0 of the MSIT SharePoint Reporting Framework.  Versions 1.0 was used heavily internally for our service metrics and operational tracking.  With version 2.0 many great improvements have come out of it, additionally with the release to the public :)  I encourage everyone to head over and check it out.  Thanks!!

In this post I will guide you on how to implement this since this is not an application which you can install clicking using a wizard (yet ;))..

1. Download the file from CodePlex
2. Logon remotely to the SQL Server of your acceptance environment
3. Start > All Programs > SQL Server 2005 > SQL Server Management Studio
4. Login with the same account as the database access account that is used for your SharePoint environment
5. Click on "New Query"
6. Paste the first snippet and click on Execute (the database needs to be created first before the stored procedures get built)

GO
/** MetricsOPs Database Creation **/
SET NOCOUNT ON
GO
IF Exists (select name from master..sysdatabases where name = 'metricsops')
PRINT 'Metricsops Database allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR6 NVARCHAR(4000)
SELECT @CMDSTR6 = '
create database metricsops
alter database metricsops
Modify file
(name = metricsops,
 filegrowth = 10%);
alter database metricsops
modify file
(name = metricsops_log,
filegrowth = 10%)
'
EXEC (@cmdstr6)
PRINT 'Metricsops Database Created.'
END

7. Next paste the remaining snippets to create the stored procedures
8. Then to populate the tables use the created stored procedures by using the following snippet

use metricsops
exec cb_q1_proc
exec cb_q2_proc
exec cb_q3_proc
exec cb_q4_proc
exec cb_q5_proc

9. To view the data, open up the tables
1. cb_q1 > Gives you an overview of all the sitecollections with data about sizing, last modified date, bandwidth usage, recycle-bin size, etc
2. cb_q2 > Gives you a total overview per config database which content databases host which sitecollection
3. cb_q3 > Gives you an overview of all the servers in the farm
4. cb_q4 > Gives you an overview of all the sitecollections and sites about pages (?)
5. cb_q5 > Gives you an overview of all the databases on the SQL Server with their sizes and unallocated space
10. Connect using a front-end application (Excel, Report Server) to view those tables in nicely way in SharePoint ;)

 

Technorati tags: MOSS, WSSv3, Capacity, Sizing, SQL