Matthew Knott

Setting up a Site Collection quota in SharePoint 2007 is a straight forward task, but how do you represent the remaining space back to the users?

For our specific example, we've set a site collection quota of 4gig and need a way to reflect this back to the site collection administrator. There is nowhere in the Site Collection admin for viewing the remaining space, and often you won't know until you get a warning from SharePoint.

What I'll show you is how to automatically collate the remaining space for the site collection by creating a small command line application to sit on a SharePoint front end.

There is an STSADM command called enumsites. You can get all the size data by running this command and getting an XML document back. Try it like this:

STSADM.EXE -o enumsites -url http://mySharePointSite

Alternatively, you can dump the results to file like this: 

STSADM.EXE -o enumsites -url http://mySharePointSite > siteinfo.xml

This will give you an idea of the data being returned, namely:

• Url - Site Collection URL
• Owner - Site Collection Owner
• SecondaryOwner - Secondary Site Collection Owner
• ContentDatabase - The Content Database used by the Site Collection
• StorageUsedMB - The amount of physical space used in Megabytes
• StorageWarningMB - The point at which warnings about the quota are generated
• StorageMaxMB - The Site Collections size limit

SecondaryOwner is an only returned if set, so allow for this in your code.

The really handy thing is that the data is returned in xml format. From here you have three main approaches you can chose, although I'll only cover one:

• Dump the results to an xml file and read that in through a webpart.
• Run the report "on demand" through a custom webpart.
• Run the report as a scheduled task and save the results to a SharePoint list.
• Run the report as a scheduled task and save the results to an SQL database.

Because it's that much easier to manage and setup, we'll save our usage report to the database, keeping only the most recent set of data.

Set up the database table

To set up the table execute the following SQL script:

1. CREATE TABLE [dbo].[stats_site_usage](
2. [url] [varchar](500) COLLATE Latin1_General_CI_AS NOT NULL,
3. [owner] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
4. [contentdb] [varchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
5. [storageused] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
6. [storagewarning] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
7. [storagemax] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
8. [lastupdate] [datetime] NOT NULL CONSTRAINT [DF_stats_site_usage_lastupdate] DEFAULT (getdate())
9. ) ON [PRIMARY]

I've decided to excluded secondary owner because I'm not at all interested in having that information, but if you want it then modify the structure accordingly.

Writing The Command Line Application

First, we'll gather the raw XML data.

1. XmlDocument siteDataFeed = new XmlDocument();
2. string functionCall = " -o enumsites -url http://mySharePointSite";
3. string s = string.Empty;
4. try
5. {
6. Process p = new Process();
7. p.StartInfo.FileName = @"C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe";
8. p.StartInfo.Arguments = functionCall;
9. p.StartInfo.UseShellExecute = false;
10. p.StartInfo.CreateNoWindow = true;
11. p.StartInfo.RedirectStandardError = true;
12. p.StartInfo.RedirectStandardOutput = true;
13. p.Start();
14. StreamReader pOutput = p.StandardOutput;
15. s = pOutput.ReadToEnd();
16. if (!p.HasExited)
17. {
18. p.Kill();
19. }
20. pOutput.Close();
21. p.Close();
22. }
23. catch (Exception ex)
24. {
25. Console.WriteLine(ex.Message);
26. }

Here, we execute STSADM command within our console application, an save the output to a string. The next job is to parse the results from the xml data and  store them data in the database.

1. if (!string.IsNullOrEmpty(s))
2. {
3. //Truncate Table
4. using (SqlConnection conn = new SqlConnection(@"server=SQL1\SQL1;Initial Catalog=my_DB;Persist Security Info=True;User ID=username;Password=password"))
5. {
6. SqlCommand cmd = new SqlCommand("TRUNCATE TABLE stats_site_usage", conn);
7. conn.Open();
8. cmd.ExecuteReader();
9. cmd.Dispose();
10. }
11. XmlDocument xmlDoc = new XmlDocument();
12. xmlDoc.LoadXml(s);
13. siteDataFeed = xmlDoc;
14. foreach (XmlNode status in siteDataFeed.DocumentElement)
15. {
16. string url = string.Empty;
17. string owner = string.Empty;
18. string contentDatabase = string.Empty;
19. string storageUsed = string.Empty;
20. string storageWarning = string.Empty;
21. string storageMax = string.Empty;
22. url = status.Attributes["Url"].InnerText;
23. owner = status.Attributes["Owner"].InnerText;
24. contentDatabase = status.Attributes["ContentDatabase"].InnerText;
25. storageUsed = status.Attributes["StorageUsedMB"].InnerText;
26. storageWarning = status.Attributes["StorageWarningMB"].InnerText;
27. storageMax = status.Attributes["StorageMaxMB"].InnerText;
28. using (SqlConnection conn = new SqlConnection(@"server=SQL1\SQL1;Initial Catalog=my_DB;Persist Security Info=True;User ID=username;Password=password"))
29. {
30. SqlCommand cmd = new SqlCommand("INSERT INTO stats_site_usage (url, owner, contentdb, storageused, storagewarning, storagemax) VALUES (" +
31. "'" + url.Replace("'", "''") + "', " +
32. "'" + owner.Replace("'", "''") + "', " +
33. "'" + contentDatabase + "', " +
34. "'" + storageUsed + "', " +
35. "'" + storageWarning + "', " +
36. "'" + storageMax + "')", conn);
37. conn.Open();
38. cmd.ExecuteReader();
39. cmd.Dispose();
40. }
41. }
42. }

Before parsing the XML we truncate the table to clear out the previous results. Run this now, and see what happens. Once the application has completed, check your database table and it should be full of rows of usage data.

Next Steps

Now you've done this once, you can set up a scheduled task to run it daily, or even hourly as we have. The question now is what do you do with the data. We haven't implemented the data on our portal yet but what I will probably do is take the value of SPContext.Current.Site.RootWeb and match that against the database to get the contextual Site Collection usage. You can then compare the storageMax value to the storageUsed value, and express the result in a number of ways. There are various .net or CSS / Javascript based reporting solutions, you could even use my canvas example, though if you look around the web, technology has moved on since then and you can do something really snazzy with modern techniques.