Freeware - Logparser.WSS30 (SharePoint)

Also see the next post concerning Log Parser Sharepoint output solution

I recently came across the need query a SharePoint Document and List libraries programatically . I played with CAML and found the experience fairly miserable. I played with LINQ and warmed a little to the technology, but in the end came back to Logparser and writing a custom input format plugin.

I decided that LogParser needed a SharePoint (WSS 3.0) Log Parser COM input format plugin. I've been playing with the plugin for a couple of months as the complexities in Sharepoint web services soon became obvious.

Hope you find the utility useful, and if not the utility then the code. Enjoy!

Features

• Freeware with VB.NET 2005 source code included
• Can query WSS 3.0 list, wiki, or document library content. Have only tested on WSS 3.0 - it may work on earlier or later versions.
• Supports retrieving columns and rows back to the LogParser engine based on your existing defined views in the list, wiki, or document library. Additionally can ignore columns in the view and returning all fields (including hidden).
• No CAML. Create a SharePoint view and call it with LogParser using all its querying techniques on the returned data.
• Supports multiple list, wiki, or document library querying.
• Supports version (check in) details for list, wiki or document libraries. This enables you to retrieve all historical changes for a list if version control is enabled on the list, wiki, or document library.
• Implements iCheckPoint to support only showing the latest added content to the list, wiki, or document library
• Implements a number of custom columns (as per standard LogParser). They are listed below. This is to provide extra querying features.
• Logs any errors to console and to EventLog.Application
• Supports Log Parser data types.
• Uses the ILogParserInputContext interface to enable LogParser COM interface to directly call. i.e, you can code against this plugin.

*************************************************

Download VB.NET source is included in download.

*************************************************

To install:
1. Copy the LogParser.WSS30 folder to a location on your harddrive
2. Run the command .\LogParser.WSS30\InstalldotNETasCOM.bat (need GACUTIL.EXE - part of the .NET FW 2.0 SDK - why?). This will install into the GAC the Gluegood.LogParser.WSS30.
3. Run your Log Parser query. e.g.

LogParser.exe -i:COM "select * From 'http://sharepoint.net/topsite/mysite/Lists/Change Control/AllItems.aspx'" -iProgId:Gluegood.LogParser.WSS30 -o:DataGrid

Syntax :-
LogParser.exe -i:COM "select * From '{URL1}','{URL2}',..." -iProgId:Gluegood.LogParser.WSS30 -o:DataGrid -iComParams:"iVersion={TrueFalse},iAllFields={TrueFalse},iSiteCollection={SiteURL},iListName={ListName or ListGUID},iViewGUID={ListGUID},"

• {URLx} - a URL to the path of the SharePoint list and including the view name. Within Sharepoint open a document library, choose your view and then copy the link. Supports multiple URLs as long as they are deliminated with a comma (,).
• iVersion - Whether the history for each record should be included. Default = False.
• iAllFields - Ignore the columns defined in the view and retrieve all columns including hidden columns. Default = False
• iSiteCollection, iListName, iViewGUID - You can manually define the site, list name and GUID for the view. This shouldn't be required except for where the code's logic fails to determine these 3 values from the {URL}. If using these settings set {URL} = '.' - see SampleQuery.bat for example.

Columns returned

• LogParserListName - The name, or GUID of the list, wiki or document library. GUID is returned where the title of the list is different from the URL path. SharePoint strips special characters from the URL path name. e.g. v2.4 vs v24
• LogParserRecordNumber - A counter used to uniquely identify the row.
• LogParserItemURL - The URL to the item in the list, wiki or document library.
• LogParserListItemId - (iVersion only) The unique item id.
• LogParserVersionId - (iVersion only) The Version number of the check in.
• LogParserVersionModified - (iVersion only) Time that the check in occurred.
• LogParserVersionModifiedBy - (iVersion only) Who checked in the change.
• LogParserVersionComments - (iVersion only) Provides the check in comments. Available only for Wiki and Document Libraries.
• {Columns defined in the view} - beyond the fields above only the columns defined in the view. Sharepoint provides a DisplayName and Name for each column defined. Where possible I try to name the columns (as per the view) by their DisplayName. When however I find that the DisplayName is non-unique I revert to its unique Name.

