Automating SharePoint 2013 Usage Reports

May 30, 2015 Leave a comment

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”

Categories: Work

Going to discuss Office 365 Taxonomy and OneDrive for Business at SharePoint Saturday Paris

SPS ParisThis week I am off to Paris with my little family, and on Saturday I will enjoy spending my day with the French SharePoint community and presenting my views and case study of using OneDrive for Business in Office365. I will be presenting [en Français] which I have not done for years, so be sure to hear some weird “fran-glais” as the technical terms are all in english for me.

The title was actually “Shall we forget about taxonomy with Office 365?” which refers to the fact that OneDrive for Business allows users to store a crazy-full amount of files on the cloud and the question that stands: what to do with all this ? What about my team sites, department sites, organised libraries with metadata that I have been telling and training my users to maintain for years ?
Do I have to forget everything I learnt ?

See you on Saturday to see how some of my clients are managing this question!
À Samedi pour voir comment certain de mes clients gèrent cette problématique d’entreprise! 

via François on Sharepoint http://bit.ly/1J1QTFK

François Souyri
French native Sharepoint Consultant living in London. A crossway between a designer, developer and system architect. Prefers stretching the limit of out-of-the-box features rather than breaking them into code. When not working with Microsoft Sharepoint François is often found on Web2.0 News sites and related social networking tools.

This article has been cross posted from sharepointfrancois.wordpress.com/ (original article)

Categories: Work Tags: ,

#Office #Project And #Visio 2016 Preview Available

This is to spread the word, with great excitement, that Project 2016 preview version is now available for trial use.

If you do not have required Office 365 subscription to try Project or Visio 2016, you can download using below links and trial product keys:

Project

product key: N46MP-733KG-T8MR9-VV9JX-HCFG4

Visio

product key: W9WC2-JN9W2-H4CBV-24QR7-M4HB8

Few weeks back, at Ignite 2015, preview version of Office 2016 was announced. You can browse here to download Office 2016 desktop version for trial. Or if you have an Office 365 subscription, home or enterprise, you can find details here to download new version.

An important point to note regarding Office 2016 preview version, is that it cant be installed side by side to Office 2013 (not sure if its true for Office 2010 as well). So you have to uninstall Office 2013 prior to install Office 2016 preview.
Enjoy.

via All about Enterprise Project Management (EPM) http://bit.ly/1FuTwh4

Khurram Jamshed
The author of the blog has an extensive experience of working as an EPM Consultant. Currently he is located in Dubai, UAE and working for Microsoft partner organization as Project Server specialist. He has a thorough experience of providing Project Management technical/functional consultancy to all sort of organizations. He is a certified PMP, a Project Server MCITP, and also received a MS community contributor award 2011.

This article has been cross posted from khurramjamshed.blogspot.com/ (original article)

#Microsoft #Ignite 2015 Wrap up #ProjectServer #SharePoint 2016

So the week of MS Ignite is over, and there is whole lot of information to absorb.

I had been closely following to all the happenings and announcements, and in this post i will summarize my take away of information related to SharePoint and Project Server.

So Microsoft has confirmed, that SharePoint Server 2016 (and Project Server) will become generally available in second quarter of 2016, with a public beta planned for last quarter of 2015. 

Microsoft has also confirmed that they will continue to release on-premises version of SharePoint/Project Server, however considering MS’s Cloud first strategy, every new feature will be released first Project Online and later to On-Premises version through a update/CU/SP.

Project Online/ Project Server 2013:

Following were the announcements in Ignite related to Project Server 2016:
  • Resource Engagement:

Resource management functionality is a major gap in current PPM version. 
There isn’t any proper mechanism in current PPM solution, that can allow Resource Manager to control allocation of resource to Projects. And Project Managers to formally request to block required resource for certain period of time to ensure availability of resource.
You can find this feature demonstrated during Ignite session: IT Project and Portfolio Management in the Cloud

  • Multiple Timelines in project professional, so instead of one you can now create multiple timelines in Project Pro 2016 and then later use it in your presentation or share through an email.
  • Visualizing Resource Capacity using Heat Map.

  • Project Server 2016 would ‘literally’ be available as a service in SharePoint 2016. So no need to install any separate binaries for Project Server. Separate license would be required though to enable Project Server service.
  • PWA database would be merged to SharePoint content db, so there will only be one database for each PWA instance and the schema for PWA would bepjrep -“. means less Admin work.

Sharepoint 2016: 
Any improvements to SharePoint architecture would definitely leverage Project Server performance as well. There were quite few announcements been made related to SP 2016, such as:

  • introduction to MiniRole approach to define server role within a farm. 
Role Name
Description
Special Load Reserved for services to be isolated from other services, I.e. 3rd party, PerformancePoint, etc.
Web Front End Services end user requests, optimized for low latency.
Single Server Farm Provisions all services on the server for a single server deployment.  This role is provided for evaluation and development purposes.
Search Reserved for Search services.
Application Services the backend jobs or the requests triggered by backend jobs, optimized for high throughput.
Distributed Cache Services distributed cache for the farm. Optionally, the server assigned to this role can load balance end user requests among the web front ends.
  • for implementation and deploymentSharePoint 2016 requires Windows Server 2012 R2 or Windows Server 10 for the OS and SQL Server 2014 SP1 x64 or SQL Server vNext x64
you will find detailed hardware and software requirements of SharePoint 2016 here: 

  • zero down time to build patches with SharePoint 2016,
  • Delve people profile will be part of SharePoint 2016. If i understood correctly, then it would be first released for SharePoint online, and then may later be pushed to On-Premises version
