querying membership data

Found one very usefull query from internet :

http://johnlivingstontech.blogspot.com/2009/11/sharepoint-2007-five-very-helpful-yet.html

thanks for the creator to share this!!  Below is the SQL query

1. Select Groups By URL

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrl]

@url NVARCHAR(255)

AS

BEGIN

SELECT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle", Webs.Title AS WebTitle, Webs.FullURL AS WebURL, Roles.Title AS "RoleTitle"

FROM RoleAssignment WITH (NOLOCK)

INNER JOIN Roles WITH (NOLOCK) ON Roles.SiteId = RoleAssignment.SiteId

AND Roles.RoleId = RoleAssignment.RoleId

INNER JOIN Groups WITH (NOLOCK) ON Groups.SiteId = RoleAssignment.SiteId

AND Groups.ID = RoleAssignment.PrincipalId

INNER JOIN Sites WITH (NOLOCK) ON Sites.Id = RoleAssignment.SiteId

INNER JOIN Perms WITH (NOLOCK) ON Perms.SiteId = RoleAssignment.SiteId

AND Perms.ScopeId = RoleAssignment.ScopeId

INNER JOIN Webs WITH (NOLOCK) ON Webs.Id = Perms.WebId

WHERE ((Webs.FullUrl = @url) OR (@url IS NULL))

ORDER BY

Groups.Title, Webs.Title, Perms.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrl 'Sites/HR/Pay'

Results

GroupID	GroupTitle	WebTitle	WebURL	RoleTitle
71	Manager	Accounting	Sites/HR/PAY	View Only
74	Acct Sr Manager	Accounting	Sites/HR/PAY	Full Control
75	Member	Accounting	Sites/HR/PAY	View Only
76	Viewer	Accounting	Sites/HR/PAY	View Only

 

 

 

2. Select Groups By URL and User

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrlUser]

@login NVARCHAR(255),

@url NVARCHAR(255)

AS

BEGIN

SELECT G.ID AS "GroupID", G.Title AS "GroupTitle", W.Title AS WebTitle, W.FullURL AS WebURL, R.Title AS "RoleTitle"

FROM RoleAssignment AS RA WITH (NOLOCK)

INNER JOIN Roles AS R WITH (NOLOCK) ON R.SiteId = RA.SiteId

AND R.RoleId = RA.RoleId

INNER JOIN Groups AS G WITH (NOLOCK) ON G.SiteId = RA.SiteId

AND G.ID = RA.PrincipalId

INNER JOIN Sites AS S WITH (NOLOCK) ON S.Id = RA.SiteId

INNER JOIN Perms AS P WITH (NOLOCK) ON P.SiteId = RA.SiteId

AND P.ScopeId = RA.ScopeId

INNER JOIN Webs AS W WITH (NOLOCK) ON W.Id = P.WebId

WHERE ((W.FullUrl = @url) OR (@url IS NULL))

AND G.ID IN

(

SELECT Groups.ID FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

)

ORDER BY

G.Title, W.Title, P.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrlUser 'acmedomain\jdoe', 'Sites/HR/Pay'

Results

GroupID	GroupTitle	WebTitle	WebURL	RoleTitle
74	Sr Manager	Accounting	Sites/HR/PAY	Full Control

 

 

 

3. Select Groups By User Login

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUserLogin]

@login NVARCHAR(255)

AS

BEGIN

SELECT DISTINCT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle"

FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo WITH (NOLOCK) ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

ORDER BY Groups.Title

END

Sample Query

EXEC usp_SelectGroupsByUserLogin 'acmedomain\jdoe'

Results

GroupID	GroupTitle
74	Sr Manager
52	Corporate Viewer
14	HR Team Member

4. Select Users By Group

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroup]

@groupTitle NVARCHAR(255)

AS

BEGIN

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, dbo.Groups.ID AS GroupsID, dbo.Groups.Title AS GroupsTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

WHERE ((dbo.Groups.Title = @groupTitle) OR (@groupTitle IS NULL))

ORDER by dbo.UserInfo.tp_Login

END

Sample Query

EXEC usp_SelectUsersByGroup 'HR Team Member'

Results

UserID	UserTitle	UserLogin	UserEmail	GroupsID	GroupsTitle
23	Doe, John	acmedomain\jdoe	jdoe@acmedomain.com	14	HR Team Member
20	Doe, Jane	acmedomain\jadoe	jadoe@acmedomain.com	14	HR Team Member
2	Smith, Bob	acmedomain\bsmith	bsmith@acmedomain.com	14	HR Team Member
24	Yamamoto, Kazue	acmedomain\kyamamoto	kyamamoto@acmedomain.com	14	HR Team Member
46	Nakamura, Ichiro	acmedomain\inakamura	inakamura@acmedomain.com	14	HR Team Member

