Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

Summary

It is a very common requirement in SharePoint Portal Solution to analyze a SharePoint solution in terms of storage capacity and statistics of sites, document libraries, area etc., to monitor the growth of the SharePointsolution and to take prompt action, if any thing goes wrong. Like, SharePoint should know what document library is most used in terms of versions, or the number of documents uploaded.

Microsoft SharePoint Portal Solution database is Microsoft�s proprietary database, and it cannot be modified because of any problems with future service pack releases, but sometimes you can write SELECT queries that can give full information that may be useful for the SharePoint Administrator. I was looking for some queriesthat would help me to analyze a SharePoint solution, but unfortunately, didn�t find any article that gives a list of useful queries, so I started to write my own SQL queries.

An Overview of the SharePoint Database Schema
A List of SQL Queries

An Overview of the SharePoint Database Schema

In order to write queries against the SharePoint database schema, you should how Microsoft organizes information in SQL tables. Some common tables are:

Docs
Docversions
Sites
Webs

Information pertaining to document libraries, list, area, and sites can easily be fetched by joining these tables in the Portal_Site database. You can write a query against the PORTAL_Profile database to get useful information of user profiles etc.

A List of SQL Queries

Top 100 documents in terms of size (latest version(s) only):

Collapse

SELECT TOP 100 Webs.FullUrl As SiteUrl, 
Webs.Title 'Document/List Library Title', 
DirName + '/' + LeafName AS 'Document Name',
CAST((CAST(CAST(Size as decimal(10,2))/1024 As 
      decimal(10,2))/1024) AS Decimal(10,2)) AS 'Size in MB'
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')  
               AND (LeafName NOT LIKE '%.aspx') 
               AND (LeafName NOT LIKE '%.xfp') 
               AND (LeafName NOT LIKE '%.dwp') 
               AND (LeafName NOT LIKE '%template%') 
               AND (LeafName NOT LIKE '%.inf') 
               AND (LeafName NOT LIKE '%.css') 
ORDER BY 'Size in MB' DESC

Top 100 most versioned documents:

Collapse

SELECT TOP 100
Webs.FullUrl As SiteUrl, 
Webs.Title 'Document/List Library Title', 
DirName + '/' + LeafName AS 'Document Name',
COUNT(Docversions.version)AS 'Total Version',
SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2)) )  AS  'Total Document Size (MB)',
CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2))   AS  'Avg Document Size (MB)'
FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id 
   INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 
AND (LeafName NOT LIKE '%.stp')  
AND (LeafName NOT LIKE '%.aspx')  
AND (LeafName NOT LIKE '%.xfp') 
AND (LeafName NOT LIKE '%.dwp') 
AND (LeafName NOT LIKE '%template%') 
AND (LeafName NOT LIKE '%.inf') 
AND (LeafName NOT LIKE '%.css') 
GROUP BY Webs.FullUrl, Webs.Title, DirName + '/' + LeafName
ORDER BY 'Total Version' desc, 'Total Document Size (MB)' desc

List of unhosted pages in the SharePoint solution:

Collapse