There is so much more that its difficult to consolidate everything in one post. So i will keep on writing about new stuff to be released later this year. For those who are interested to catch up Ignite sessions, please browse Channel 9 for your favorite sessions and watch.
Please also register for this great upcoming webinar , will be presented by my colleagues, to find out first hand information and opinions about all the happenings during 5 days of Ignite sessions.

PPM Updates From Microsoft Ignite

via All about Enterprise Project Management (EPM) http://bit.ly/1FqpBqd

Khurram Jamshed
The author of the blog has an extensive experience of working as an EPM Consultant. Currently he is located in Dubai, UAE and working for Microsoft partner organization as Project Server specialist. He has a thorough experience of providing Project Management technical/functional consultancy to all sort of organizations. He is a certified PMP, a Project Server MCITP, and also received a MS community contributor award 2011.

This article has been cross posted from khurramjamshed.blogspot.com/ (original article)

#ProjectServer and #SharePoint 2010 / 2013 May 2015 Cumulative Update #PS2010 #SP2010 #PS2013 #SP2013 #MSProject

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

The Office 2013 May 2015 Cumulative Updates are now available, please see the links below:

http://bit.ly/1A1UeDm

Project Server 2013 May 2015 Server Roll up package:
http://bit.ly/1QLs5oe

Project Server 2013 May 2015 CU:
http://bit.ly/1A1UeDn

Project 2013 May 2015 CU:
http://bit.ly/1QLs4As

Worth noting is that the SharePoint 2013 and Project Server 2013 updates are in Windows Updates: http://bit.ly/1A1UeDr

Also worth noting, if you haven’t done so already, install Service Pack 1 http://bit.ly/1uorn2C first if installing the May 2015 CU.

The Office 2010 May 2015 Cumulative Updates are now available, please see the links below:

http://bit.ly/1A1UeDm

Project Server 2010 May 2015 Server Roll up package:
http://bit.ly/1A1UeDs

Project Server 2010 May 2015 CU:
http://bit.ly/1QLs5oh

Project 2010 May 2015 CU:
http://bit.ly/1A1Ucvi

SP2 is a pre-requisite for the Office 2010 May 2015 CUs.

For more details on these patches see: http://bit.ly/1QLs4Ay

As always, fully test these updates on a replica test environment before deploying to production.

Categories: Paul Mather, Work Tags:

#ProjectOnline / #ProjectServer 2013 Access #SharePoint lists on PDPs #JavaScript #jQuery #Office365

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

A common requirement is to see and access the Issues and Risks lists on the Project Detail Pages in the PWA site for the associated project. Previously I wrote a script that displayed the Issue and Risks data in a table but this was read-only. This can be seen here: http://bit.ly/1ctq4xK 

This new script will display the Issues and Risks (or any other list for that matter – slight code change / update required) on the PDP but will allow you to create / edit the lists items providing you have the permissions of course. Martin did something similar here. The script can be downloaded from the script gallery below:

 http://bit.ly/1ctq68Z

To get the script to work you will need to download the following jQuery library: jquery-2.1.1.min.js – jQuery download Another version of this library may work but this was the one I used / tested with. Upload this library to your PWA site collection then update the script file with the correct location. I uploaded this file to the style library as you can see in the code below:

 image

Alternatively you can reference a CDN if preferred.

Once the script is downloaded, upload this to the PWA site collection, in this example it was uploaded to the shared documents library. Choose what PDP you want the script to be accessed, in this example I created a PDP called SharePoint Data, added a content editor web part on the new page then referenced the uploaded script using the content editor web part:

 image

Once loaded, the page will look like this:

 image

Don’t worry about the message – this is because there is no project UID available here.

Add the new PDP to the require Enterprise Project Types then navigate to the Project Center and access one of those projects from that EPT. Click the new PDP and you should see:

 image

Click one of the buttons and that list should load:

 image

You can also change the behaviour of the list form and use modal pop ups for a better experience:

 image

This currently includes Risks and Issues but can easily be updated to include other SharePoint lists.

The script is provided "As is" with no warranties etc.

Categories: Paul Mather, Work Tags:

#Microsoft #MSIgnite Day 5 #Office365 #Cloud #SharePoint #ProjectOnline

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

The last day of the Microsoft Ignite conference I lined up three sessions, one on why trust Microsoft Enterprise cloud with your data, the next session was on auditing in Office 365 and the final session before we make our way home back to the UK was the Project Online / Project Server support.

The session on Office 365 and why trust Microsoft is a big topic and one with split views, Microsoft are working very hard to make customers data secure and also make the customers know their data is secure. There is lots of information on how Microsoft do this and the compliance standards etc. here: http://bit.ly/1ALekfP. I believe the data is probably more secure in the Microsoft cloud than it is in your own network – obviously there would be some exceptions to this for government / highly secure sites etc. Also a key take away from that session was that it isn’t the cloud that isn’t secure, it is the devices that the end users use each day (connecting to on-prem or cloud). Microsoft do monitor unusual activity from devices and make this information available to the admins.

The second session was on auditing options in Office 365. This covered the compliance center, the audit activity reports, the API available to for activity management and the centralised compliance auditing store. All the work Microsoft are doing around auditing shows how seriously they are taking security and how they are making this data available to admins.

The final session of the day was the Project Online / Project Server support session by Brian Smith. This was a great session and gave good insights on how the Project escalation engineers support project. Brian blogged about this session earlier: http://bit.ly/1zKFsR6

Time for me to head to the airport now to head home back to the UK. It’s been a great week here in Chicago with lots of things to understand and digest. Top marks for Microsoft, a great conference :)

Categories: Paul Mather, Work Tags:
Follow

Get every new post delivered to your Inbox.

Join 1,784 other followers

%d bloggers like this: