Introduction

SharePoint has built-in analytic reports, but they don't always provide you with the data you need.

This article demonstrates how you can combine two great Microsoft tools, LogParser and PowerShell, to create a custom report that summarizes the access to a particular PDF file in a SharePoint library,  broken down by department, location and employee type (a custom Active Directory attribute). 

PowerShell probably doesn't need much of an introduction, but LogParser might. It's a tool that has been around for a while now, and it's extremely useful for parsing and querying a variety of log file formats. It can then output the results in various formats. You can download LogParser from Microsoft here, Download Log Parser 2.2, and there is great information on using LogParser on the TechNet site, Log Parser 2.2.  

To complete this exercise, you will need to:

1. Copy the IIS log files (for the SharePoint web application in question) from all of the WFE (web front end) servers into a single directory on your computer

2. Open a PowerShell console that has the Active Directory module installed, change to the directory with the log files, run LogParser against the files, and extract the data you want into a CSV file.

[Notes] 
a). This example assumes all of the IIS Log files for the web application http://corporate were copied from all the SharePoint web front end servers in the farm to c:\iislogs directory.
b). You have installed LogParser 2.2
c). You have the Active Directory module loaded. You can find out about installing and using the Active Directory module for PowerShell on Microsoft's TechNet site, here:
Active Directory Administration with Windows PowerShell  and here Active Directory Cmdlets in Windows PowerShell  

Simple hey? Let's take a closer look.

Walk Through

 
This example steps through creating a report the number of staff in a global firm that have downloaded a document, myspecialdocument.pdf, from the documents library in the SharePoint Marketing site. The URL of the document is http://corporate/marketing/documents/myspecialdocument.pdf, and the web application is http://corporate

1. The first thing to do is write a log parser command to select all the logs in the last 30 days (since 2013-07-09) where the URI = /marketing/documents/myspecialdocument.pdf, where the user is not null. Aggregate and count all the logs for each unique visitor, and stored them in the ReadCount column. Output the results to a CSV file called report.csv.



2. Next is the fun bit, using PowerShell to bring it all together. We create an object to store each record in, then add the records to an array, and use the array to create the report.... Oh, and we use the Active Directory PowerShell module to get some extra data about each user from Active Directory (department, office location and employee type).

First, create the object for storing each record in:



Copy the data from the CSV file to a PowerShell object we can use (from more information importing CSV files, see: Import-CSV):



The object $r now contains each line of the CSV file (as a collection of objects), and we can access individual columns by their original column name in the CSV file. I.e. $r[0].user

Next, create a new array, loop through each object in $r (aka each line of the imported CSV file), format the 'user' column (we need to trim off the domain name), passing the 'user' string to the Get-AdUser cmdlet (returning a user object containing the extra properties (displayName, Office and employeeType) needed for the report, parse the object returned from Active Directory, extracting the contents into a new "request" object and merge it with the details from the current object in $r, and finally add it to the array. Phew... that was a long sentence, but really quite straight forward when you break it down!

To break it down a little;

1. Create an empty array to store the data used for the report


2. For each object (aka line of the CSV file) in $r


3. Getting the "user" property. The user is represented as domain\user, so we need to trim off the domain name and forward slash


4. Pass the trimmed username to the Get-AdUser cmdlet, and request the additional properties, displayName, department, office and employeeType

 
5. If the object returned from Active Directory is not empty, then create a new "request" object, and populate it with information from the current object in $r (aka current line in the CSV file), and finally add it to the array


Here's the full command, condensed:

$a = $null

$a = @()

foreach($i in $r){$u="";$u = get-aduser $i.User.Substring($i.User.IndexOf('\')+1) -Properties displayName,department,office,employeeType -ErrorAction:SilentlyContinue; if($u -ne ""){

$b = $request | Select-Object *; $b.Name = $u.displayName; $b.Location = $u.office; $b.Department = $u.department; $b.EmployeeType = $u.employeeType; $b.Reads = $i.ReadCount; $a += $b;

;}}

Once we have done this, we have all the information we need in a new array of objects, and it's simply a case of formatting the data the way we want it and creating the report. Because we have an array of "request" objects, this is very easy, as we can iterate over them, group them, sort them, all using PowerShell's built-in cmdlets, such as Format-Table, Sort-Object, Measure-Object, Where-Object and Group-Obejct. In this example, we need to display the following information:

Total amount of people who opened the document
Total amount of people by Office
Total people with the job title "Partners"
Total people with the department Fee Earners
Total people with the employee type (a custom Active Directory attribute) "Business Services"

The script for the report looks like this:


And when displayed, it looks like this...

Total Count: 477

Count per office:
52      Hong Kong
42      Hamburg
8        Monaco
191    London
12      Le Havre
19      Paris
33      Dubai
36      Greece
29      Singapore
33      Shanghai
20      Singapore Local
2        Beijing

Total Partners
92

Partners / Office
58      London
1        Hamburg
5        Singapore
11      Greece
2        Shanghai
8        Dubai
3        Singapore Local
4        Paris

Total Support Staff
195

Support Staff / Office
23      Hong Kong
35      Hamburg
58      London
12      Le Havre
15      Paris
11      Greece
7        Dubai
15      Singapore Local
5        Shanghai
13      Singapore
1        Beijing

Total Fee Earning Staff
269

Fee Earning Staff / Office
6         Monaco
7         Hamburg
133     London
26       Dubai
29       Hong Kong
15       Greece
21       Singapore
28       Shanghai
4         Paris 

This article originally came from a post of Matthew Yarlett  which can be found here Quick Analytics from SharePoint (kind of...)