Get your SharePoint On Premise Logs through a Web API…

… and connect it to Power BI !

Dear Fellows !

Today let’s see how we can use the new Web API project to connect to your SharePoint On Premise.

The goal there is to create web service you can use with Power BI to analyze logs of your SharePoint.
Please note that I will focus on the Web API development part, and not on the Power BI part : in the real world, you’d need a data warehouse to store all of the SharePoint logs, before exploiting it with Power BI.

So first things first, let’s take a look at this little schema.MVC Web API.png

As you maybe know, since SharePoint 2013, the work of collecting audit datas is dedicated to the Search Service. But, it’s totally transparent for you, developer.

Swagger is truly the best tool you can use to test your Web API.

Ready ?! Let’s code (on a SharePoint Server 2013 Dev Farm with Visual Studio 2013 (or higher) with last of last updates) !

In SharePoint, activate the Audit stuff : at the site collection root > site settings > audit settings of the site collection and tick everything you wand to log.

Create a projet Web API project. File > New project > Type « web api » in the search box > Select « ASP.Net Web Application » > Name your application > clic « OK »Capture d’écran 2016-03-05 à 09.47.56.png

Select Web API empty project, tick « Web API » at the bottom of the page. Clic « OK »
Capture d’écran 2016-03-05 à 17.45.43.pngYour project is being created.

Add Swagger to you Web API Project You’ll see why we use Swagger ! We’ll use NuGet for that. So, open the « tools » menu > NuGet Package Manager > Package Manager Console.
Capture d’écran 2016-03-05 à 17.48.07.pngIn the console, paste « Install-Package Swashbuckle »

Add a reference to SharePoint library. Right clic on « Reference » > search and tick « Microsoft.SharePoint.dll » (note : if you don’t find this DLL, that’s because you’re not on a dev farm).
Capture d’écran 2016-03-05 à 17.54.32.png

Create an empty controller. Right click on « Controllers » > Select a « Web API 2 Controller – Empty » > click OK > Name it « SPLogsController »Capture d’écran 2016-03-05 à 17.57.20.pngCapture d’écran 2016-03-05 à 17.58.08.pngCapture d’écran 2016-03-05 à 18.00.48.png

In the newly created file we will add a method that use the SharePoint Audit SDK to retrieve all Logs entries.First, add a using to the SharePoint Reference at the top of the page.

using Microsoft.SharePoint;

Then, add this method. Note the convention : the term « Get » at the beginning of the name of this method will identify it as an HTTP Get request method.

public SPAuditEntryCollection GetPagesViews(string webUrl, string date)
SPAuditEntryCollection logs = null;
using (SPSite site = new SPSite(String.Concat("http://", webUrl)))
using (SPWeb web = site.OpenWeb())
SPList pages = web.GetList("Pages");
SPAuditQuery query = new SPAuditQuery(site);
//only query items which have been viewed.
//set the query date range
DateTime dtDate = DateTime.Parse(date);
logs = web.Audit.GetEntries(query);
return logs;

Capture d’écran 2016-03-05 à 18.43.09.png

We’ll have a few changes to do in the « App_Start » > « WebAPIConfig.cs » file.

Replace the config.Routes.blablablabala by :

name: "DefaultApi",
routeTemplate: "api/{controller}/{action}/{webUrl}",
defaults: new { }
Capture d’écran 2016-03-05 à 18.44.06.png

Two more things, to set the « 64 bit side of SharePoint »…

Double click the « Properties » node of the solution, in the « Build » tab, in « plateform target » choose « 64 bit »
Capture d’écran 2016-03-05 à 18.45.47.png

Then, clic « Tools » menu > « Options » > « Projects and Solutions » > « Web Projects » > and tick the box « Use 64 bit version of IIS Express (…) »

Capture d’écran 2016-03-05 à 18.30.11.png

Please be sure that you have launched Visual Studio as an administrator.

Hit F5 (or the Debug button), wait, wait, … wait again, and you’ll have a really beautiful error page ! … … No problem, that’s foreseen !

Capture d’écran 2016-03-05 à 18.51.24.png

Add « /swagger » to the Url, and let’s enjoy the power of this tool !

Capture d’écran 2016-03-05 à 18.52.31.png
Swagger is doing a kind of scan of your web API to give you perfect testing interface for your API. Click on « SPLogs ».

Capture d’écran 2016-03-05 à 18.53.43.png

You see your HTTP Get method, there, if you click on it, you’ll be able to test your method.

Capture d’écran 2016-03-05 à 18.54.53.png
In the « WebUrl » field, add the URL of a SharePoint Site that contains a « Pages » library, with page in it. Note that you have to visit some of those pages to generate log entries you can view there.

Click on the « Try it out ! » button… Wait… Wait… And bam bam kaboum ! An array of results… Each occurence of the array corresponds to a visit on a page of your SharePoint library called « Page ». You’ll find severals informations about the visit, like the UniqueID of the visited page, the UserId that visited the page, …Capture d’écran 2016-03-05 à 18.58.33.png

Each occurence of the array corresponds to a visit on a page of your SharePoint library called « Page ». You’ll find severals informations about the visit, like the UniqueID of the visited page, the UserId that visited the page, …

Note that the Request URL on the page is composed of arguments of your Web API Method : http://localhost:20200/api/SPLogs/GetPagesViews/win-hleg3mek870%3A93?date=05%2F03%2F2016

It’s this URL you can use in Power BI to request SharePoint, with a blank query

Deploy your Web API on a real IIS Site.

I won’t explain you how to create a IIS Website and I WILL CERTAINLY NOT EXPLAIN HOW TO ACTIVATE THE F*****G WEB DEPLOY FEATURE ON IT. Trust me, I love Web Deploy, but setting it is a painful path… I even didn’t know how I succeed in this task…
The only thing you must know is : make the SP Web App app pool your Web API IIS Website app pool. It’s the only way I find for the Web API to get the authorization to request SharePoint. Maybe there is another way I’ve just thinking about : the App Pool account of your Web API IIS Website could be the same as the SP Web App’s App Pool… I will try this later and let you know the result.

So, changing the App Pool of a IIS Website is easy : go in the IIS manager, bellow the « Sites » node, select the site you’ve created to deploy your We API, clic on « Advanced Settings » and change the « Application Pool » to you SharePoint Web App Application Pool !

Capture d’écran 2016-03-05 à 19.12.41.png

That’s it ! Please do not hesitate if you have any question ! I’ll try to help !