Guilherme Gouveia

Resumo

Como todos vocês sabem a plataforma SharePoint possui vários cenários e a cada dia temos mais contato com essa tecnologia. Dentre as necessidades que hoje existe, uma delas é analisar um portal em sua capacidade de armazenamento, estatísticas de sites,  bibliotecas de documentos etc.

Com isto podemos acompanhar o crescimento da solução do SharePoint e podemos tomar uma ação preventiva evitando erros. Existem várias formas de se fazer esta análise e iremos colocar uma delas, focando SharePoint 2007.

Mediante a esta necessidade e sabedor que para contemplarmos as melhores práticas da Microsoft® não devemos acessar o banco de dados do SharePoint para edição, inserção e deleção direta de dados, entretanto achamos pertinente mencionar que para analisarmos o SharePoint, podemos utilizar operações de consulta, com isto segue um detalhemento que poderá servir de guia para Adms SharePoint.

Sumário

• Uma visão geral do esquema de banco de dados do SharePoint
• Uma lista de consultas SQL

Uma visão geral do esquema de banco de dados do SharePoint

Para escrever consultas no esquema de banco de dados do SharePoint, você deve entender como a Microsoft organiza a informação em tabelas SQL. Algumas tabelas comuns são:

• Docs 
• Docversions
• Sites 
• Webs

As informações relativas às bibliotecas de documentos, lista, área e sites podem ser facilmente obtidas juntando estas tabelas no banco de dados.

Lista de consultas SQL

• Top 100 documentos em termos de tamanho (versão mais recente (s) apenas):

SELECT TOP 100 Webs.FullUrl As SiteUrl,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 documentos mais versionado:

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

•  Lista de WSS sites de nível superior e seu tamanho total, incluindo sites filho no portal:

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'

•  Lista de área de portal e número total de usuários:

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

• Lista de nível superior e páginas do portal e do número de usuários:

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

• Lista de todas as áreas do portal:

select Webs.FullUrl As [Site Url],

Title AS [Area Title]

from Webs

Where fullurl NOT like '%sites%' AND fullUrl <>

•  Lista de todos os sites de nível superior e páginas no portal:

select Webs.FullUrl As [Site Url], 

Title AS [WSS Site Title]

from webs

where fullurl like '%sites%' AND fullUrl <>

• Lista de todas as lista / bibliotecas de documentos e itens no total:

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

• 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

• Lista de bibliotecas de documentos e itens no total:

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

• Lista de lista de anúncios e outros itens no total:

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

• Lista de lista de contatos e itens no total:

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

• Lista de lista de eventos e itens no total:

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

Lista de todas as tarefas e itens no total:

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 descList of all InfoPath form library and total items:

• Lista de todas biblioteca de formulários do InfoPath e itens no total:

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 descList of generic list and total items:

• Lista de genéricos e lista de itens no total:

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 descTotal number of documents:

• Número total de documentos:

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:

• Total de documentos de MS Word:

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 de documentos do MS Excel:

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 de documentos de MS PowerPoint:

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 de documentos TXT:

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 de arquivos Zip:

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 de arquivos JPG:

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 de arquivos PDF:

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 de arquivos GIF:

• 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 de arquivos que não DOC, PDF, XLS, PPT, TXT, ZIP, ASPX, dewp, STP, CSS, JPG, GIF:

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

• O tamanho total de todos os documentos:

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

Conclusão

Foi disponibilizado uma série de instruções que facilitam análises  de Portais

SharePoint, logo somando conhecimento técnico bem como rotinas aliadas a

boas  práticas conseguimos prever problemas antes mesmo que eles venham a acontecer,

caminha nesta idéia de troca de conhecimentos que continuaremos com posts sobre novidades e vivências técnicas. Até mais pessoal!!!