Friday, 14 December 2012

Daily Service Requests Report SCSM 2012

Daily Service Requests report is something which is used by service delivery managers and there is no out of box reports in SCSM to process this. 

Below SQL query will give you the count of following Service Requests assigned to Support Engineer's:

·         Active
·         Completed
·         Closed

Run this against DWDataMart database as per Microsoft’s suggest no query should be run directly on a transactional database in this case it ServerManager Database.

 distinct userdim.DisplayName as AssignedToUser , count ( case ServiceRequest.status
when 'ServiceRequestStatusEnum.Active' THEN 'Active' end )  as Active,
 count ( case ServiceRequest.status
when 'ServiceRequestStatusEnum.Completed' THEN 'Completed' end )  as Completed,
Count ( case ServiceRequest.status
when 'ServiceRequestStatusEnum.Completed' THEN 'Closed' end )  as Closed
From    ServiceRequestDim ServiceRequest JOIN   
WorkItemDim workitem on ServiceRequest.EntityDimKey = workItem.EntityDimKey JOIN   WorkItemAssignedToUserFactvw assignedtouser
on workitem.WorkItemDimKey = assignedtouser.WorkItemDimKey JOIN   
UserDimvw userdim on assignedtouser.WorkItemAssignedToUser_UserDimKey = userdim.UserDimKey
 Where    assignedtouser.DeletedDate is null and ServiceRequest.createddate>= CONVERT(varchar(8), GETDATE()-1, 112)
 group by userdim.DisplayName


Assigned To

Support Engineer A

Support Engineer B

Support Engineer C

Support Engineer D

 Please note this query will only pull info for Service requests

Wednesday, 12 December 2012

Missing server side dependencies SharePoint 2010

There is this known issue where you see Missing Server Side dependencies in the Health Analyzer in a clean install environment of SharePoint 2010. There are some blogs where they have Powers hell to sort this out but I found the Feature Admin Clean Up Tool from codeplex easy to use.

It’s an executable file and when you run, you just have to select the Web App and click on Find Faulty Feature to remove it.

Repeat the process for the other Web App’s if you’ve:

Tuesday, 11 December 2012

Delete All versions from a Document Library in SharePoint 2010

The issue we had was the database size was growing at alarming rate which caused some serious concerns over SAN allocation.  The Size of Site was 140 GB and Document Library (Document Set) was 100 GB. We figured out based on a historical analysis of DB growth , there was an increase of  60 GB in 2-3 weeks of time for a sub site which was alarming.

This happened as versioning was enabled but no major versioning limit was set. That allows too many versions to be created in SharePoint based on the software boundaries. A bespoke application housing LOB Process and a bespoke timer solution somehow created too many versions as the limit was not set.

Run this Power Shell Command to change the number of version to be retained:

ForEach-Object {ForEach($_.list in $_.Lists){If($_.EnableVersioning -eq $True){if($_.Title -eq "Shared Documents"){Write-host "List:"$_.Title "- List ID"$_.Id;$_.MajorVersionLimit = 4;$_.Update();ForEach($_.item in

Replace the URL with your Site URL and Shared Documents with the Document Library  Name you want

Change MajorVersionLimit to the number you require in my case I changed it to 4.

When that’s done, Connect to the corresponding Database Server via SQL Server Management Studio

Shrink the database by re-organizing the indexes, refer to DB maintenance guide before performing this on technet.

This released around 110 GB and we now have the Site Collection Size as 30 GB.

The Storage Metrics was showing incorrect numbers as We had June 2011 , August 2011 CU resolves this issue but the file size on Database was correct

Run this in Power Shell to get the size of all Site Collections to verify the numbers

Get-SPSite | select url, @{label="Size in MB";Expression={$}} | Sort-Object -Descending -Property "Size in MB" | ConvertTo-Html -title "Site Collections sort by size" | Set-Content SiteCollection.html

Thanks to Todd and some other references this helped immensely, saved my day. 

Important part of governance is to define versioning , though we have the versioning defined on all other site collections, for this one it wasn't as the necessity was due to bespoke application, timer job , but this has been changed now after experiencing this issue. Now we have defined versioning on this site collection too.