Challenges -

Version - The implementation of retrieving version details in Sharepoint isn't straight forward.

• Trap 1 - There isn't a single call to retrieve version details for a view. To retrieve history for items you must retrieve all the nodes using Lists.asmx -> GetListItems and then loop through each node and each column in that node using Lists.asmx -> GetVersionCollection. That's right GetVersionCollection provides history per column. Performance is therefore fairly horrible so if you are looking for version details ensure that
  a) create a view in SharePoint that has the fields you require (reduce the number of columns) and
  b) consider using checkpoint to reduce the amount of rows returned.
  Thanks to this site for pointing me in the right direction in regards to versions and Sharepoint.
• Trap 2 - You'd think that Version comments using the Sharepoint field ows__CheckinComments would come through using Lists.asmx -> GetVersionCollection, especially because it it part of GetListItems. Not in Sharepoint land. You do get the Comments, but only the last one (miserable!). To get all comments you need to make a call to Versions.asmx -> GetVersions and look at the comments attribute and then match up the version to the rest of the list details.
• Trap 3 - I really struggled in ensuring that all parts of the version details aligned. The simpliest way I could find was creating a datatable with the columns I required and then creating a base entry for all the versions available for that item based on the ows__UIVersionString field and using its Modified field (date/time). Then because every GetVersionCollection for a field returns a Modified field in addition to the field you are after you are able to sync it up against the row version (why it doesn't return the ows__UIVersionString is beyond me). Because in Versions.asmx -> GetVersions you can't rely on the Modified time we are fortunate that version is returned (equivalent to ows__UIVersionString) as a matching row.

Column headings - If you call Lists.asmx -> GetListItems you get a list of ALL fields available and secondly their column name is based on the internal Sharepoint name, not the name displayed to end user (these names are truncated by the way, so not very nice). To get around this I make an initial call to Lists.asmx -> GetListAndView which supplies for the view the internal Sharepoint name (Name) and its friendly name (DisplayName). . A userful trick is that in the datatable I create and store the Name as the Column.Name and the DisplayName as the Column.Caption - useful as they are often different. Sharepoint allows for the same DisplayName therefore you need to (and I do) revert to Name for the Column.Caption if I find a duplicate. When I return back to Log Parser I use the Column.Caption. By using the GetListandView method you are also able to get the data types of the field, which I can then map to LogParser datatypes. All up by making this extra web service call you are able to get the columns in this view, their friendly name, and their datatype.

ViewName (you mean ViewGUID) - Sharepoint documentation for its web services provide a field named ViewName. e.g. GetListItems

Public Function GetListItems (listName As String, viewName As String, ... etc)

You'd think that like ListName you can provide either the 'friendly name' or the 'ListGUID'. In the case of ViewName it actually always means ViewGUID. So if you have the 'friendly name' you need to make a call to Views.asmx -> GetViewCollection and then hunt for your GUID. I do this in the code for you.

xpath and Sharepoint - I spent a couple of days fighting with xpath queries and Sharepoint. After a number of frustrating hours I finally discovered a site which talked about needing to use namespaces in SharePoint xpath queries.

e.g.

Dim nodeListItems As System.Xml.XmlNode = Webservice.GetListCollection()
Dim xpq As String = "//Lists/List"
For Each Node As System.Xml.XmlNode In nodeListItems.SelectNodes(xpq)

while this does

Dim nodeListItems As System.Xml.XmlNode = Webservice.GetListCollection()
Dim xpq As String = "//sp:Lists/sp:List"
For Each Node As System.Xml.XmlNode In RunSharePointXPathQuery(nodeListItems, xpq)

* Download code for function RunSharePointXPathQuery

Date Time in SharePoint web service - It is truely amazing that different web methods in SharePoint return times in UTC or local timezone format. The most frustrating is that Lists.asmx -> GetListItems can return in UTC, while Versions.asmx -> GetVersions and Lists.asmx -> GetVersionCollection do not. There is a useful blog talking about UTC and SharePoint.

*************************************************

** Legal **
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.