SharePoint (and Project Server) Shenanigans

In SharePoint 2010 the site collection Web Analytics  reports could be viewed by end users or business owners via the ‘View Web Analytics’  permission.

With SharePoint 2013 and usage reporting move into the Search Service Application this is much more difficult unless you want to grant business owners full control on your site.

Recently we had to try and grant business owners the ability to read the usage reports without being sites owners, as this was impossible via permissions we had to look for another method.

After some searching I came across this excellent link from Mike Smith that shows how to save the central search reports back the SharePoint Server.

http://techtrainingnotes.blogspot.co.uk/2015/04/run-sharepoint-2013-search-reports-from.html

I updated this to run and save all the site collection usage reports then upload the reports to a library in SharePoint, finally I set a daily task to run the report to create self service solution for end users.

Add-PSSnapin “Microsoft.SharePoint.PowerShell" -ea Continue

# Set the intranet portsl location
$WebURL = “http://intranet.corp/”

#This is the URL for site collection usage reports
$url = "http://intranet.corp/_layouts/15/Reporting.aspx?Category=AnalyticsSiteCollection"

# This is the path to write the reports to must exist on server running the script
$path = "D:\SearchReports\"

function Get-SPSearchReports ($url, $searchreport, $path)
{
  # function to run the usage reports and store locally on server
  # Usage reports ID’s must match the environment, use View source on the page _layouts/15/Reporting.aspx?Category=AnalyticsSiteCollection to get the ID’s

  # Usage Reports for Prod
  
   $Usage                   = "6bbf6e1c-d79a-45da-9ba0-d0c3332bf6e2"
   $Number_of_Queries         = "df46e7fb-8ab0-4ce8-8851-6868a7d986ab"
 
  # Search Reports forProd

  $Top_Queries_by_Day         = "06dbb459-b6ef-46d1-9bfc-deae4b2bda2d"
  $Top_Queries_by_Month       = "8cf96ee8-c905-4301-bdc4-8fdcb557a3d3"
  $Abandoned_Queries_by_Day   = "5dd1c2fb-6048-440c-a60f-53b292e26cac"
  $Abandoned_Queries_by_Month = "73bd0b5a-08d9-4cd8-ad5b-eb49754a8949"
  $No_Result_Queries_by_Day   = "6bfd13f3-048f-474f-a155-d799848be4f1"
  $No_Result_Queries_by_Month = "6ae835fa-3c64-40a7-9e90-4f24453f2dfe"
  $Query_Rule_Usage_by_Day    = "8b28f21c-4bdb-44b3-adbe-01fdbe96e901"
  $Query_Rule_Usage_by_Month  = "95ac3aea-0564-4a7e-a0fc-f8fdfab333f6"

  # set the file path and name
  $filename = $path + (Get-Variable $searchreport).Name + ".xlsx"
  $reportid = (Get-Variable $searchreport).Value

  $TTNcontent = "&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId%3D" + $reportid

  # setup the WebRequest
  $webRequest = [System.Net.WebRequest]::Create($url)
  $webRequest.UseDefaultCredentials = $true
  $webRequest.Accept = "image/jpeg, application/x-ms-application, image/gif, application/xaml+xml, image/pjpeg, application/x-ms-xbap, */*"
  $webRequest.ContentType = "application/x-www-form-urlencoded"
  $webRequest.Method = "POST"

  $encodedContent = [System.Text.Encoding]::UTF8.GetBytes($TTNcontent)
    $webRequest.ContentLength = $encodedContent.length
    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($encodedContent, 0, $encodedContent.length)
    $requestStream.Close()

  # get the data
  [System.Net.WebResponse] $resp = $webRequest.GetResponse();
    $rs = $resp.GetResponseStream();
    #[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs;
    #[byte[]]$results = $sr.ReadToEnd();
    [System.IO.BinaryReader] $sr = New-Object System.IO.BinaryReader -argumentList $rs;
    [byte[]]$results = $sr.ReadBytes(10000000);

  # write the file
  Set-Content $filename $results -enc byte

}

Function Upload-Report($URL, $DocLibName, $FilePath)
{
# function to upload the locally stored reports to SharePoint
# Get a variable that points to the folder
$Web = Get-SPWeb $URL
$List = $Web.GetFolder($DocLibName)
$Files = $List.Files

# Get just the name of the file from the whole path
$FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1)

# Load the file into a variable
$File= Get-ChildItem $FilePath

# Upload it to SharePoint
$Files.Add($DocLibName +"/" + $FileName,$File.OpenRead(),$true)
$web.Dispose()
}

# delete anything in the d:\SearchReports folder
Remove-item D:\SearchReports\*

# run the reports

