Expanded Site Report by Porting SQL Query to a Stored Procedure RRS feed

  • Question

  • Hey there.

    I have a SQL query I run about once a week that produces an aggregate report of all sites on my Sharepoint farm.
    It includes multiple content databases and also does some joining from data help in the config database.

    Weekly, I have been running the SQL query and pasting the output into a nice Sharepoint list with a few different custom views set up.

    Our help desk references this list when users call up for Sharepoint support to verify owners of a site collection and we also this with a filtered view to do pruning of stale sites.

    I want to automate the display of the SQL query so it no longer has to be updated by hand.

    I am thinking to save my query into a stored procedure on the SQL instance and then use a data viewer web part to run and display the results of the Stored Procedure.

    Any comments or suggestions?

    Below is a small sample of my query if anyone finds this useful. I have cut off my query at the first content database, but any additional content databases can also be reported on by using the UNION command and adding/updating a copy of the first query directly below it.

    select distinct a.fullurl as [SiteUrl], b.tp_login as [SiteAdmin], b.tp_title as [Display Name],
    sum(cast(((cast(c.size as decimal(18, 2)))/1024/1024) as decimal(18, 2))) as [RecycleBin],
    cast((cast(d.bwused as decimal(18, 2))/1024/1024) as decimal(18, 2)) as [BandwidthUsed],
    cast((cast(d.diskused as decimal(18, 2))/1024/1024) as decimal(18, 2)) as [SiteSize],
    cast((cast(d.diskquota as decimal(18, 2))/1024/1024) as decimal(18, 2)) as [SiteQuota],
    d.timecreated as [Created On], as [SiteID],
    f.Name as [Content DB],
    (select @@servername) as [SQLServerName],
    d.lastcontentchange as [LastContentChange],
    (select datediff(day,d.lastcontentchange,current_timestamp)) as [DaysSinceLastChange]
    from wss_db1..webs as a inner join
    wss_db1..sites as d on inner join
    wss_db1..userinfo as b on a.siteid=b.tp_siteid left join
    wss_db1..recyclebin as c on a.siteid=c.siteid
    join WSS_Config..SiteMap e on rtrim(e.path) = '/' +
    join WSS_config..Objects f on f.Id = e.DatabaseId
    where b.tp_siteadmin = '1' and a.parentwebid is null
    group by a.fullurl, b.tp_login, b.tp_title, d.diskused,, d.bwused, d.diskquota, d.timecreated, d.lastcontentchange,

    Friday, November 14, 2008 3:29 PM


  • I've used Data View Web Parts in the past to show data grabbed from SQL Server tables or the results of SQL Server jobs etc.

    I even used them *in connection* with the Help Desk software that was SQL Server based.

     I was able to show things like the latest 10 questions sent to the Help Desk; the Break-down in question types; the latest 10 questions per product etc. etc.

    The combination of Data View Web Parts and web part connections to for instance Lists with all the Product Names extracted from a different table in the Help Desk database proved to be very efficient.

    So I don't see any problems with what you intend to do provided that doing it too often (running the procedure) isn't going to to slow the db system down too much.
    Friday, November 14, 2008 5:49 PM