which country user step here?

Tag Cloud

MOSS (47) SharePoint 2007 (37) SharePoint 2013 (24) SharePoint 2010 (22) MOSS admin (17) PowerShell (17) admin (17) developer (16) List (15) WSS (14) sql query (14) MOSS SP2 (13) end user (11) scripting (11) wss V3 (11) Moss issue (8) search (8) permission (7) sql (7) Service Pack (6) database (6) reportadmin (6) workflow (6) Excel (5) client object model (5) CU (4) Client Code (4) Command (4) Cumulative Updates (4) Patch (4) RBS (4) SharePoint designer (4) stsadm (4) ASP.NET (3) Content Database (3) Groove (3) Host Named Site Collections (HNSC) (3) IIS (3) Tutorial (3) alert (3) batch file (3) codeplex (3) error (3) incomming email (3) restore (3) upload (3) user porfile (3) Caching (2) Folder (2) Index (2) Internet (2) My Site Cleanup Job (2) My Sites (2) News (2) People Picker (2) Share Document (2) View (2) Web Development with ASP.NET (2) add user (2) authentication (2) coding (2) column (2) deploy solution (2) domain (2) download (2) enumsites (2) exam (2) export (2) issue (2) june CU (2) mySites (2) network (2) office 365 (2) orphan site (2) performance (2) profile (2) project server (2) query (2) server admin (2) theme (2) timer job (2) training (2) web master (2) web.config (2) wsp (2) 70-346 (1) 70-630 (1) AAM (1) Anonymous (1) Approval (1) Cerificate (1) Consultants (1) Content Deployment (1) Content Type (1) DOS (1) Document Library (1) Drive Sapce (1) Excel Services (1) Export to Excel (1) Feature (1) GAC (1) Get-SPContentDatabase (1) Get-WmiObject (1) HTML calculated column (1) ISA2006 (1) IT Knowledge (1) ITIL (1) Install (1) Link (1) MCTS (1) Macro (1) Migration (1) NLBS (1) Nintex (1) Office (1) Open with Explorer (1) ROIScan.vbs (1) Reporting Services (1) SPDisposeCheck.exe (1) SQL Instance name (1) SSRS (1) SharePoint admin (1) SharePoint farm (1) Shared Services Administration (1) Site Collection Owner (1) Site template (1) Steelhead (1) URLSCAN (1) VLOOKUP (1) WSS SP2 (1) XCOPY (1) admi (1) app (1) application pool (1) aspx (1) audit (1) availabilty (1) backup (1) binding (1) blob (1) branding sharepoint (1) cache (1) calendar (1) connection (1) copy file (1) counter (1) crawl (1) custom list (1) event (1) excel 2013 (1) facebook (1) filter (1) fun (1) group (1) iis log (1) import (1) import list (1) improment (1) interview (1) keberos (1) load balance (1) log in (1) metada (1) migrate (1) mossrap (1) onedrive for business (1) operation (1) process (1) publishing feature (1) resource (1) security (1) send email (1) size (1) sps2003 (1) sql201 (1) sql2012 (1) sub sites (1) system (1) table (1) task list (1) today date (1) vbs (1) video (1) web part (1) widget (1) windows 2008 (1) windows 2012 R2 (1) windows Azura (1) windows account (1) windows2012 (1) wmi (1)

Wednesday, August 4, 2010

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


2 comments:

Shuwi said...

WOW !! Thanks a bunch !! These queries helped me to find a document of 11 GB with 120000 versions in an instant !

I imported the queries into a powerpivot sheet and are now publishing reports to sharepoint admin :)

Thanks again !
Jeroen

nsp said...

welcome just sharing information , this good job should go to the creator :)