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://corporate1. 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.
C:\iislogs\logparser.exe
-i:W3C
"select cs-username as User, Count(*) as ReadCount, date into report.csv from .\* where cs-uri-stem = '/marketing/documents/myspecialdocument.pdf' and User Is Not Null and date > Timestamp('2013-07-09','yyyy-MM-dd') group by
user,date" -o:CSV2. 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:
$request = New-Object psobject $request | Add-Member -MemberType NoteProperty -Name "Name" -value "" $request | Add-Member -MemberType NoteProperty -Name "Location" -value "" $request | Add-Member -MemberType NoteProperty -Name "Department" -value "" $request | Add-Member -MemberType NoteProperty -Name "EmployeeType" -value "" $request | Add-Member -MemberType NoteProperty -Name "Reads" -value ""
Copy the data from the CSV file to a PowerShell object we can use (from more information importing CSV files, see: Import-CSV):
$r = Import-Csv .\report.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
$a = $null; $a = @();
2. For each object (aka line of the CSV file) in $r
foreach($i 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
$i.User.Substring($i.User.IndexOf('\')+1)
4. Pass the trimmed username to the Get-AdUser cmdlet, and request the additional properties, displayName, department, office and employeeType
$u = get-aduser $i.User.Substring($i.User.IndexOf('\')+1) -Properties displayName,department,office,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
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; /span> $u.department; $b.EmployeeType = $u.employeeType; $b.Reads = $i.;}
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:
$request = New-Object psobject $request | Add-Member -MemberType NoteProperty -Name "Name" -value "" $request | Add-Member -MemberType NoteProperty -Name "Location" -value "" $request | Add-Member -MemberType NoteProperty -Name -MemberType NoteProperty -Name "Name" -value "" $request | Add-Member -MemberType NoteProperty -Name "Location" -value <> "Department" -value "" $request | Add-Member -MemberType NoteProperty -Name "EmployeeType" -value "" $request | Add-Member -MemberType NoteProperty -Name "Reads" -value "" $r = Import-Csv .\report.csv $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; ;}} $nr = @(); $nr += "Total Count: "+$a.Count $nr += "" $nr += "Count per office:" $b = $a | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name} $nr += "" $nr += "Total Partners" $b = $a | ?{$_.Department -like "Partners"} | measure; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name} $nr += "" $nr += "Partners / Office" $b = $a | ?{$_.Department -like "Partners"} | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name} $nr += "" $nr += "Total Support Staff" $b = $a | ?{$_.EmployeeType -like "SupportStaff"} | measure; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name} $nr += "" $nr += "Support Staff / Office" $b = $a | ?{$_.EmployeeType -like "SupportStaff"} | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name} $nr += "" $nr += "Total Fee Earning Staff" $b = $a | ?{$_.EmployeeType -like "FeeEarningStaff"} | measure; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name} $nr += "" $nr += "Fee Earning Staff / Office" $b = $a | ?{$_.EmployeeType -like "FeeEarningStaff"} | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
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
References
- This article originally came from a blog post of Matthew Yarlett, which can be seen here: Quick Analytics from SharePoint (kind of...)