Get-SPSearchReports $url "Usage" $path
Get-SPSearchReports $url "Number_of_Queries" $path
Get-SPSearchReports $url "Top_Queries_by_Day" $path
Get-SPSearchReports $url "Top_Queries_by_Month" $path
Get-SPSearchReports $url "Abandoned_Queries_by_Day" $path
Get-SPSearchReports $url "Abandoned_Queries_by_Month" $path
Get-SPSearchReports $url "No_Result_Queries_by_Day" $path
Get-SPSearchReports $url "No_Result_Queries_by_Month" $path
Get-SPSearchReports $url "Query_Rule_Usage_by_Day" $path
Get-SPSearchReports $url "Query_Rule_Usage_by_Month" $path

# upload the reports

Upload-Report $WebURL "Audit Reports\Site Collection Usage" “D:\SearchReports\Usage.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Number_of_Queries.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Abandoned_Queries_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Abandoned_Queries_by_Month.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\No_Result_Queries_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\No_Result_Queries_by_Month.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Query_Rule_Usage_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Query_Rule_Usage_by_Month.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Top_Queries_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Top_Queries_by_Month.xlsx”

A recent project I worked on involved a SharePoint 2010 to 2013 migration, part of this migration was to migrate an SSRS 2012 install from SP2010 to SP2013, the steps are well documented in MSDN http://msdn.microsoft.com/en-gb/library/hh759331.aspx

As part of the migration process we moved from classic to claims authentication, this is a straight forward 2 step process

1 ) Create a web app in classic mode in the 2013 farm and mount the content database from SP2010.

2) Call the Convert-SPWebApplication cmdlet to migrate the user accounts to claims authentication.

To perform the SSRS migration we installed SSRS 2012 SP1, moved across the reporting database and Encryption key, when testing the reports a few worked but lots were showing the error “User Cannot be Found” error as outlined in this blog post.

http://chanakyajayabalan.wordpress.com/2011/06/17/ssrs-report-server-has-encountered-a-sharepoint-error-user-cannot-be-found/

The post above suggests that a user has been deleted from the content db, we checked this but no accounts were marked as deleted.

The next step was to run a SQL trace while opening the reports and see what is being called.

The site ID was DDDC5D75-A52C-4FD9-8DE9-405246F54C4A, in the trace file I found the following

proc_SecGetPrincipalById @SiteId=’DDDC5D75-A52C-4FD9-8DE9-405246F54C4A’,@PrincipalId=9,@GetSTSToken=0,@GetExternalToken=0,@RequestGuid=’0873A39C-9913-7078-5AE6-AF4064C5D2B6′

proc_SecGetPrincipalById @SiteId=’DDDC5D75-A52C-4FD9-8DE9-405246F54C4A’,@PrincipalId=10,@GetSTSToken=0,@GetExternalToken=0,@RequestGuid=’0873A39C-9913-7078-5AE6-AF4064C5D2B6′

Checking back in the content db UserInfo table both ID’s were for valid users but Id 9 was for a migrated user (an account that starts i:0#.w|domain\username) however Id 10 was a non-migrated user account (an account in the format domain\username) .

Checking the account for Id 10 there was no matching entry in AD, I can only assume this user account had been previously deleted from AD but not removed from SharePoint, hence the “User cannot be found” error.

Some more searching turned up this MS support article http://support.microsoft.com/kb/2781008 describing the same issue but different circumstances, the fix is rolled into SQL Server 2012 SP1 CU2, installing this patch fixed the issue for us and we could open all the reports again.

While it is always good to work with the latest and greatest versions of everything, there are still solutions running on 2010 that need maintenance.

So recently I found myself having to update a SharePoint 2010 solution that was made up of an SPD2010 workflow, a content type and library template. I had to update the content type, workflow and library template with some new and updated columns.

The library template was using a custom Edit form with some of the  content type fields hidden, so I had to update the Edit form to show some new fields. To add a new field to a form you would normally edit the form table to and new rows where needed then select the field from the data source and select as ‘Insert as List Form Field’, unfortunately this option was no available; updating an edited form seems to be a common problem with SPD2010, as the same issue is outlined here.

http://social.msdn.microsoft.com/Forums/sharepoint/en-US/ac032a6b-5195-4ecf-a0ab-6e0117fd1a77/insert-as-list-form-field-not-available-on-data-source-details-panel-when-editing-editformaspx?forum=sharepointcustomizationprevious

My solution was as follows:

Select a field in the form where I wanted the new field and insert a new row into the form table, copy an existing control and insert into the new row, using the Common FormField Tasks control select the new control from the Data Field picker

Now Pick ‘List Form Field’ from the Format as  picker

In the Quick Tag Selector bar the new field will be called <SharePoint:FormField#FormField1>

From the Quick Control select Edit Tag…

The Tag code is now displayed with the ID value of  “FormField1”

Now check the other fields and find the control with the highest ID value and set the new tag value the be higher in the format “ff<id>{$Pos}”, ensure both references to the ID value are set

If the ID numbering is incorrect a duplicate

Once completed the new control will be available on the form.