In this post, I will talk about my SharePoint Performance Dashboard Project. It has mainly two major types of reporting features, Part 1.  CPU Monitor, Memory Monitor, Top Users of the farm, Top Site Collections in the farm and many more and Part 2. List of content database with the size - for each web application, per content database how many site collections are there and what is the quota of each site collection and how much is used out of it.

So this project was planned in two parts, 1. Server Performance Reporting 2. Farm Content Size Reporting

Considering the length of the information,  I will divide each part into two posts, so lets start with Part 1. Server Performance Reporting

For this functionality, We wanted mainly five types of reporting

  1. CPU trend in SharePoint servers in the farm for last 10 days
  2. Memory trend in SharePoint servers in the farm for last 10 days
  3. Top users of the farm
  4. Top Site Collections of the farm
  5. Tools used for the access

To begin with, I did my initial research in SharePoint’s Usage and Health data collection functionality. I studied how this functionality is and what type of information it collects. For more information about this functionality, please refer to Configure usage and health data collection (SharePoint Server 2010)

In WSS_Logging database, we have various views available and it really contains very useful information, it’s the data source for our reporting in this part.

image

Also, I evaluated SharePoint Diagnostic Studio 2010 (SPDiag 3.0) (SharePoint Server 2010). Based on it and by checking the various views from WSS_logging database I could derive all the necessary SQL queries that are required to get various performance information about each server in the farm.

CPU Monitor :

image

Query : 

SELECT     
    MachineName, LogTime, Value
FROM         
    PerformanceCounters
WHERE    
(counterid in 
    (select id from PerformanceCountersDefinitions 
    where 
    category = 'Processor'
    and Machine in ('sharepoint') –- Add your server names of the SharePoint farm here
    )
) 
and (LogTime between GETDATE() - 10 and GETDATE())
order by Machinename,logtime

Memory Monitor

image

Query : 

SELECT 
    LogTime,    [MachineName], [CounterId]
    ,(convert(decimal(10,1),((14336-[Value]) / 1024)) / 14) * 100 'Value'
FROM 
    [PerformanceCounters]
where 
CounterId in (    select id from PerformanceCountersDefinitions 
        where 
        category = 'Memory'
        and 
        Machine in ('sharepoint') –- Add your server names of the SharePoint farm here 
) 
and (LogTime between GETDATE() - 10 and GETDATE())
order by [MachineName],[LogTime]

Top Users of the farm

image

Query : 

SELECT     
         TOP (10) [Username], SUM(RequestCount) AS 'RequestCount'
FROM         
         TopUserRequestCount
WHERE     
         (LogTime BETWEEN GETDATE() - 10 AND GETDATE()) 
GROUP BY UserName
ORDER BY 'RequestCount' DESC

 

Top URL/Site Collection Requests

image

Queries : 

SELECT  top 10   
    SUM(duration) AS requests, 
    SiteUrl AS Url
FROM         
    RequestUsage
where 
    (LogTime between GETDATE() - 10 and GETDATE())
    and SiteUrl not like '%_vti_bin%'
    and SiteUrl not like '%.axd%'
    and SiteUrl not like '%.asmx%'
    and SiteUrl not like '%office_viewing_service_cache%'
    and SiteUrl not like '%_layouts%'
GROUP BY 
    SiteUrl
order by 
    requests

SELECT top 10
    Url,
    count([Count]) Visitcount
FROM 
    [UrlVisit]
where 
    (LogTime between (GETDATE() - 10) and GETDATE())    
    and Url not like '%_vti_bin%'
    and Url not like '%.axd%'
    and Url not like '%.asmx%'
    and Url not like '%office_viewing_service_cache%'
    and Url not like '%_layouts%'
    
group by 
    Url
order by 
    Visitcount desc
Information about the other reporting and how I did the UI part & packaging, I have covered in my next post.