Generate Advanced Reports using LogParser

Overview

With iShare it can be useful to extract information from the IIS logs. This page details how to generate iShare Maps or iShare GIS reports from these IIS log files. You may also wish to view the page Analyse Logs using MS Log Parser for other examples.

Log Parser 

Log Parser is Microsoft's utility for treating log files, or directories of log files, like database tables, with a SQL-like syntax for constructing queries. MS Log Parser may be installed using the Astun 3rd Party installer or downloaded from the Microsoft web site:

http://technet.microsoft.com/en-gb/scriptcenter/dd919274.aspx

Log Parser Studio

Microsoft's graphical front-end for Log Parser. Select the preparedlogs folder as the source log file path and ensure that the Log Type is set to W3CLOG (not IISW3CLOG which doesn't not allow custom fields such as those in the prepared logs folder). This tool is good for ad-hoc queries and testing changes to script SQL.

http://blogs.technet.com/b/exchange/archive/2012/03/07/introducing-log-parser-studio.aspx

http://blogs.technet.com/b/exchange/archive/2013/06/17/log-parser-studio-2-2-is-now-available.aspx

C:\Inetpub\Logs\Logfiles\W3SVC1*

C:\Inetpub\Logs\Logfiles\W3SVC3*

D:\Astun\Stats\preprocessor.py 

D:\Astun\Stats\preprocessor.cfg

D:\Astun\Stats\PreparedLogs

D:\Astun\Stats\Scripts\*.bat

D:\Astun\Stats\Output\*.csv 

C:\Program Files (x86)\Log Parser 2.2\

C:\Program Files (x86)\Log Parser Studio\

Getting Started

1. Download Log Parser or Log Parser Studio
2. Download the attached zip file "auditing.zip" and ensure it's unblocked before extracting
3. Extract it to somewhere like the Astun Tools folder (normally on the D: drive)

Step-by-step guide

The generation of the audit report files proceeds like this:

1. Scheduled task runs the 'preprocessor' app
2. Preprocessor sends prepared files to new location
3. When required, run the appropriate Log Parser script
4. Script generates output files

Preprocessor

The preprocessor Python app is used to turn raw IIS logs into something more palatable for Log Parser. It does this by breaking single requests for many layers into many requests for single layers, parsing the string for MapSource/profile information, constructing bounding box information for different ways of specifying geometries and tagging each request with a type.

This is intended to be run as a Scheduled Task, but can be run as required.

Note: The preprocessor can currently only parse one directory of logs at a time. It should be pointed at the folder for the live website.

Configuration

The two entries in preprocessor.cfg file that should be checked/changed are

[Input]
folder=C:\path\to\source\log\files
 
[Options]
strip_domains=True

Input/folder

This is the location from which to load IIS logs, e.g. C:\inetpub\logs\LogFiles\W3SVC1.

Options/strip_domains

This setting determines whether domain names are stripped from the cs-username field:

• If it is omitted or is one of no, false, 0 or none then domain names are not removed
• yes, true, 1 or all means that all found domain names are removed
• Any other value is treated as a comma-separated list of domain names to remove. If your logs contain a mixture of down-level (DOMAIN\USER) and user-principal name (user@domain.tld) formats then both the styles have to be included, e.g.: corp,corp.net

Username handling

Since the backslash separator in down-level usernames is used as a folder delimiter in Windows and is an escape character in Windows batch files, Python and SQL, these will be changed in the output files cs-username field to use the user-principal name style, e.g. CORP\EMPLOYEE in source files will become employee@corp in the prepared log and output files - unless the domain names are being removed as per the setting above.

iShare custom fields

In addition to the IIS log fields in the source fields, the prepared log files have the following additional fields, where applicable:

• ishare-type - this indicates the type of the request which can be one of:
  • map -  a map image request 
  • data - a request for feature information 
  • export - feature information exported to a file (or at least to a standard format)
  • print - printing to single image or PDF
  • legend - image request for layer legend(s)
  • metadata - a request that gets information about the layer
• ishare-profile - the profile (a.k.a. MapSource) name of the request
• ishare-layer - the unique layer name (not the display name) of the layer in the request
• ishare-min-x, ishare-min-y, ishare-max-x, ishare-max-y - coordinates of the bbox of the request
• ishare-x, ishare-y - coordinates of the centroid of the request

Log Parser scripts

These are run from the Windows command line and expect the current directory to be their own.

Usage note: all dates must be of the format YYYY-MM-DD, e.g. 2015-01-01.

Users accessing a profile

D:\Astun\stats\scripts>profile-users.bat profile [from-date [to-date]]

This script counts all requests for a profiles by all users.

Output

profile,username@domain,requests-count

Users accessing a layer

D:\Astun\stats\scripts>layer-users.bat profile layer [from-date [to-date]]

This script counts all requests by all users for a layer in a particular profile (the same name could be used in different profiles to refer to different data)

Output

profile,layer,username@domain,requests-count

Layer access by a user

D:\Astun\stats\scripts>user-layers.bat username [from-date [to-date]]

This script lists all layer requests by an individual user account with the extents of all spatial requests

Output

username@domain,profile,layer,timestamp,min-X,min-Y,max-X,max-Y

Exports

D:\Astun\stats\scripts>exports.bat [from-date [to-date]]

Output

username@domain,profile,layer,timestamp,min-X,min-Y,max-X,max-Y

This script lists the profile, layer, date and time and extents of all export requests made by all users. 

Prints

D:\Astun\stats\scripts>prints.bat [from-date [to-date]]

This script lists the profile, layer, date and time and extents of all print to PDF requests made by all users. 

Note: each layer in a single print gets listed separately

Output

username@domain,profile,layer,timestamp,min-X,min-Y,max-X,max-Y