5 Select Users By Groups

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroups]

@groupTitles VARCHAR(1000)

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @groupTitles

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, Groups.Title AS GroupTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

JOIN OPENXML(@idoc, '/Root/Group', 0) WITH (Name VARCHAR(100)) AS g

ON dbo.Groups.Title = g.Name

ORDER by dbo.Groups.Title, dbo.UserInfo.tp_Title

END

Sample Query

EXEC usp_SelectUsersByGroups '<Root><Group Name="HR Team Member"></Group><Group Name="Acct Sr Managers"></Group></Root>'

Results

UserID	UserTitle	UserLogin	UserEmail	GroupsID	GroupsTitle
23	Doe, John	acmedomain\jdoe	jdoe@acmedomain.com	14	HR Team Member
20	Doe, Jane	acmedomain\jadoe	jadoe@acmedomain.com	14	HR Team Member
2	Smith, Bob	acmedomain\bsmith	bsmith@acmedomain.com	14	HR Team Member
24	Yamamoto, Kazue	acmedomain\kyamamoto	kyamamoto@acmedomain.com	14	HR Team Member
46	Nakamura, Ichiro	acmedomain\inakamura	inakamura@acmedomain.com	14	HR Team Member
7	Horowitz, Rick	acmedomain\rhorowitz	rhorowitz@acmedomain.com	15	Acct Sr Managers
3	Davis, Jiro	acmedomain\jdavis	jdavis@acmedomain.com	15	Acct Sr Managers

-- Query to get all the SharePoint groups in a site collection

 

Another cool query from sharepointkings

 

Customer request the report on all the user in the group on the specify sites collection.

 

-- Query to get all the SharePoint groups in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,
dbo.Groups.Title AS Expr2, dbo.Groups.Description
FROM dbo.Groups INNER JOIN
dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

 

-- Query to get all the members of the SharePoint Groups
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership INNER JOIN
dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

 

 

after query the all the memeber of the group, use excel to remove the duplicate data then you will know how many user in the group :)

The faster way get the enumsites report in excel

Just notice have the easy way for you to get the nice report with the stsadm enumsites command. you can see the top level sites , owner, content db report between one minute!!! cool !!

stsadm -o enumsites -url http://abc.com > sitelist.xml
Then just opened up the xml file with excel2007. (really beginning to like the features in this version)

It warned that there was not an associated schema, would I like to create on from the file, ‘yes Please’!

And now we can sort, filter, sum, etc.

SQL query to find "DayLastAccessed" in portal's Site DB

is time to do clean up for the SharePoint environment , have 2 option i know we can do that. one is manual one is auto :P ha ha ha

 

Auto one you can setup at CA, Site use confirmation and deletion  /_admin/applications.aspx . not sure how correct is it!! ha ha

 

but i prefer manual one.so use below query to check day last accessed , so we can guess is still used or not.

 

SELECT     FullUrl AS 'Site URL', TimeCreated, DATEADD(d,
DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101)) AS
lastAccessDate
FROM         Webs
WHERE     (DayLastAccessed <> 0) AND (FullUrl LIKE N'sites/%')
ORDER BY lastAccessDate

Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

 

keep track on what i can get from internet about SQL query for SharePoint :

 

i am looking for last access to sites query, but unable to find..but found some other interesting one.. at below from this link

 

 

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')

  
  

Overview of usage reports

The following usage reports are available:

• 
  Site usage report Site administrators, including administrators of the Shared Services Administration site, can view usage reporting for their site by clicking Site usage reports in theSite Administration section of the Site Settings page.
  
• 
  Site collection usage report Site collection administrators for most site collections can view usage reporting by clicking Site collection usage reports in the Site Collection Administrationsection of the Site Settings page.
  
• 
  Site collection usage summary Site collection administrators for the Shared Services Administration site and individuals with personal sites can view the Site Collection Usage Summary page by clicking Usage summary in the Site Collection Administration section of the Site Settings page.
  

[Below information from Kevin-lam]
Problem description:
=================
The data in SpUsageWeb.aspx don’t match the data in the UsageDetails.aspx page.

Plan:
==================
The issue is fixed in the April’s Service Pack, which is going to be released. We are now testing the Service Pack and will update you once we finish the test.
If no exception, I will send you the Service Pack early next week. Then you can try and see if the issue can be fixed by the Service Pack.

Additional information:
==================
The _layouts/usagedetails.aspx page is still available in SharePoint 2007, although there is no link to it in the site settings page.
If the Site Usage Report works properly, the data in the usagedetails.aspx should match the data in the SpUsageWeb.aspx.