Overview of sites and content DBs in large installations

Every MOSS administrator of a large installation with many web applications and sites will know the problem, that you can view all necessary information about site sizes, content databases and so on, but you have switch between several pages to get all the detail one would like to view. Therefore I’ve created a little tool, that collects all the information from the Sharepoint API and writes it to an XML file. From that file you can easily create different HTML reports by using XSLT (I’m very fond of XML and XSLT, you know).
Below is the source code of the .NET application and an example xml and xslt file. I have some problems attaching an exe or zip file, so you have to compile yourself. If you have suggestions where I could permanently store small files to be linked here, please email me.

Please note, that the account running the application must have access to all sites. Maybe I create an enhanced version that uses "run with evelated privileges". Another item on the wishlist is to have the effective database size (internal) instead of the allocated diskspace.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.Web;
using System.IO;
using System.Xml.Serialization;
using System.Data.SqlClient;
using System.Data.Sql;

namespace SharepointAdminReport
{
    class ReportGenerator
    {
        static void Main(string[] args)
        {
            if (args.Length <2 || args[0] == null || args[1]== null || !args[0].StartsWith("http://"))
            {
                System.Console.WriteLine("You must specify the URL of a site in the webapplication and an output file!");
                System.Console.WriteLine("Usage: spadmreport url filename");
                System.Console.WriteLine("Example: spadmreport http://corp.sp.rehau.org d:\report070925.xml");
                return;
            }
           
            String url = args[0];
            String filename = args[1];
            System.Console.WriteLine("filename: " + filename);
            System.Console.WriteLine("URL: " + url);
            XmlDocument xDoc = new XmlDocument();
            if (args.Length == 3 && args[2] != null && args[2] == "-append")
            {
                try
                {
                    FileStream fs = new FileStream(filename, FileMode.Open);
                    xDoc.Load(fs);
                    fs.Close();
                }
                catch (Exception )
                {
                    System.Console.WriteLine("Could not open file! Creating new one!");
                    xDoc.LoadXml("<?xml version=’1.0′ encoding=’UTF-8′?><report/>");
                }
            }
            else
            {
                xDoc.LoadXml("<?xml version=’1.0′ encoding=’UTF-8′?><report/>");
            }
            XmlNode rootNode = xDoc.DocumentElement;
            SPSite rootSite = null;
            try
            {
                rootSite = new SPSite(url);
                SPWeb web = rootSite.OpenWeb();
                SPWebApplication webApp = rootSite.WebApplication;
                XmlElement webAppNode = (XmlElement)rootNode.AppendChild(xDoc.CreateElement("WebApplication"));
                webAppNode.SetAttribute("name", webApp.Name);
                webAppNode.SetAttribute("url", rootSite.Url);
                foreach (SPContentDatabase contentDB in webApp.ContentDatabases)
                {
                    XmlElement dbNode = (XmlElement) webAppNode.AppendChild(xDoc.CreateElement("ContentDatabase"));
                    dbNode.SetAttribute("id", contentDB.Id.ToString());
                    dbNode.SetAttribute("name", contentDB.Name);
                    dbNode.SetAttribute("dbServer", contentDB.ServiceInstance.DisplayName);
                    dbNode.SetAttribute("numSites", contentDB.CurrentSiteCount.ToString());
                    dbNode.SetAttribute("maxSites", contentDB.MaximumSiteCount.ToString());
                    dbNode.SetAttribute("diskSize", contentDB.DiskSizeRequired.ToString());
                    foreach (SPSite site in contentDB.Sites)
                    {
                        XmlElement siteNode = (XmlElement)dbNode.AppendChild(xDoc.CreateElement("Site"));
                        siteNode.SetAttribute("id", site.ID.ToString());
                        siteNode.SetAttribute("name", site.RootWeb.Title);
                        siteNode.SetAttribute("url", site.RootWeb.Url);
                        siteNode.SetAttribute("relativeUrl", site.RootWeb.ServerRelativeUrl);
                        siteNode.SetAttribute("numSubSites", (site.AllWebs.Count – 1).ToString());
                        siteNode.SetAttribute("size", site.Usage.Storage.ToString());
                        siteNode.SetAttribute("bandwidth", site.Usage.Bandwidth.ToString());
                        siteNode.SetAttribute("hits", site.Usage.Hits.ToString());
                        siteNode.SetAttribute("visits", site.Usage.Visits.ToString());
                        siteNode.SetAttribute("numUsersInRoot", site.RootWeb.Users.Count.ToString());
                    }
                }
            }
            catch (Exception ex1)
            {
                System.Console.WriteLine("Could not open site at URL " + url);
                System.Console.WriteLine(ex1.Message);
                System.Console.WriteLine(ex1.StackTrace);
                return;
            }
            try
            {
                XmlTextWriter writer = new XmlTextWriter(filename, Encoding.UTF8);
                writer.Indentation = 3;
                writer.Formatting = Formatting.Indented;
                xDoc.WriteTo(writer);
                writer.Flush();
                writer.Close();
            }
            catch(Exception ex)
            {
                System.Console.WriteLine("Unable to create output file: " + ex.Message);
            }
        }
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="details.xsl"?>
<report>
    <WebApplication name="SharePoint – region1" url="http://region1.company.org">
        <ContentDatabase id="9b2b711d-5828-4a41-b8f9-2904b646fbda" name="wss_region1_009" dbServer="dbsp1.company.com" numSites="1" maxSites="15" diskSize="17825792000">
            <Site id="6b3e6f2d-2664-427a-8d56-a751771f126e" name="Die Region 1 informiert" url="http://region1.company.org/sites/r1-Info" relativeUrl="/sites/r1-Info" numSubSites="3" size="18884441" bandwidth="616806" hits="3157" visits="924" numUsersInRoot="3"/>
        </ContentDatabase>
        <ContentDatabase id="67b2eb13-c97b-4b00-8a37-e3963fefc787" name="wss_region1_001" dbServer="dbsp2.company.com" numSites="8" maxSites="8" diskSize="17825792000">
            <Site id="f5ddc5c6-8ab7-4d13-bfba-d3ceaaa2c74a" name="Einstiegsseite" url="http://region1.company.org" relativeUrl="/" numSubSites="0" size="298168" bandwidth="102" hits="737" visits="734" numUsersInRoot="2"/>
            <Site id="54ea8a2c-112d-409e-861a-b1f85d747bda" name="teamsite1" url="http://region1.company.org/sites/teamsite1" relativeUrl="/sites/teamsite1" numSubSites="1" size="596943782" bandwidth="11919966" hits="94784" visits="64872" numUsersInRoot="202"/>
        </ContentDatabase>
    </WebApplication>
</report>

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
    <xsl:decimal-format  grouping-separator="." decimal-separator=","  />
    <xsl:template match="/">
        <html>
            <head>
                <title>Sharepoint Admin Report</title>
                <style type="text/css">
                    th, td { border: thin solid black; padding-left:5px; padding-right: 5px;}
                </style>
            </head>
            <body>
                <h1>Sharepoint Admin Report</h1>
                <xsl:apply-templates select="/report/WebApplication"/>
            </body>
        </html>
    </xsl:template>
    <xsl:template match="WebApplication">
        <h2>WebApplication: <xsl:value-of select="./@name"/> (URL:<xsl:value-of select="./@url"/>)</h2>
        <table style="border-collapse:collapse;">
            <tbody>
                <tr>
                    <th>Site-Title</th><th>url</th><th>num subsites</th><th>disk size</th><th>visits</th><th>bandwidth</th>
                </tr>
                <xsl:apply-templates select="./ContentDatabase/Site">
                    <xsl:sort select="./@url" order="ascending"/>
                </xsl:apply-templates>
                <tr>
                    <td>Summary</td>
                    <td><xsl:value-of select="count(./ContentDatabase/Site)"/> sites in web application</td>
                    <td><xsl:value-of select="sum(./ContentDatabase/Site/@numSubSites)"/></td>
                    <td><xsl:value-of select="format-number(number(sum(./ContentDatabase/Site/@size)) div 1000000, ‘#.###.##0′)"/> MB</td>
                    <td><xsl:value-of select="sum(./ContentDatabase/Site/@visits)"/></td>           
                    <td><xsl:value-of select="format-number(sum(./ContentDatabase/Site/@bandwidth) div 1000000, ‘#.###.##0′)"/> MB</td>
                </tr>               
            </tbody>
        </table>
    </xsl:template>
    <xsl:template match="Site">
        <tr>
            <td><xsl:value-of select="./@name"/></td>
            <td><a href="{./@url}"><xsl:value-of select="./@relativeUrl"/></a></td>
            <td><xsl:value-of select="./@numSubSites"/></td>
            <td><xsl:value-of select="format-number(number(./@size) div 1000000, ‘#.###.##0′)"/> MB</td>
            <td><xsl:value-of select="./@visits"/></td>           
            <td><xsl:value-of select="format-number(number(./@bandwidth) div 1000000, ‘#.###.##0′)"/> MB</td>   
        </tr>
    </xsl:template>
</xsl:stylesheet>