As this is going to be an ongoing exercise I am splitting this into parts.

At the moment I am busy finding ways to create different types of usage reports for MOSS deployments. There are some good tools out there that help with this (Reporting Framework from Codeplex) and of course the default MOSS usage reports. None of these however quite fit my or most of my clients requirements, some examples of which are:

  • We want to see how many documents were added in each month with a rolling total.
    • This must EXCLUDE folders, lists and list items (they only want to see what has been uploaded to the system).
  • Show total content on the system excluding content in the recycle bin
    • Eventually items are removed from the recycle bin and from all MOSS tables, when showing rolling totals items in the recycle bin will modify previous known figures.
  • Show percentage values of content type usage. (10% Agenda, 12% Minutes etc.)
  • Show percentage values of file type usage (10% Word Documents, 20% PDF Documents, etc.)
  • And more....

I have managed to achieve all the above and more and will be addind to this post as I have time.

First things first, what tables should be used for these reporting requirements? I am using the following:

  • Alldocs
    This contains records of everything that has been added to MOSS excluding the following:
    • Lists and Libraries
    • Sites
  • AllDocStreams
    This contains records of everything that has been added to MOSS excluding the following:
    • Lists and Libraries
    • List Items
    • Folders
    • Sites
  • AllUserData
    I am using this table as it contains extra information like "Content Type" and other data for items in the Alldocs and AlldocStreams tables that I need to use.
  • RecycleBin
    Self explanatory.
  • Alllists
    I need this to get list names for some of my other reports.
  • Webs
    I need this to get a breakdown of content usage per site.

Example: Show how many items have been added per month with a rolling total

NOTE: as always it is never recommended by MS that you run queries / stored procedures directly on a production DB, rather make copies of the required tables. (if you are like many of my clients who cannot afford the extra data storage, run these reports at midnight :) )

My SQL is rough - you have been warned :)

The following SQL query will get all items that have been added to the system, show how many were added per month and what each months rolling total was (usefull for a growth line):

USE {add your selected content database here}

SELECT DATENAME([month], x1.monthdate) + ' ' + DATENAME([year], x1.monthdate) AS [Month and Year], MAX(x1.added) AS [Added in Month], SUM(x2.added) AS [Rolling Total]

FROM

(SELECT MONTH(TimeCreated) AS monthno, DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0) AS monthdate, COUNT(*) AS added FROM alldocs WHERE id IN  (SELECT id FROM alldocstreams) GROUP BY DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0), MONTH(TimeCreated)) AS x1

INNER JOIN

(SELECT MONTH(TimeCreated) AS monthno, DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0) AS monthdate, COUNT(*) AS added FROM alldocs WHERE id IN  (SELECT id FROM alldocstreams) GROUP BY DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0), MONTH(TimeCreated)) AS x2

ON x1.monthdate >= x2.monthdate

GROUP BY x1.monthdate ORDER BY x1.monthdate

NOTE: The above query returns only items that have been uploaded into the environment, to show all content remove the following WHERE queries:
WHERE id IN (SELECT id from alldocstreams)

The above is just a sample of the query I used which is combined in my stored procs which read off of a custom reporting database. My queries go further as they automatically find all relevant content databases and run this query....this I will not show as we all need to learn :)

If I had place to put screenshots I would show how the pivot tables pulled into Excel Services work.

Part 2 here.