select Webs.FullUrl As SiteUrl, 
case when [dirname] = '' 
then '/'+[leafname] 
else '/'+[dirname]+'/'+[leafname] 
end as [Page Url], 
CAST((CAST(CAST(Size as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2))   AS  'File Size in MB'
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
where [type]=0 
and [leafname] like ('%.aspx') 
and [dirname] not like ('%_catalogs/%') 
and [dirname] not like ('%/Forms') 
and [content] is not null 
and [dirname] not like ('%Lists/%') 
and [setuppath] is not null 
order by [Page Url];

List of top level WSS sites and their total size, including child sites in the portal:

Collapse

select FullUrl As SiteUrl,  
CAST((CAST(CAST(DiskUsed as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2))   AS  'Total Size in MB'
from sites 
Where FullUrl LIKE '%sites%' AND 
   fullUrl <> 'MySite' AND fullUrl <> 'personal'

List of portal area and total number of users:

Collapse

select webs.FullUrl, Webs.Title, 
    COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers 
ON Webs.Id = WebMembers.WebId
Where fullurl NOT like '%sites%' AND 
    fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl,  Webs.Title
Order By  'Total User' desc

List of top level and sub sites in the portal and the number of users:

Collapse

select  webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers 
ON Webs.Id = WebMembers.WebId
where fullurl  like '%sites%' AND fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl,  Webs.Title
Order By  'Total User' desc

List of all portal area:

Collapse

select Webs.FullUrl As [Site Url], 
Title AS [Area Title]
from Webs 
Where fullurl NOT like '%sites%' AND fullUrl <> 
    'MySite' AND fullUrl <> 'personal'

List of the total portal area:

Collapse

select COUNT(*)from Webs 
Where fullurl NOT like '%sites%' AND 
    fullUrl <> 'MySite' AND fullUrl <> 'personal'

List of all top level and sub sites in the portal:

Collapse

select Webs.FullUrl As [Site Url], 
Title AS [WSS Site Title]
from webs
where fullurl  like '%sites%' AND fullUrl <> 
     'MySite' AND fullUrl <> 'personal'

List of the total top level and sub sites in the portal:

Collapse

select COUNT(*) from webs
where fullurl  like '%sites%' AND fullUrl <> 
    'MySite'  AND fullUrl <> 'personal'

List of all list/document libraries and total items:

Collapse

select  
case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
case tp_servertemplate
when 104 then 'Announcement'
when 105 then 'Contacts'
When 108 then 'Discussion Boards'
when 101 then 'Docuemnt Library'
when 106 then 'Events'
when 100 then 'Generic List'
when 1100 then 'Issue List'
when 103 then 'Links List'
when 109 then 'Image Library'
when 115 then 'InfoPath Form Library'
when 102 then 'Survey'
when 107 then 'Task List'
else 'Other' end as Type,
tp_title 'Title', 
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate IN (104,105,108,101, 
    106,100,1100,103,109,115,102,107,120)
order by tp_itemcount desc

Note: the tp_servertemplate field can have the following values:

104 = Announcement
105 = Contacts List
108 = Discussion Boards
101 = Document Library
106 = Events
100 = Generic List
1100 = Issue List
103 = Links List
109 = Image Library
115 = InfoPath Form Library
102 = Survey List
107 = Task List

List of document libraries and total items:

Collapse

select  
case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 101
order by tp_itemcount desc

List of image libraries and total items:

Collapse

select case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 109  -- Image Library

order by tp_itemcount desc

List of announcement list and total items:

Collapse

select case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 104  -- Announcement List

order by tp_itemcount desc

List of contact list and total items:

Collapse

select case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 105   -- Contact List

order by tp_itemcount desc

List of event list and total items:

Collapse

select case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 106   -- Event List

order by tp_itemcount desc

List of all tasks and total items:

Collapse

select  
case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 107  -- Task List

order by tp_itemcount desc

List of all InfoPath form library and total items:

Collapse

select  
case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 115  -- Infopath Library

order by tp_itemcount desc

List of generic list and total items:

Collapse

select  
case when webs.fullurl = '' 
then 'Portal Site' 
else webs.fullurl
end as [Site Relative Url], 
webs.Title As [Site Title], 
lists.tp_title As Title,
tp_description As Description, 
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 100  -- Generic List

order by tp_itemcount desc

Total number of documents:

Collapse

SELECT COUNT(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')  
               AND (LeafName NOT LIKE '%.aspx') 
               AND (LeafName NOT LIKE '%.xfp') 
               AND (LeafName NOT LIKE '%.dwp') 
               AND (LeafName NOT LIKE '%template%') 
               AND (LeafName NOT LIKE '%.inf') 
               AND (LeafName NOT LIKE '%.css')

Total MS Word documents:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.doc') 
  AND (LeafName NOT LIKE '%template%')

Total MS Excel documents:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.xls') 
    AND  (LeafName NOT LIKE '%template%')

Total MS PowerPoint documents:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.ppt') 
    AND  (LeafName NOT LIKE '%template%')

Total TXT documents:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.txt')  
    AND  (LeafName NOT LIKE '%template%')

Total Zip files:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.zip') 
    AND  (LeafName NOT LIKE '%template%')

Total PDF files:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.pdf') 
    AND  (LeafName NOT LIKE '%template%')

Total JPG files:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.jpg') 
               AND  (LeafName NOT LIKE '%template%')

Total GIF files:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.gif') 
               AND  (LeafName NOT LIKE '%template%')

Total files other than DOC, PDF, XLS, PPT, TXT, Zip, ASPX, DEWP, STP, CSS, JPG, GIF:

Collapse

SELECT count(*)
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.pdf') 
AND  (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.doc')
AND (LeafName NOT LIKE '%.xls')
AND (LeafName NOT LIKE '%.ppt')
AND (LeafName NOT LIKE '%.txt')
AND (LeafName NOT LIKE '%.zip')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.css')
AND (LeafName NOT LIKE '%.jpg')  
AND (LeafName NOT LIKE '%.gif')  
AND (LeafName <>'_webpartpage.htm')

Total size of all documents:

Collapse

SELECT SUM(CAST((CAST(CAST(Size as decimal(10,2))/1024 
       As decimal(10,2))/1024) AS Decimal(10,2))) 
       AS  'Total Size in MB'
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp') 
               AND (LeafName NOT LIKE '%.aspx') 
               AND (LeafName NOT LIKE '%.xfp') 
               AND (LeafName NOT LIKE '%.dwp') 
               AND (LeafName NOT LIKE '%template%') 
               AND (LeafName NOT LIKE '%.inf') 
               AND (LeafName NOT LIKE '%.css') 
               AND (LeafName <>'_webpartpage.htm')

Conclusion

I have demonstrated how easy it to analyze a SharePoint Portal solution. In the second part of the article, I will try to write queries on Users, User Profiles, Subscriber Lists, Site Groups, Cross Site Groups etc.

Murali Kumar Pitchika

Sunday, November 21, 2010