Wednesday, October 10, 2007

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)
  7. 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
  8. Next paste the remaining snippets to create the stored procedures
  9. Then to populate the tables use the created stored procedures by using the following snippet
  10. use metricsops
    exec cb_q1_proc
    exec cb_q2_proc
    exec cb_q3_proc
    exec cb_q4_proc
    exec cb_q5_proc
  11. 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
  12. Connect using a front-end application (Excel, Report Server) to view those tables in nicely way in SharePoint ;)


 

Technorati tags: , , , ,

3 comments:

Anonymous said...

Thanks!


So what happens if I "install" this on a production system?

Will I then be in an "unsupported" mode becuase of the database access.

/J

Robin Meuré said...

I don't think it's really unsupported (they use it themself ;)) and you are not making modifications to the database itself. It's only extracting data from the database.

But(!) it's not a best practice. Read Mike's post about not accessing the (production) databases yourself with tools like this : http://blogs.msdn.com/mikefitz/archive/2005/04/01/404802.aspx

electronic signature pad said...

Thanks! This article was very helpful and saved me many hours of digging and experimentation.