IT knowledge sharing - SharePoint (MOSS 2007,2010,2013,2016 and 2019 + office 365 )

Tuesday, August 10, 2010

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.

Wednesday, August 4, 2010

Great SharePoint Tutorials site to share

here have some freebies to share from internet, if you like to have some training tutorials for your SharePoint user here is have good one.

Video Tutorials > easy to learn :D enjoy ya

http://www.fpweb.net/sharepoint-tutorials/

Templates & Navigation

Create a SharePoint Group

Delete a Group

Create a Site Template

Create a List Template

Change Title Description & Logo

Change Quicklist Order

Add Quicklist Link

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

Sunday, August 1, 2010

Shorting the content DB from enumlist.txt

this morning try to get the URL and Database from enumlist for orphan object checking , but after import to excel with delimited but look like the information is not good enough. ha ha, then learnt some excel function to short it out. so want to keep track new excel command i learnt :

this command is trim up the front part

=RIGHT(A1,LEN(A1)-FIND("e=",A1))

Results is :

="MOSS_Content_DB1" StorageUsedMB="28.7" StorageWarningMB="80" StorageMaxMB="100" />

Another column is trim out the left over

=LEFT(B2,(FIND("St",B2)))

Results is:

="MOSS_Content_DB1" S

At last replace the “ and “ S with space, then you will get the DB name :)

Maybe look stupid , but i get what i want!! i think will have more easy way , but i unable to think out yet. so i just use the faster skill i know to get my information i think this is fair.

please do share with me the easy way , if you have :)

IT knowledge sharing - SharePoint (MOSS 2007,2010,2013,2016 and 2019 + office 365 )

which country user step here?

Tag Cloud

Tuesday, August 10, 2010

The faster way get the enumsites report in excel

Wednesday, August 4, 2010

Great SharePoint Tutorials site to share

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

Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

A List of SQL Queries

Sunday, August 1, 2010

Shorting the content DB from enumlist.txt