Gluegood Software

VSTO, ClickOnce, MSBuild equals happiness

2010-07-09T22:56:00.002+10:00

After spending several frustrating days working on VSTO and ClickOnce deployment I thought I'd share the experiences in the below guidance and instructions for newbie’s to follow (like I was a few days ago). There are some great articles out there, but a lot of misinformation because of differences between VS2008 and VS2005.

The objective :-From an intranet hosting a VSTO package enable a use to click on a URL of the document (http://mysebserver/VSTO/vstogluegood.docx) that is opened on their PC and the required VSTO file is installed and ran. The VSTO ClickOnce package I wanted to be created using MSBuild project file.

Target development environment and Office version
- VSTO developed using Visual Studio 2008
- Office 2007 (specifically targeting Word 2007)

Client side components required
The below are a list of client side components required to run VSTO packages using ClickOnce.
- .NET Framework 3.5
- Microsoft Office 2007 System Update Redistributable PIA (o2007pia.msi)
- Microsoft Visual Studio Tools for the Microsoft Office system (vstor30.exe)

Client side component installs note
- I opted for a pre-deployment of the packages through ActiveDirectory group policy. The o2007pia.msi is easy to place in the OU, however the vstor30.exe isn't because it is an .exe. The solution we found was to extract the vstor30.exe using the below command :-

vstor30.exe /x:Extract

Using the extracted contents in <current directory>\Extract\, we simply placed the trin_trir.msi in the OU. I suspect this is less than ideal, but worked for us.

Certificates
You need a certificate to deploy and run VSTO packages. There are 3 choices :-
a) Purchase a certificate (mandatory if you are deploying beyond your internal network)
b) Use the Temporary Visual Studio certificate created (downside - expires in 1 year)
c) Create a Certificate using makecert.

I'm using the 3rd choice as it provides you with a more robust certificate which can be deployed using ActiveDirectory. Below are the steps :-

1. Open the Visual Studio Command Prompt
2. Type makecert -r -pe -n "CN=<yourCertificateName>" -b 01/01/2000 -e 01/01/2099 -ss My
3. We have made a .cer certificate, but for Visual Studio we need a .pfx certificate. Within Internet Explorer under Options->Content->Certificates find your certificate and export it as a .pfx, (Choose export private key). Now keep this certificate for the VSTO packages.
4. To create a .cer which isn't exportable (and can be pushed out onto client PCs), simple delete the existing certificate created in step 3 and double click on the .pfx file created in step 4. Install the certificate (mark as not exportable) and then export it as a .cer.

You will need to install the non-exportable .cer on the users' PCs, while using the .pfx file in Visual Studio when you create your packages.

Certificate installation
The certificate created above needs to be installed in
a) Trusted Root Certificate Authorities
b) Trusted Publishers

Manual
- On each PC double click on the .cer file created above as administrator and install into the 2 locations.

OU
- In the Group Policy Object Editor add the certificates to Computer Configuration > Windows Settings > Security Settings > Public Key Policies. For a full walkthrough see this (don't forget to place in both certificate locations).:-https://support.smoothwall.net/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=180

Word configuration
To automatically allow the Word document to run the ClickOnce deployment it must have the location of the website in its Trusted locations.

Manual
- Within Word goto Office menu->Word Options->Trust Center->Trust Center Settings->Trusted Locations.
a. Allow Trusted Locations on my network – checked
b. The following list of locations :- e.g.
http://website-Prod/VSTO/
http://website-UAT/VSTO/
http://website-TEST/VSTO/
http://website-DEV/VSTO/
* Ensure Subfolders of this location are also trusted is checked when added.

Note: Word has a quirk where the VSTOs must be installed in the subfolder of the site (for Trusted Locations), so you can't use http://MyWebSite/Gluegood.docx, while you can use http://MyWebSite/VSTO/Gluegood.docx

OU
- Follow instructions here on how to set the above in an OU (http://technet.microsoft.com/en-us/library/cc178948(office.12).aspx )

Web site configuration
So that the VSTO runs correctly you need to add the MIME type of extension .vsto and application/x-ms-vsto. See this article for instructions (http://msdn.microsoft.com/en-us/library/bb608629.aspx )

MSBuild project file (aka the Magic happens here)
VS2008 comes with a nice 'Publish' UI, however it lacks features in the following ways :-
1. You are unable to differentiate builds between the environments.
2. It can't be part of a scheduled build

There seem lots of other ways of doing the below, but I like the simplicity of building and creating the VSTO in once step using MSBuild without needing to fiddle with other tools.

Below I have provided VB.NET code to generate the MSBuild file and kick off an install. The below however can easily be converted to .msbuild file and .cmd file with hard coded values.

There are 2 parts to the MSBuild file :-
a) The properties created in the Property Group
b) The Actual MSBuild settings

Below are parameters which are passed to the code :-
SolutionFile - This is the location of the .sln file to be compiled. e.g. C:\Projects\VSTOGluegood.sln

Version - This is the version of the VSTO project. e.g. 3.1.2.29

Environment - We use this to determine the SolutionId (see below)

Destination - This is the network address in which you want to create the VSTO. e.g. \\MyWebServer\e$\Site\VSTO\

DestinationURL - This is the public address in which the VSTO will be installed, when new versions are checked by the clients this is the location they will use. e.g. http://MySite/VSTO

SolutionId - To make the VSTOs unique for each environment they must have different AssemblyName and different SolutionId – (http://briannoyes.net/2006/11/03/ClickOnceDeploymentApplicationIdentity.aspx) . The function CompileVSTOReports_GetSolutionId in the below code looks for the .vbproj and extracts the SolutionId, however it replaces the last character so each environment is unique, e.g. Our ProjId is dd0d7e0e-7913-4e9a-8541-d3ef27387d16, for VSTO deployments the 6 becomes an 'a' in DEV, 'b' in TEST, 'c' in UAT and 'd' in PROD.

ManifestCertificateThumbprint - This value you extract from the .vbproj file and is the password you created for the .pfx file encrypted by Visual Studio. To get this value in Visual Studio sign the project with the .pfx file and then open up the .vbproj file and find the value and paste it below.

  Private Sub CompileVSTOReports_CompileSolutionFile(ByVal SolutionFile As System.IO.FileInfo, _
                            ByVal Version As String, _
                            ByVal Environment As String, _
                            ByVal Destination As String, _
                            ByVal DestinationURL As String)

    Dim oStreamWriter As System.IO.StreamWriter = Nothing
    Dim sBuildFile As String = My.Computer.FileSystem.GetTempFileName

      Dim sMSBuild As System.Text.StringBuilder = New System.Text.StringBuilder

      sMSBuild.Append("<Project DefaultTargets=""Main"" xmlns=""http://schemas.microsoft.com/developer/msbuild/2003"">" & vbCrLf)
      sMSBuild.Append("<PropertyGroup>" & vbCrLf)
      sMSBuild.Append(" <SourceSolution>" & SolutionFile.FullName & "</SourceSolution>" & vbCrLf)
      ' If you change the PFX file you must change the Thumbprint below. This comes from the .vbproj file if you manually
      ' use the PFX file - when seen in a text editor.
      sMSBuild.Append(" <KeyFileLocation>" & My.Application.Info.DirectoryPath & "\gluegood.pfx</KeyFileLocation>" & vbCrLf)
      sMSBuild.Append(" <VersionNumber>" & Version & "</VersionNumber>" & vbCrLf)
      sMSBuild.Append(" <AssemblyName>" & "VSTO_" & Replace(SolutionFile.Name, ".sln", "") & "_" & Environment & "</AssemblyName>" & vbCrLf)
      sMSBuild.Append(" <PublishDir>" & Destination & "</PublishDir>" & vbCrLf)
      sMSBuild.Append(" <InstallUrl>" & DestinationURL & "</InstallUrl>" & vbCrLf)
      sMSBuild.Append(" <SolutionID>" & CompileVSTOReports_GetSolutionId(Environment, SolutionFile) & "</SolutionID>" & vbCrLf)
      sMSBuild.Append("</PropertyGroup>" & vbCrLf)

      sMSBuild.Append("<Target Name=""Main"">" & vbCrLf)
      sMSBuild.Append(" <MSBuild Projects=""$(SourceSolution)""" & vbCrLf)
      sMSBuild.Append("    Properties = ""Configuration=Release;" & vbCrLf)
      sMSBuild.Append("      InstallUrl=$(InstallUrl);" & vbCrLf)
      sMSBuild.Append("      PublishDir=$(PublishDir);" & vbCrLf)
      sMSBuild.Append("      PublishUrl=$(InstallUrl);" & vbCrLf)
      sMSBuild.Append("      SignAssembly=true;" & vbCrLf)
      sMSBuild.Append("      DelaySign=false;" & vbCrLf)
      sMSBuild.Append("      AssemblyOriginatorKeyFile=$(KeyFileLocation);" & vbCrLf)
      sMSBuild.Append("      ManifestKeyFile=($KeyFileLocation);" & vbCrLf)
      ' If you change the PFX file you must change the Thumbprint below. This comes from the .vbproj file if you manually
      ' use the PFX file - when seen in a text editor.
      sMSBuild.Append("      ManifestCertificateThumbprint=84489FD07E578BBA71CC67E9EB4F49E1B4EE740A;" & vbCrLf)
      sMSBuild.Append("      SignManifests=true;" & vbCrLf)
      sMSBuild.Append("      ApplicationVersion=$(VersionNumber);" & vbCrLf)
      sMSBuild.Append("      BootstrapperEnabled=true;" & vbCrLf)
      sMSBuild.Append("      UpdateEnabled=true;" & vbCrLf)
      sMSBuild.Append("      UpdateInterval=0;" & vbCrLf)
      sMSBuild.Append("      AssemblyName=$(AssemblyName);" & vbCrLf)
      sMSBuild.Append("      SolutionID=$(SolutionId);" & vbCrLf)
      sMSBuild.Append("      IsWebBootstrapper=True""" & vbCrLf)
      sMSBuild.Append("    ContinueOnError = ""false""" & vbCrLf)
      sMSBuild.Append("    Targets=""Publish"" />" & vbCrLf)
      sMSBuild.Append("</Target>" & vbCrLf)
      sMSBuild.Append("</Project>" & vbCrLf)

      oStreamWriter = New System.IO.StreamWriter(sBuildFile, False)
      oStreamWriter.Write(sMSBuild.ToString)
      oStreamWriter.Close()
      oStreamWriter = Nothing

Running the compile in code :-

      Dim sBuildLogFileName As String = "MSBuild_VSTO_" & Now.ToString("yyyyMMdd_hhmmss") & ".log"

      ' Run the build
      Dim oProcessInfo As System.Diagnostics.ProcessStartInfo = New ProcessStartInfo("C:\WINDOWS\Microsoft.NET\Framework\v3.5\MSBuild.exe", """" & sBuildFile & """ /clp:errorsonly /l:FileLogger,Microsoft.Build.Engine;logfile=" & sBuildLogFileName & " /filelogger")
      oProcessInfo.WindowStyle = ProcessWindowStyle.Hidden
      Dim oProcess = Process.Start(oProcessInfo)
      oProcess.WaitForExit()

      If oProcess.ExitCode <> 0 Then
        'Error
        Throw New Exception("Build failed for solution " & SolutionFile.FullName & ". See log file " & sBuildLogFileName)
      End If

Notes:
Debugging - VSTO and ClickOnce is 'fun' to debug. Below are some suggestions :-
1. Run the .vsto deployment file to install the package first. Do this directly by copying the file and then clicking on the link. If you get a security warning you know that Office is going to silently discard the VSTO, so troubleshoot this first.
2. In add/remove programs you can see the VSTO, so check it exists. Remove it manually between tests.
3. Initially run the .docx or .dotx locally from the C:\ of the test PC. This gets around any security issues.

Passing query string / parameters via URL - We required to deliver some parameters to the VSTO. A really simple way of doing this is by passing Me.Fullname to the below function e.g.

  Private Sub ThisDocument_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    ...
GetParameterIntFromQueryString("UserId", Me.Fullname)
   ...
  End Sub

  Public Shared Function GetParameterIntFromQueryString(Byval ParameterName as String, ByVal Location As String) As Integer
    ' Retrieve the ParameterName from the QueryString used to launch the document. Pass to this function me.Fullname
    Dim oURI As UriBuilder = New UriBuilder(Location)
    Dim sQueryString = oURI.Query.ToString

    If sQueryString = vbNullString Then
      Return 0
    Else
      Dim NameValueTable As New Collections.Specialized.NameValueCollection()
      NameValueTable = System.Web.HttpUtility.ParseQueryString(sQueryString)
      If IsNumeric(NameValueTable(ParameterName)) Then
        Return CInt(NameValueTable(ParameterName))
      Else
        Return 0
      End If
    End If
  End Function

e.g. Opening the VSTO from a website with http://myvstoserver/VSTO/UserReport.docx?UserId=123 would return 123 from the above function.

I would strongly suggest to prompt for the parameters if not supplied (use Inputbox) so it can be run in debug mode, or your users can run off website.

VSTO run once from .docx - We required our VSTO to run once and then not again. Unfortunately the original developer created the VSTO as a Word Document (.docx) which meant once the code the Word Document that was saved continue to embed in it the VSTO relationship, so when the user opened it again it would re-run wiping any manual changes they made.. The work around is either to change the project to a Document Template (.dotx), or in the ThisDocument_Startup at the end of the Finally block place the command Me.RemoveCustomization() - which strips the relationship between the document and VSTO.

Enjoy!

*************************************************
** 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.

Freeware - LogParser.TFSQuery (Team Foundation Server)

2009-08-01T06:07:00.003+10:00

I recently found myself needing to query Team Foundatin Server (TFS) like I had previously done with Visual Source Safe (VSS). I found I needed to following capabilities :-
- Querying and notification of recent work items
- Querying and notification of the most recent check-ins
- Querying and notification of check-outs which were over a time threshold
- Querying and notification of shelvesets (parked code) which expired a certain amount of time
- Querying and notification across linked work items

I could have used out of the box TFS functionality to provide some of the above, but some don't exist without reaching into the database. Therefore given I have LogParser.Email capabilities (ability to e-mail LogParser results) I decided to write a LogParser COM input format plugin for TFS to perform all the above.

Initially I thought I could provide the tool using the direct TFS web services, however after reading online documentation I decided against this approach, mostly because Microsoft haven't got any documentation, nor will guarentee they won't break it between releases. I additionally considered simply parsing the output from TF.EXE, however thought this was inelegant as a solution and prone to error and additionally wouldn’t provide Work Item querying I was looking for.

I therefore decided to look at TFS APIs available in VB.Net. The available functionality is extensive and blogs like James Manning make it much easier to navigate various examples of using the APIs.

Below are details on the solution.

Features

Freeware with VB.NET 2005 source code included
Can query Team Foundation Server (TFS) work items, check-ins (ChangeSets), check-outs (Pending ChangeSets), and Shelvesets. This has been tested on TFS 2005 - it may work on earlier or later versions.
For work items supports either querying using existing Stored Queries, or using direct work item query language (WIQL). The queries can be run across a specific project, or multiple projects.
For check-ins, check-outs and shelveset queries it reports the file details and associated work items. In addition supports a single or multiple project paths in the same query and the recursion level.
Work items and check-ins (changesets) support iCheckPoint functionality to enable you to retrieve only the latest changes since the last query. Works on recording the time (UTC) and then querying for item greater than that time.
The Work items query can retrieve specified fields (either in Stored Query, or WIQL) or return all fields available for that work item.
Can query multiple different TFS projects, or even different TFS servers and combine the results. Internally used Dataset.Merge functionality.
Work item query allows for LEFT JOIN-ing linked Work Items. You can specific the fields you want returned in the LEFT JOIN.
To provide extra querying features Implements a number of custom columns (as per standard LogParser).
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:

Assume that TeamFoundation*.dll (Team Explorer or Visual Studio) is installed in the GAC and working on your system. Stand-alone Team Explorer install instructions. Both Team Explorer and Visual Studio install the dlls into the GAC.

1. Copy the LogParser.TFSQuery folder to a location on your harddrive

2. Run the command .\LogParser.TFSQuery\InstalldotNETasCOM.bat.

3. Run your Log Parser query.

e.g. LogParser.exe -i:COM "select * From 'http://tfsserver:8080/TestProject/My Work Items'" -iProgId:Gluegood.LogParser.TFSQuery -o:DataGrid

A number of samples are available in the downloaded file in SampleQuery.bat

Syntax

LogParser.exe -i:COM "select * From '{Path1}','{Path2}',..." -iProgId:Gluegood.LogParser.TFSQuery -iComParams:"iAllFields={TrueFalse},iCheckPoint={Path},iWIQL={WIQL query},iQueryCheckOuts={TrueFalse},iQueryShelvesets={TrueFalse}, iRecursive={TrueFalse},iWorkItemLinksQuery={TrueWIQL queryField list}"

{Pathx} - a formatted path that adheres to the following standard.
- Work items – {server:port}/{project}/{query} e.g. ‘http://tfsserver:8080/ProjectTest/All Work Items’ or ‘tfsserver/*/All Work Items’. Expect the 3 slashes as delimiters even when item isn’t required..
- Check-ins, Check-outs, shelvesets - {server:port}/{path} e.g. ‘http://tfsserver:8080/$/ProjectTest/Release’ or ‘tfsserver/$/’
Supports multiple Paths as long as they are deliminated with a comma (,).
iAllFields - Ignore the columns defined in the query (or WIQL) and retrieve all columns. Default = False
iCheckPoint – Path to a checkpoint file. Default is no checkpoint.
iWIQL – WIQL query. Overrides any stored queries specified. Defaults to no iWIQL Hint: If using from command line any commas must be escaped with \u002c.
iQueryCheckOuts - Query the path specified looking for CheckOuts. If iQueryCheckOuts and iQueryShelvesets are not provided, or both false, then assume querying for Check-ins. Default is false.
iQueryShelvesets - Query the path specified looking for Shelvesets. If iQueryCheckOuts and iQueryShelvesets are not provided, or both false, then assume querying for Check-ins. Default is false.
iRecursive – defines wether to recusively scan subfolders, or stay within the current folder/project defined. Default is true.
iWorkItemLinksQuery – Defines whether to LEFT JOIN all linked Work Items. Extra columns returned will be prefixed with ‘LogParserTFSRelated’. Setting to True will return Linked Work Item Id, Type, State, and Title. Can additionally define your own WIQL (with only SELECT and FROM clause) or a field list. Default is "" (not enabled).

Columns returned (Work items)
LogParserTFSServer – Server in which the query is being executed.
LogParserTFSProject – TFS Project which this query has been executed
LogParserTFSQuery – TFS Stored Query, or WIQL query
LogParserTFSWorkItemId – Work Item Id for this item
LogParserRecordNumber – Record number for this item in the query.
LogParserTFSRelated – If using iWorkItemLinksQuery Columns in the query or all Columns

Columns returned (Check-ins / changesets)
LogParserTFSServer – Server in which the query is being executed.
LogParserRecordNumber – Record number for this item in the query.
ChangeType – Type of change
User – User who checked in this item.
Date – Date that this check-in occurred.
Comment – Comment associated to this check-in
Path – File path (server side) of this check-in
WorkItemId – Associated Work Item Ids. Will display multiple seperated by a comma.
ChangeSetId – ChangeSet Id.
PolicyOverrideComment – Policy override comment if entered.
PolicyFailures – Number of policy failures

Columns returned (Check-outs / pendingsets)
LogParserTFSServer – Server in which the query is being executed.
LogParserRecordNumber – Record number for this item in the query.
ChangeType – Type of change
User – User who checked out this item.
Date – Date that this check-out occurred.
Path – File path (server side) of this check-out
PendingSetId – PendingSet Id.
Computer – Computer in which this check-out was conducted against.

Columns returned (Shelvesets)
LogParserTFSServer – Server in which the query is being executed.
LogParserRecordNumber – Record number for this item in the query.
ChangeType – Type of change
User – User who shelved this item.
Date – Date that this shelve occurred.
Path – File path (server side) of this shelve
PendingSetId – PendingSet Id.
Computer – Computer in which this Shelve occurred from.
Name – Name of this shelveset

Challenges

CheckPoint, WIQL and store.Query
When using iCheckPoint with Work Items I had to modifying the supplied stored query or WIQL provided with an additional clause returning the latest items based on System.ChangeDate. I ran into a problem using {string}.replace to look for the existence of existing WHERE clause as it’s case sensitive and I didn’t think I could rely on the case, therefore I ended up using Regex.Replace thanks to this. In addition because the CheckPoint stores date and time I had to modify the way I executed the query from the standard store.Query approach listed in many examples to a more obscure method using the Query object and setting the dayPrecision flag.

Performance and WorkItems
There is a lot of documentation stating that performance is degraded if you query every work item field beyond those returned in the WIQL query (as TFS needs to round trip to the server) , therefore I ensure that when returning fields (without the iAllFields setting) I only reference those fields specified. In addition I bumped up the PageSize to 200.

Changeset, Pendingset and Shelveset and querying
There seems to be some complexity around this (using APIs QueryPendingSets, QueryShelvedChanges, and QueryHistory) and I conducted some research and found this entry helpful in providing directions. In addition I leaned heavily on James Manning’s site and numerous examples provided e.g. Shelvesets

Querying Linked WorkItems and Performance
The easy way would have been to return the Linked Work Item details on each Work Item that had a link. However this performs terribly (as multiple trips are made for every Work Item link). Another better way I found was to

Add the RelatedLinkCount field to the initial WIQL query (to determine if there were any links),
Then loop through the WorkItem.Links collection (performance hit on this one and doesn't seem to be a way around this) determining if any are WorkItems and then storing any found WorkItem link ids into a BatchReadParameter.
Then after cycling all Work Items I then execute a new WIQL store.query using the BatchReadParameterCollection (a list of Work Item Ids).
Then I link the original Work Item dataset to the Linked Work Item dataset by moving through all the DataTable.rows. There is a few tricks around this code to support when a Work Item cannot be referenced due to permissions.

GACUTIL and REGASM
My current approach with LogParser COM Input addins was to always install them in the GAC as I mistakenly thought that LogParser when reading the COM components both needed the item registered and installed in the GAC. I realised with this project that I really didn’t want to have to get the entire TeamFoundation assemblies in the GAC and played around with the install and I made 2 discoveries.

a) the TeamFoundation assemblies appear to already be installed in the GAC (if you have Visual Studio Installed, or Team Explorer) and

b) that using REGASM with the /codebase (as per MSDN - The Codebase entry specifies the file path for an assembly that is not installed in the global assembly cache) means you don't need to install into the GAC.

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

** 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.

Freeware - LogParser.Email

2009-05-30T22:35:00.007+10:00

Monitoring logs is always very challenging. You can use very expensive software which has its own interfaces and nuances, or you can write your own (can be just as painful).

A few years ago I had the responsibily of monitoring a buggy 3rd party web application. I would often be awoken in the middle of the night to simply bounce the IIS service by overseas users. To enjoy a more peaceful nights sleep I created a tool that monitored IIS logs and when a particular string appeared I would know that the 3rd party software had crashed and thus issue a command to restart IIS. The tool worked and I got some rest.

I wish at the time I had known of LogParser and had written the tool I've created in this post which would have solved the problem very quickly and very cleanly.

LogParser.Email can take the output from any LogParser query and deliver a 'nicely' formatted SMTP e-mail. In addition the tool has a 'trigger' concept that can determine if the email should be sent. It also as an extension can run batch commands (e.g. restart IIS). Used in combination with a scheduling software you have an extremely powerful monitoring solution.

LogParser.exe -i:EVT "Select Top 10 * into STDOUT from System" -tpl:EmailTemplate.tpl -stats:off | LogParser.Email.exe

Suggested usage

A cheaper alternative to some of the monitoring features in Microsoft Operations Manager (MOM), or Heroix RoboMon, HP Openview etc
Used to 'test' if a specific rule being introduced into an enterprise monitoring product is valid
SharePoint Alert replacement (in conjunction with LogParser.WSS30) where an e-mail can list items, or changes across multiple lists, wikis or document libraries
Query databases and send alerts for specific detials (in conjunction with Logparser.OLEDB) which can e-mail row from multiple data sources. e.g. a poor man's security breach tool
Check for Low Disk Space, or Unexpected Server restarts
Notify when WLBS (monitor EventLog), or other Load Balancing product is down (monitor logs)
E-mail daily a combined list of file versions running on particular servers (using -i:FS)
Monitor IIS performance daily, weekly, monthly, or the health of the server (using -:IISW3C)
Check anything you have a log file for which you need to monitor.

Solution overview
1. Create a LogParser query which returns the data in exactly the way you'd want in presented in an e-mail. e.g. formatting, column positions, column names etc.

2. Create a SharePoint Template file that matches the E-mail Template format. e.g., Create a LogParser Template File Named C:\Template.tpl. The Template should follow the format in the samples provided (simple one listed below). Add more or less FieldName_X as per the number of columns in the LogParser query. Also update the EmailAddress and SMTPServer address.

<LPHEADER>
<Email>
<Trigger Operator=">" Value="0" />
<Actions>
<Action Type="EMAIL" EmailAddress="destination@destination.com" SMTPServer="mail"/>
</Actions>
<Rows>
</LPHEADER>
<LPBODY>
<Row>
<Field Name="%FIELDNAME_1%">%FIELD_1%</Field>
<Field Name="%FIELDNAME_2%">%FIELD_2%</Field>
<Field Name="%FIELDNAME_x%">%FIELD_x%</Field>
</Row>
</LPBODY>
<LPFOOTER>
</Rows>
</Email>
</LPFOOTER>

3. Run the LogParser query ensuring
a) that the INTO STDOUT clause is used
b) that -stats:off is used to ensure that only the XML is generated to STDOUT
c) that your LogParser results escape any XML characters. Use the REPLACE_STR function in LogParser e.g. escape < > " ' &
d) that you pipe the result to LogParser.Email.exe

LogParser.exe -i:EVT "Select Top 10 * into STDOUT from System" -tpl:EmailTemplate.tpl -stats:off | LogParser.Email.exe

Features of LogParser.Email.exe

Freeware with VB.NET 2005 source code included
LogParser.Email can take the output from any LogParser query and deliver a 'nicely' formatted HTML SMTP e-mail. Can deliver to multiple recipients in a single e-mail, or seperate e-mails via multiple EMAIL 'Actions'
Used the LogParser template (-tpl) flag and a specially crafted (not too complex) Template file to pass results to the tool.
The tool displays the LogParser results in a grid within the e-mail. Using LogParser native facilities you can name and order the columns and format the row results (e.g. date/time formats)
The tool works on the concept of 'Triggers' which allow you to define when to send the e-mail based on a threshold being e.g. only send an e-mail if more than 50 LogParser rows are returned (Rows>50 then send e-mail)
Can specify the SMTPServer, e-mail address, e-mail source address, e-mail subject, e-mail notes, e-mail footer text, and choose the colours (columns headings, and even and odd rows)
Using a special prefix 'NOHTMLESCAPE' on any results you can espace the HTML formatting and created HTML tags within the e-mails. e.g. Hyperlinks
The tool can execute a batch command (or multiple via COMMAND 'Actions') based on the Trigger
Logs any errors to console, EventLog.Application, and a AppPath\Trace folder
Can be used without LogParser by piping contents of an XML file (formatted as directed) to the utility.

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

Download VB.NET source is included in download.

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

To install:
1. Copy the LogParser.Email.exe to C:\Program Files\Log Parser 2.2\

2. Setup the Template file required. Ensure you set the fields names equal to the Sharepoint names in your Logparser query and the number also matches the FIELD_X values in the Template file.

3. Run your Log Parser query.

*************************************************
Syntax

{STDIN Sharepoint Batch XML} LogParser.Email.exe {Optional Debug Template}"
e.g. TYPE | c:\Email LogParser.Email.exe True

*************************************************
XML Attribues

Below are details of the XML attributes within the Template file

Trigger
Operator - This is the operator that will be used to test the value against the returned Rows from LogParser. e.g. > > or =. It must be escaped so > or < e.g. >0 will mean that if the Logparser query returns any rows the trigger will be fired.
Value - This is the value which the returned rows and operator are tested against.

Action
Type- Mandatory. EMAIL or COMMAND.
EmailAddress - Mandatory. SMTP destination e-mail address.
SMTPServer - Mandatory. SMTP Server name.
EmailText - Optional. Text that you'd like to appear above the results. Usually used to provide some guideance on what the result contains or what should be done to rectify.
EmailTextFullControl - Optional. Allows to override the standard header text with your own text (with NOHTMLESCAPE you can markup the text.
EmailSourceAddress - Optional. Source e-mail address.
EmailSubject - Optional. Email subject
EmailFooter - Optional. Text you'd like displayed at the bottom of the e-mail.
EmailColumnColour - Optional. The HTML colour used for the column heading.
EmailRowColour - Optional. The HTML colour used for the even rows.
EmailRowAlternateColour - Optional. The HTML colour used for the odd rows.
Command - Mandatory. Used for COMMAND type only and the full path to the command that needs to be executed.

Row
Field name attribute - Name of the field
Field node value - Value for the field in this row. Values with NOHTMLESCAPE will display without their characters being escaped. e.g. hyperlinks.

*************************************************
** 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.

Bitten by the cloud

2009-04-01T22:46:00.001+11:00

Software as a service, cloud computing, SOA etc or whatever the buzz word is. They all rely on trust. Trust that we won't do anything naughty with with their service, and our trust that the service is reliable and that you get plenty of warning of closure.

I've been happily using Openomy.com for about 2 years after Omnidrive died. At least with Omnidrive it was around for a few months before it disappeared, while Openomy simplied died on the 24th March after 10 days notice from this site.

So once again I've moved content to a new provider - Box.net.

P.S. Some content will be unavailable temporarily while I recover it from various locations.

Freeware - WSS30ListUpload (Sharepoint)

2008-11-13T22:18:00.013+11:00

I recently published a solution to query Sharepoint lists/document libraries using LogParser. I then thought about how you could output LogParser results directly to Sharepoint (WSS 3.0) lists.

Some advantages that I can see of using Sharepoint as a Log Parser output is that you have a more managed location for results and additionally you could utilise the alert facilities. e.g. provide a monthly IIS statistics report that is automatically distributed to required people.

After some thinking I decided upon a solution involving using the LogParser output 'Template' format (-o:TPL) to generate the Sharepoint batch XML. Outputing the resultant XML to STDOUT (in the LogParser query) and then redirecting (using pipe i.e, ) to a small tool named WSS30ListUpload (which I wrote) which uploads to a SharePoint site list or document library. This allows for a solution utilising features within Log Parser and therefore provides a very clean solution with a great amount of flexibility.

Note: The WSS30ListUpload tool can be used without Log Parser. In essence using this tool you have a command line driven Sharepoint list uploading tool. e.g.

type c:\listdata.xml | WSS30ListUpload.exe "http://mysite/site/Lists/MyList/AllItems.aspx"

Solution overview

1. Create a SharePoint Template file that matches the SharePoint Batch XML format. e.g., Create a LogParser Template File Named C:\Template.tpl. The Template should follow the below example. You can download a working XML in below. Add more FieldName_X as per the number of columns uploaded into SharePoint.

<LPHEADER><Batch OnError="Continue" PreCalc="TRUE" ListVersion="0" ViewName=""> </LPHEADER>
<LPBODY> <Method ID="%FIELD_1%" Cmd="New">
<Field Name="%FIELDNAME_2%">%FIELD_2%</Field>
<Field Name="%FIELDNAME_3%">%FIELD_3%</Field>
<Field Name="%FIELDNAME_4%">%FIELD_4%</Field>
<Field Name="%FIELDNAME_5%">%FIELD_5%</Field>
<Field Name="%FIELDNAME_6%">%FIELD_6%</Field>
<Field Name="%FIELDNAME_7%">%FIELD_7%</Field>
<Field Name="%FIELDNAME_8%">%FIELD_8%</Field>
</Method>
</LPBODY>
<LPFOOTER></Batch>
</LPFOOTER>

2. Place WSS30ListUpload.exe in C:\Program Files\LogParser 2.2\

3. Run the LogParser query ensuring
a) that the first column is a unique number per row. e.g. RecordNumber
b) that a column [ID] is generated with the text 'New'
c) that the column names match either the SharePoint 'DisplayName' (the title in SharePoint), or the underlying Name (e.g. 'This_x0020_Field' vs 'This Field') within the list.
d) that the INTO STDOUT clause is used.
e) that -stats:off is used to ensure that only the XML is generated to STDOUT.
f) that you pipe the result to WSS30ListUpload.exe which takes a single command line parameter of the SharePoint List URL.

e.g. LogParser.exe -i:EVT "Select Top 10 RecordNumber,'New' as [ID],EventLog,RecordNumber,TimeGenerated,TimeWritten,EventID,EventType into STDOUT from System" -tpl:c:\SharePointTemplate.tpl -stats:off | WSS30ListUpload.exe "http://mysite/site/Lists/Test/AllItems.aspx"

Note: The result from the execution is the Sharepoint Lists.asmx -> UpdateListItems XML response. Any errors resulting from the upload will be in this file. If you want to capture it to file redirect to a file. e.g. add > C:\Output.xml at the end of the example above.

Features of WSS30ListUpload

Freeware with VB.NET 2005 source code included
Can execute any Sharepoint Batch XML file against WSS 3.0 list, wiki, or document library content. Have only tested on WSS 3.0 - it may work on earlier or later versions. Uses the web service Lists.asmx -> UpdateListItems.
Single command line parameter is SharePoint URL to List/Document Library. Internally resolves Site and List Name based on the URL.
STDIN supports Batch XML
The STDOUT from WSS30ListUpload is the result of Sharepoint Lists.asmx -> UpdateListItems in XML format. This includes any errors.
Supports both Name and DisplayName for the Sharepoint column names. To do this it automatically translates Sharepoint column DisplayNames into Name (e.g 'This Field' becomes 'This_x0020_Field'). In addition if duplicate DisplayNames are found then will abort the upload.
Logs any errors to console and to EventLog.Application

*************************************************
Download VB.NET source is included in download.
*************************************************

To install:

1. Copy the WSS30ListUpload.exe to C:\Program Files\Log Parser 2.2\

2. Setup the Template file required. Ensure you set the fields names equal to the Sharepoint names in your Logparser query and the number also matches the FIELD_X values in the Template file.

3. Run your Log Parser query.

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

Syntax

{STDIN Sharepoint Batch XML} WSS30ListUpload.exe {SharePointListURL}"

e.g. TYPE c:\batch.xml | WSS30ListUpload.exe http://mysite/site/lists/MyList/AllItems.aspx

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

** Legal **

Freeware - Logparser.WSS30 (SharePoint)

2008-11-08T16:27:00.008+11:00

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.

Freeware - Logparser.ZIP

2008-09-11T22:03:00.007+10:00

My last project was an OLEDB Log Parser COM input format plugin. One of the comments was that ‘we’ (meaning I) should work on a solution to query within ZIP files. I read the comment at the time and thought the suggestion was very specific and technically very difficult. Several months have passed and I did some more research and found a few more comments on trying to do this.....

Ta-da! Introducing a Log Parser COM input format ZIP plugin that will allow you to execute any Log Parser input query (e.g., –i:CSV, -i:IISW3C, -:TSV) against a single, or multiple Zip, GZip, Tar and BZip2 files. * For convenience I’ll refer to this set of files as ZIP files.

Features

Freeware with VB.NET 2005 source code included
Can query Zip files (GZip, Tar and BZip2 files are supported via an extension written by joelangley). To reduce disk requirements the LogParser.ZIP plugin extracts each file inside the zip, runs the Log Parser query, then deletes the unpacked file and repeats for each file.
Supports multiple zip files including wildcards, so you can query in the same Log Parser query across not only multiple files within a single ZIP, but multiple ZIP files either using comma separated list, or wildcards. E.g. ‘Select * From ‘2009*.zip’,’ 200812.zip’”
Supports all Log Parser Input Queries and their associated parameters. Note: Because the zip files are unpacked and then deleted the iCheckPoint parameter doesn’t work as it thinks the files have completely changed.
Implements iCheckPoint to support only showing the latest added files to the ZIP (not too sure if this will always work). This is supported across multiple ZIP files.
Implements 2 custom columns (as per standard LogParser). They are LogParserZIPFilename, LogParserRecordNumber in addition to returned columns. This is to provide extra querying features.
Logs any errors to console and to EventLog.Application
Supports all Log Parser data types except TIMESTAMP_TYPE, see challenges below.

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

Download. VB.NET source is included in download.

*************************************************
Note: I've given the code a good test, however there may be unexpected bugs. As this code has overwrite and delete file features please ensure that you test the software.

To install:
1. Copy the LogParser.ZIP folder to a location on your harddrive
2. Run the command .\LogParser.ZIP\InstalldotNETasCOM.bat (need GACUTIL.EXE - part of the .NET FW 2.0 SDK - why?). This will install into the GAC the 3 components being Gluegood.LogParser.ZIP, Interop.MSUtil.dll (LogParser wrapper) and ICSharpCode.SharpZipLib.dll.
3. Change directory to the location where LogParser.ZIP can extract and then delete the unpacked zip files and directories. E.g. cd c:\test
4. Run your Log Parser query. e.g.

LogParser.exe -i:COM "select * From 'C:\Test\Test.zip'" -iProgId:Gluegood.LogParser.ZIP -iCOMParams:iQuery="Select * From *.txt",iInputParameters="-i:CSV -HeaderRow:Off" -o:DataGrid

Syntax

LogParser.exe -i:COM "select * From {ZIPFile(s)}'" -iProgId:Gluegood.LogParser.ZIP -iCOMParams:iQuery="{LogParserQuery}",iInputParameters="{LogParser InputParameters}" -o:DataGrid

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

Challenges
1. Calling Log Parser APIs within VB.NET
The LogParser.ZIP COM Plugin is a VB.NET program that is COM interop enabled (to allow for LogParser to call it). Additionally it calls the LogParser COM API to execute LogParser queries thus making the whole interfacing rather technically challenging.

a) GAC, assemblies and DLLs
To enable LogParser to call LogParser.ZIP COM input plugin you need to give it a strong name, register and then publish it to the Global Assembly Cache (GAC). Due to the LogParser.ZIP residing in the GAC you need to create a dotNet wrapper for the Log Parser COM APIs (LogParser.dll), give it a strong name, and then place it too into the GAC. After a number of trials I found that the TLBIMP program did just this :-

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\tlbimp "C:\Program Files\Log Parser 2.2\LogParser.dll" /out:Interop.MSUtil.dll /keyfile:"GluegoodStrongKey.snk"

I then needed to Register the dotNet wrapper (Interop.MSUtil.dll) using REGASM.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe .\Interop.MSUtil.dll

I then needed to place Interop.MSUtil.dll into the GAC

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil" /i .\Interop.MSUtil.dll

(The InstalldotNETasCOM.bat does all of the above for you)

I then added a reference to the Interop.MSUtil.dll within my VB.Net project and can now call the LogParser COM API.

b) Calling late bound a COM dll using VB.NET reflection
To enable the greatest flexibility with the solution I needed to enable users to specify all Input types. E.g. –i:CSV, -i:TSV etc. Therefore I needed to dynamically call the COM Input Context Class. In my research this seems only achievable in using Reflection. Unfortunately there isn’t a lot of documentation on COM interop reflection using the GAC (bit unique I’d suggest). I found 2 useful articles that helped provide guidance on what to do – I’d like the thank the authors for documenting this.

Effectively I late bound to the COM input class using code exampled in this site. One gotcha was the late bound GAC name I had to use for Interop.MSUtil.dll. Initially I used just Interop.MSUtil, however I found that the late bound function didn’t work, therefore I used the full GAC name for the assembly being

Interop.MSUtil, Version=1.0.0.0, Culture=neutral, PublicKeyToken=048dde0ba838787f, processorArchitecture=MSIL

I then had to set the Input Parameters and I used a function called CallByName, which allows you to set object properties by name, instead of early binding (very cool).

2. Deciding on ZIP API
There are 2 major free ZIP APIs available for dotNET developers. The jslib ZIP methods, or the #ZIPLib dotNET Library. I decided on the latter simply because it was open source like my solution, it had a smaller footprint (wasn’t an extra download) and the comments out in the community tended to favour it as a solution. Additionally it provided more than just ZIP extraction which I thought may be useful. For the sceptics it was really easy to implement within my code.

3. TIMESTAMP_TYPE datatype conversion issue
This remains unsolved, so if anyone has any ideas let me know. The LogParser.ZIP program extracts each value in the IRecordSet response and places into a DataTable. In my code I match the DataTable column data types to the LogParser column data types. When I then return to LogParser (via the GetFieldType method) the values I re-convert the DataTable column types to the appropriate LogParser FieldType. For some very strange reason the delivery of TIMESTAMP_TYPE on sample data I tried performed extremely poorly when compared to STRING_TYPE. E.g. 2 seconds compared to 5 minutes. I therefore decided not to convert TIMESTAMP_TYPE, but pass any TIMESTAMP columns back as STRING_TYPE.

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

** 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.

Freeware - Logparser.OLEDB

2008-04-23T20:51:00.009+10:00

Log Parser has a native extension which allows developers to write their own custom input formats and for users to query using the i:COM input format. I decided to write the OLEDB Log Parser COM input format plugin so I could extend the data sources which LogParser could query. By using OLEDB and LogParser you have an extremely powerful tool which can provide complex querying solutions.

Features

Using this DLL with LogParser you can query any OLEDB datasource which Log Parser doesn't support natively. e.g Excel, Access, DBF, MSSQL, Oracle, MS Project, FoxPro, AS/400, Index Server, HTML Tables, Text Files, and any other custom OLEDB providers. There are a number of sites with OLEDB Connection Strings
Provide a query for the base OLEDB datasource and a query for Log Parser. e.g. get the data you want back from the data source and let Log Parser provide its SQL querying features (e.g. min, max, avg, etc and numerous functions) to format the data correctly.
Supports multiple datasources, so you can query in the same LogParser query across multiple Excel spreadsheets, Access databases, SQL databases etc. Extremely useful to query multiple data sources to discover the location of data.
Implements iCheckPoint to support only showing the latest records. This is supported across multiple data sources in a single query.
Implements 2 custom columns (as per standard LogParser). They are LogParserConnectionString, LogParserRecordNumber in addition to returned columns.
Logs any errors to console and to EventLog.Application
VB.NET 2005 source code included
Note: Doesn't currently support conversion of OLEDB datatypes to LogParser datatypes - everything is provided to LogParser engine as as string.

Below are some of the challenges :-

1. Add new columns to a DataSet, placing in the right order and setting their value. The trick is to add the columns and then to reverse transverse through the records updating them.

'oDataSet.Tables(0).Columns.Add("LogParserRecordNumber", 'System.Type.GetType("System.String"))
'oDataSet.Tables(0).Columns("LogParserRecordNumber").SetOrdinal(1)
'Dim lIndex As Long = lRowCount - 1
'Do Until lIndex = -1
' Dim oRow As DataRow = oDataSet.Tables(0).Rows(lIndex)
' If (lIndex + 1) <= lCheckPointOffset Then ' 'Can delete this row from in-memory datatable
' oRow.Delete()
' oRow.AcceptChanges()
' Else
' ' Keep this row and add values to it.
' lRecordNumber = lRecordNumber + 1
' oRow.BeginEdit()
' oRow.Item("LogParserRecordNumber") = lRecordNumber
' oRow.EndEdit()
' End If
' lIndex = lIndex - 1
'Loop

2. Allowing Log Parser to escape quote (") in the command line request inside of the LogParser.exe query. The below query has quotes within quotes and to allow LogParser to accept this you must escape with slash (\). e.g. \"

LogParser.exe -i:COM "select * From 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\SOLVSAMP.XLS;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"'" -iProgId:Gluegood.LogParser.OLEDB -iCOMParams:iQuery="SELECT * FROM [Quick Tour$]" -o:DataGrid

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

Download. VB.NET source is included in download.

*************************************************
To install:
1. Copy the LogParser.OLEDB folder to a location on your harddrive
2. Run the command .\LogParser.OLEDB\InstalldotNETasCOM.bat (need GACUTIL.EXE - part of the .NET FW 2.0 SDK - why?)
3. Run your Log Parser query. e.g. LogParser.exe -i:COM "select * From 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;'" -iProgId:Gluegood.LogParser.OLEDB -iCOMParams:iQuery="SELECT * FROM [Employees]",iCheckPoint="C:\CheckPointTest.chk" -o:DataGrid

Syntax

LogParser.exe -i:COM "select * From '<oledbConnectionString>'" -iProgId:Gluegood.LogParser.OLEDB -iCOMParams:iQuery="<QueryString>", ,iCheckPoint="<CheckPointFile>"

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

** 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.

Freeware - Logparser.VSSJournal

2008-03-16T16:29:00.004+11:00

Now that I understand the Log Parser COM input format plugin I have migrated the VSSMonitor code into a Log Parser input format plugin.

Features :-

Read VSS 6.0 Journal files via Log Parser queries
Supports all fields and converts dates
Supports iCheckPoint feature -custom implementation, but basically the same as the implementation that Log Parser natively supports
Logs any errors to console and to EventLog.Application
VB.NET source code included
Supports only a single VSS Journal file in a single call. e.g. * , ; are not supported.

Below are some of the challenges :-

1. Implementing iCheckPoint - Because I used StreamReader there was a whole lot of hassle (i.e. research) in trying to record the position that I had streamed in the file. The biggest problem is StreamReader position can be different from the stated StreamReader.BaseStream.Position, there are lots of articles out there about this, however in my specific implementation I believe I'm safe. I first mark the StreamReader.BaseStream.Position from the last recorded file position before I even do a read. Then I ReadLine the file contents and when I get to the end I record the StreamReader.BaseStream.Position. The StreamReader reads ahead and stores in a buffer, however at the end of the file there is nothing else to read so the StreamReader.BaseStream.Position is at the end.

2. Storing the iCheckPoint values - I wanted to create a flexible solution to store the iCheckPoint values. Now I could simply store only the offset of a single file, however I wanted to be as flexible as the Log Parser team have made theirs so I wanted to allow for multiple offsets based on the specified FROM fields. I decided to make a ArrayList with a Class per each file parsed using serialization. The standard Microsoft System.Xml.Serialization.XmlSerializer class doesn't support complex structures, so I couldn't use this. I found on the CodeProject site a piece of code that supports serializing and de-serializing complex class structures which I had added to the code.

Download. VB.NET source is included in download.

*************************************************
To install:
1. Copy the LogParser.VSSJournal folder to a location on your harddrive
2. Run the command .\LogParser.VSSJournal\InstalldotNETasCOM.bat (need GACUTIL.EXE - part of the .NET FW 2.0 SDK - why?)
3. Run your Log Parser query.

e.g.

LogParser.exe -i:COM "select * From 'journal.txt'" -iProgId:Gluegood.LogParser.VSSJournal -iCOMParams:iCheckPoint=VSSCheckpoint.chk

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

** 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.

Freeware - LogParser.SampleQFE

2008-03-09T21:01:00.030+11:00

I decided to have a go at writing a Log Parser COM Input Format Plugin using VB.NET. The COM Input Format Plugins allow you to write your own data custom parser of a data source. Note: This is different from the Log Parser COM Interface where you can call Log Parser methods from code.

I decided to look for some VB.NET examples, however couldn't see any out there so I thought that I'd convert the VBScript SampleQFE (which is available in the .\Log Parser 2.2\Samples\COM\QFE folder).

Couple of points I found in my travels.
1. Log Parser needs components that expose COM interfaces, therefore if you are using VB.NET you need to do the following :-
a. Mark the Class you build as a 'COM Class' and 'COM visible'
b. If you want it having a sensible COM name then use the code below :-

<System.Runtime.InteropServices.ProgId("Gluegood.LogParser.SampleQFE") _
, Microsoft.VisualBasic.ComClass()> _
Public Class SampleQFE

c. Bit of a gotcha for young players is that the AssemblyInfo.vb file of your project you must also have this line :-

<Assembly: ComVisible(True)>

See this artcile for more information on COM in .NET
Calling .NET From COM

2. Additionally you must install it in the Global Assembly Cache (GAC). To install into the GAC you need to provide it with a Strong Key Name.
a. Run this command to generate a new Strong Key Name

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn" -k .\your folder\SampleQFE.snk

b. Add the SampleQFE.snk (which contains the Strong Key Name) to you project using the following line in code :-

<Assembly: AssemblyDelaySign(False)>
<Assembly: AssemblyKeyFile("C:\LogParser.SampleQFE\Code\SampleQFE\My Project\SampleQFE.snk")>

See this article for more info on this.

3. Compile
4. Register the COM interfaces into the Registry using REGASM.EXE. e.g. the following command :-
"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe" "C:\LogParser.SampleQFE\Code\SampleQFE\bin\Release\SampleQFE.dll"

5. Install the Compiled DLL into the GAC using GACUTIL so it can be universally accessed. e.g.

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil" /i .\Code\SampleQFE\bin\Release\SampleQFE.dll

Hopefully this is enough to get someone else out there through the intial hurdles. Incidentally I've made the SampleQFE available here for download.

Now to call from Log Parser run the following command :-

"C:\Program Files\Log Parser 2.2\LogParser.exe" "Select * From ." -i:COM -o:DataGrid -iProgId:Gluegood.LogParser.SampleQFE -iCOMParams:ExtendedFields&eq;ON -e:10

Download. VB.NET source is included in download.

Advice - LogParser

2008-01-31T23:16:00.001+11:00

I want to start this post by stating that I'm a huge fan of the Microsoft LogParser tool. This tool is so useful and if you are dealing with log files, events, and/or file systems it is an absolute must have in your toolkit.

A great extension to this tool is Visual LogParser. If you like SQL Enterprise Manager (or SQL Query Analyser) then this interface to LogParser is extremely helpful.

Below are some of my more advanced LogParser techniques that I've collected over time

IIS Performance - The below query is perfect if you are trying to understand performance at a subfolder level. It will provide the Server, the URL stem, total pages and average time based on only pages that were successful.

set sDate=071107
cd "C:\Program Files\Log Parser 2.2"
LogParser.exe -i:IISW3C "select EXTRACT_TOKEN(LogFilename,2,'\\') as Server, TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')) AS URL, count(*) As PageTotal, avg(time-taken) as AvgTime From '\\server1\IISLogs\W3SVC1\ex%sDate%*.log', '\\server2\IISLogs\W3SVC1\ex%sDate%*.log' where sc-status = '200' group by EXTRACT_TOKEN(LogFilename,2,'\\'), TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')) order by TO_UPPERCASE(EXTRACT_PREFIX( cs-uri-stem, 1, '/')), EXTRACT_TOKEN(LogFilename,2,'\\')" -o:DATAGRID

IIS User Usage - Using the power of PSEXEC (psexec - I love this tool in that you can execute commands on remote servers without needing to copy and exe to that server) you can create a hosting server that schedules LogParser queries and unloads them to remote servers (the remote servers must have LogParser installed). Below are 2 batch files that allow you to run daily a LogParser query on a remote server and returning the results back to the scheduling server. This is perfect if you are running queries across the WAN.

Additionally the below batch file uses a Date/Time routine - depending on your IIS Log Files it can be better to query a specific log file using a date (e.g. ex080120.log) than using the LogParser CheckPoint features.

cd "C:\Program Files\BatchFiles"
c:
psexec \\server1 -c -u server1\username -p password
"C:\Program Files\BatchFiles\IISUsage\IISUsageDailyUsers.bat"

Below is the IISUsageDailyUsers.bat file

REM Set the Date / Time elements
For /f "tokens=1-7 delims=:/-, " %%i in ('echo exit^cmd /q /k"prompt $D $T"') do (
For /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^date')do (
set %%a=%%i
set %%b=%%j
set %%c=%%k
set hh=%%l
set min=%%m
set ss=%%n
)
)
if %yy%==2008 Set %YearStr=08
if %yy%==2009 Set %YearStr=09

cd "C:\Program Files\Log Parser 2.2"
C:
REM Page Statistics
LogParser.exe -i:IISW3C "select cs-username as Username, count(*) as ActivityCount INTO '\\ServerScheduling\IISUsage\Output%YearStr%%mm%%dd%.csv' from '\\server1\IISLogs\W3SVC1\ex%YearStr%%mm%%dd%*.log','\\server2\IISLogs\W3SVC1\ex%YearStr%%mm%%dd%*.log' where cs-username IS NOT NULL and c-ip like '10.1.0.%%' group by cs-username order by count(*) desc" -o:CSV

WLBS Status - Want to know whether both servers are still in the farm? Use this tool to provide NLB Status - done in 2 parts. First a batch file to retrieve the status into a log file, next part is a LogParser query to check.

Below is NLBStatus.cmd batch file

date /t
time /t
@wlbs query SERVERCLST /passw

It is called daily with a command like :-

call "C:\Program Files\BatchFiles\NLBStatus.cmd" >> "C:\Program Files\BatchFiles\Logs\NLB\NLBStatuslog_%yy%%mm%.log"

LogParser query :-

LogParser.exe -i:TEXTLINE "Select text FROM 'c:\Program Files\BatchFiles\Logs\NLB\*.log' WHERE Text LIKE '%converged%'" -o:DATAGRID -iCheckPoint:"C:\NLBCheckpoint.chk"

Get a response with less than the number of servers in the cluster then you know you have something wrong.

Fixed length files - In something like Visual LogParser you can view nicely (in Excel style) Fixed Length formatted files. Apply a query against TEXTLINE using SUBSTR to format the fields :-

SELECT SUBSTR(Text,1,2) As [Id], SUBSTR(Text,3,10) As [Name] FROM C:\FixedLengthFile.txt

The [..] fieldname syntax allows you to have space in the column names.

LogParser and Unicode files - Something interesting I got caught with is how LogParser deals with CheckPoint files when parsing Unicode files. The solution around this is if using Unicode files to specify the iCodePage=-1 paramater. e.g.

"c:\Program Files\Log Parser 2.2\LogParser.exe" "select EXTRACT_FILENAME(LogFileName), text FROM 'UnicodeFile.log' where text like '%%FINDSOMETHING%%'" -i:TextLine -o:DataGrid -iCheckPoint:"C:\checkpoint.chk" -iCodePage=-1

Freeware - PerfCollector

2008-01-26T20:51:00.001+11:00

PerfCollector is a command line freeware tool (with .NET source) that can monitor performance counters and log values to a text file. It can use either WMI or standard Performance Counters. See a previous blog post on more information about why this tool was developed.

Freeware - with source included.
Command line driven - This allows the tool to be scheduled. The PerfCollector tool has no UI. All activity is driven by a config.xml file with errors and logs to text files. Additionally it has a Debug entry in the Config.xml which allows you to see the actions being performed, additionally any errors are dumped into a \Trace\ folder. Program terminates on any exceptions.
Logs performance counter values to text file - Allows the configuration of Performance Counters and then dumps the values of those counters (only if they have changed) to a log file that can then be parsed using LogParser.
Different monitor settings - Monitors performance counters via either WMI Events or standard Performance Counters.

Download. VB.NET source is included in download.

*************************************************
To install:
1. Copy the PerfCollector folder to a location on your harddrive
2. Configure an XML file. See CONFIG.XML for an example
3. Open a command prompt and type 'PerfCollector.exe '
4. Select 'E' (capital e) to stop.

Additionally to run as a Windows Service see the .\InstallAsAService\InstallAsAService.bat batch file which has a script to allow you to install as a service using the SRVANY and INSTSRV resource kit tools (which can be downloaded for free from Microsoft)
*************************************************

Config file structure

config
counters
counter
... many counter

Below are the attributes of Counters element

Timer - How often the tool should poll the performance counters, or how often WMI should check for changes. Lower values will impact CPU performance. Timer is in milliseconds. e.g. 5000
MonitorType - Either 'WMI' for WMI monitoring or 'PerfMon' for PerfMon style montioring.
Debug - Sets level of verbose logging. True for verbose, false for no debugging.

Below are the attributes of the Counter element

CategoryName - This is the PerfMon Category name of the Performance Counter.
CounterCountName - This is the Performance Counter that contains a increasing total counter. e.g. TotalExecutedCalls
CounterName - This is the Performance Counter that contains the value you are interested in. e.g. "Call Time Last"
CategoryWMIName - This is the WMI Performance Counter Name. Find this using steps in the previous post. e.g. "Win32_PerfRawData_CustomAppWindows_CustomAppWindows"
ThresholdMinimum - This is the number at which the Counter must pass before it is logged to a text file. e.g. "1500"

Advice - Performance Counters, WMI and VB.NET

2008-01-22T22:47:00.000+11:00

Freeware PerfCollector tool and code that I wrote allows both Performance Counter and WMI query style querying is available in another post.

Recently I was working on a project that needed to find which business methods were taking the longest. As required in all enterprise handbooks the project had developed a whole bunch of custom performance counters on the business code. It all works perfectly. Dumping out values for the number of times a business method has been called, dumping out the time the last call took, average times, recent average times etc.

Problem is that whenever I asked someone to help monitoring the business methods performance in a live environment they blindly waved at the performance counters and tell me to use them. How? Having performance counters in your code doesn’t mean anyone knows how to use them.

Fairly quickly I noticed that Windows PerfMon isn’t going to help. PerfMon logging allows you to choose the counter and then choose the instances (in our case instances are each of the business method names). Two major problems with this approach are a) PerfMon doesn’t dynamically add instances once you have setup logging, so in my case I’m stuffed as new business methods that didn’t exist when I first setup the counter log would never be logged and b) sampling every 1 second collects a huge amount of data and I don’t really want to parse files that have the same performance counter details repeated even though the values are changing – nor the room to store all this data.

Ideally what I’m looking for is IIS style log files for long running business calls. 1 line per long running business method identifying; method name, time and the length of call. I really wanted to catch long running business code and know which method to attack.

To solve this I knew I’d need to write code (couldn’t find much on the Internet). I looked at using VB.NET to access the System.Diagnostics.PerformanceCounter objects. I wrote code to log out the value of counters that changed (that met a threshold I set) and to re-enumerate all the instances to look for new counters. This gave me a trace on the business methods. Logging out performance counters in Visual Baisc dotNet looks like :-

Dim myCounter As System.Diagnostics.PerformanceCounter = New System.Diagnostics.PerformanceCounter
myCounter.CategoryName = “Process”
myCounter.InstanceName = “lsass”
myCounter.CounterName = “% User Time”
Dim lValue As Long = CLng(myCounter.RawValue)

I then realised that as the number of instances (business methods in my case) increased then the load on the server will increase (from my tool enumerating), tool thus defeating its purpose of finding long running queries. Maybe there is a simple way to capture performance counters that are changing? I remembered that WMI exposed performance counters. With further research I conducted I found that WMI can provide callbacks to code when a query you register in WMI finds a record(s).

WMI must be one of the most complex pieces of Microsoft technology I’ve worked with. E.g. root\cimv2 – huh? Why cim? why v2? Then if you want to look at instances you need to run wbemtest. Not wmitest? I’d love to write a dummies guide to WMI, but I suspect it just wouldn’t fly as fairly quickly it would turn into complex reference material. Below is a brain dump of what I’ve found in my 3 days of research – starting point only and I’m sure I’ve made some silly assumptions, but if it can help any of you let me know.

What can I do with WMI - WMI is available on newer Microsoft operating systems (W2K, WXP, W2K3 etc) and it allows you to a) interrogate a system configuration e.g. Disk, Processor, Memory etc b) interrogate all performance counters c) files on a file system and d) has differencing facilities so you can know when something is added, modified or deleted (and I’m sure many more if you can delve its depths)

How can I look at WMI stuff, specifically Performance Counters – Simple, follow these 9 simple steps ;-). Goto Start->Run and then type wbemtest. Next select Connect… and type in the top textbox (un-labled) root\cimv2. Select Connect. Now select Enum Classes… type win32_perfrawdata then select Ok. Now select the specific object (aka Performance Object), we’ll select Win32_PerfRawData_PerfProc_Process. From here you have 2 choices.
a) If you want to look at counters then in the properties list (the 2nd listbox) scroll down to the actual counters.
b) Or to view a value of a counter select Instances to view instances and then double click on an instance to view what counters are available and then select the counter to view the value.

My custom performance counter doesn’t exist – I’m sure there is a more technical reason, but use Start->Run and then type wmiadap.exe /f. Reboot and hopefully all is good. Reboot is definitely required as before a reboot you may be able to see the performance counters in wbemtest, but you won’t get instances created and thus no values.

How can I read performance counters in code – Few ways, but for my purpose I want to know when a counter changes. Firstly you need a decent WMI query like the one below.

SELECT
*
FROM __InstanceModificationEvent
WITHIN 1
WHERE
TargetInstance ISA 'Win32_PerfRawData_CustomCounter_CustomCounter'
AND TargetInstance.Count > PreviousInstance.Count
AND NOT TargetInstance.Name = '_Total'
AND TargetInstance.LastTime > 1500

The __InstanceModificationEvent part of this query is a special in-memory table that stores changes from all WMI events. The Performance Counters luckily are part of this differencing. The WHERE clause allow you to narrow down exactly what change you want to know about. The WITHIN clause is how often sampling occurs (1 second in this case). So what happens if 2 or more ‘changes’ happen in the WITHIN time. Well you get only 1 – I suspect the last one. Either poll more frequently (e.g 0.5. The lower this number the more stress you place on your PC), or ask Microsoft to support ‘GROUP WITHIN’ and aggregate Events – this isn’t supported. You also need to use code like this in VB.NET

Dim wqlQuery As String = GenerateWMIQuery()
' Setup WMI Query and Watcher
Dim query As System.Management.WqlEventQuery = New System.Management.WqlEventQuery(wqlQuery)
Dim mWatcher As System.Management.ManagementEventWatcher = New System.Management.ManagementEventWatcher
mWatcher.Query = query

' Set up a listener for events
AddHandler mWatcher.EventArrived, AddressOf WMIEventRaised
' Start listening
mWatcher.Start()
Do
' Loop every 10 seconds.
System.Threading.Thread.Sleep(10000)
Loop

Then catch via

Public Shared Sub WMIEventRaised(ByVal sender As Object, ByVal e As System.Management.EventArrivedEventArgs)

Dim ev As System.Management.ManagementBaseObject = e.NewEvent
Dim lValue As Long = CType(ev("TargetInstance"), System.Management.ManagementBaseObject)("PercentUserTime")
Dim sInstance As String = CType(ev("TargetInstance"), System.Management.ManagementBaseObject)("Name")

End Sub

How often should I sample and what is the effect on performance – No idea. Seriously depends on your hardware. My suggestion is make the WMI query work for you. Ensure that you are only querying for exactly what you need, then you should be able to query every 5 seconds to pick up the event you need. Sure you might miss something, but if performance is so bad you’ll catch it again. I’m querying every 5 seconds and the WMI performance looks fine as does the .NET code.

PerfCollector tool and code that I wrote allows both Performance Counter and WMI query style querying.

Freeware - SharePointUpload

2007-11-18T16:16:00.001+11:00

SharePoint Upload is a command line freeware tool (with .NET source) that can extract e-mails from PST, Public Folders, and Exchange Mailboxes and place on a file system in MSG format (using Redemption). Additionally it can extract Visual Source Safe (VSS) documents, SharePoint Lists (extracted to HTML) and QualityCentre Test Cases (Extracted to HTML), The extracted files are compatible to SharePoint naming conventions. Used with SharePoint this tool can create a SDLC Search tool.

Note: Although the tool was designed for SharePoint's naming restrictions, the tool can be used to extract all the above to a normal folder (e.g. C:\Temp).

Windows Sharepoint Server (WSS) v3.0 has a feature in that you can copy files directly to it using standard UNC paths. e.g. if your SharePoint document library path is http://sharepoint.mydomain/SDLCSearch/ documents/RequirementDocuments/AllItems.aspx then you can upload documents directly by copying them into \\sharepoint.mydomain\SDLCSearch\ documents\RequirementDocuments. To do this you must have the 'WebClient' service started from the PC which you are initiating the request.

Below are some examples of SDLC data you want to index and how this could be copied to a Sharepoint site for indexing.

Visual Source Safe - Using VSS automation you can conduct a 'GetLatest' direct into the WSS Document Library UNC path. An advantage of this technique is that VSS only copies over files that have changed.
E-mails - Using the Redemption Data Objects (RDO) you can extract e-mails from PST, Public Folders and Exchange Mailboxes into MSG, RTF or HTML format, these can be copied directly into the WSS Document Library.
Quality Centre - Test Cases can be extracted as a single HTML document via a report, however for indexing purposes it is much more useful to have each test case as its own document. Using HTML parsing over the single Test Case HTML extract you can split the single HTML report into each Test Case.
Windows Sharepoint Lists /Wikis - Lists and Wikis on other sites that need to be included in indexing need to be pulled into the SDLC Searching site. Using WSS List Web Services you can run a query and extract each list item into seperate documents HTML documents.

The SharepointUpload tool that is available from Gluegood can effectively do all of the above and has the following features :-

Freeware - with source included.
Command line driven - This allows the tool to be scheduled. The SharepointUpload tool has no UI. All activity is driven by a config.xml file with errors and logs to text files. Additionall has a Debug entry in the Config.xml which allows you to see the actions being performed, additionally any errors are dumped into a \Trace\ folder. Program terminates on any exceptions.
Honours WSS invalid file and path name conventions - See here for a more detailed list. Anything it doesn't like it replaces with '_' underscore, or trims to required length.
Supports full reloads - sometimes you want to wipe all the content you previously uploaded. Setting a flag in the config.xml will do this by running the command cmd /c del /q /f /s "<SharepointUploadPath>"
Has a 'High Water Mark' concept - so that if you have already extracted a file it doesn't re-extract. Used in EMAIL by only extracting those e-mails that have a 'SentOn' date greater than or equal to the 'High Water Mark' date. Used in WSSList extract by only extracting those list items that have a 'Modified By' date greater than or equal to the 'High Water Mark' date. This is reset after a successful run in the Config.xml file(except if you manually set this date to '2000-Jan-01' - this allows for re-runs, but not removing old content using the Full Reload feature)
Has matching for e-mails - E-mail is a bit of a monster if extracting to file names. Not only do you have to worry about the filename, but you need to work out if you have a new e-mail, or an e-mail you have already extracted. e.g. in SharepointUpload it appends _1 for the 1st duplicate found and _2 for the 2nd duplicate, and so on. If you are re-running the program and it tries to write to the same filename, before it overwrites it looks at the Subject and the SentOn Date/Time to check if they are the same - If they are then overwrite existing, else create a new filename looking for the next one available.
Regular Expression filtering - Available on VSS (file and folder paths), and E-mails (folder restriction only)
Automatic subfolder generation - To ensure that folders don't become full of content and become unusable in WSS each extract will create subfolders (except VSS) based on hard-coded settings. e.g. E-mail will great a subfolder based on the month and year (e.g. YYYYMM), WSSLIST and QC will create based on every 10,000 Ids. e.g. folder for 10000 and a folder for 20000.

Download. VB.NET source is included in download.

*************************************************
To install:
1. Copy and Unzip the SharepointUpload.zip archive to a folder on your harddrive. Keep both \Trace and \Log folders.
2. For Visual Source Safe (VSS) functionality if VSS 6.0 isn’t installed on the PC that you’ll be running SharePointUpload then copy the Visual Source Safe automation dlls SSUS.dll and SSAPI.DLL to your VSSMonitor folder. Register SSAPI.DLL.
3. For e-mail (EMAIL) functionality install the Developer Version of Redemption (tested against v4.4) - download and install as per directions from the site. Note: Ensure that if Outlook isn't installed on the PC that you either install Outlook or install the ExchangeMapiCdo.exe which is a dependency of the Redemption component.
4. Configure the config.xml file which your VSS, EMAIL, WSSLIST, WSSWIKI, QC, and DB settings. Samples are provided in the config.xml file.
5. Launch SharepointUpload with the command line parameter of the config.xml file path. e.g. SharepointUpload.exe config.xml or use the SharepointUpload.bat file which logs the output to a file based on current month and year.
*************************************************

Config file overview
<config>
<patterns>
<pattern>settings</pattern>
... many patterns
</patterns>
</config>

Below are attributes common to all Patterns :-

Name - Name of the pattern. e.g. "All Documents from VSS"
SharepointDestination - Destination UNC path. The SharePoint path where documents should be uploaded.
HighWaterMark - Sets the last date that the Pattern was run and provides filtering for next run against EMAIL and WSSLIST types.
Debug - Sets level of verbose logging. True for verbose, false for totals only
PatternType - Type of the pattern. See below list and additional parameters per type.

Each Pattern is based on PatternType

VSS
Visual Source Safe extract using Get Latest automation.

SourcePath - location of VSS INI file
Username - Username for VSS
Password - Password for VSS
PathTopLevel - Location to commence searching in the tree.
RegExFilter - Restriction on what to extract based on folder and filename. Blank is all.

WSSLIST (Windows SharePoint Server List)
Extracts each list item to a simple HTML document. The name of the document is in the format of <ID> - <Title>.htm. Special OWS fields are excluded.

SourcePath - location of URL to list. e.g. http://sharepoint.mydomain/site/Lists/NewReq/MyView.aspx.
ListName - Name of the List. Use either name - "New Requirements" or GUID. See here to find the GUID of the list.
ViewGUID - GUID of the view to use. See here to find the View GUID.

WSSWIKI (Windows SharePoint Server List)
Extracts each Wiki item to a simple HTML document. The name of the document is in the format of <ID> - <Title>.htm. Special OWS fields are excluded.

SourcePath - location of URL to AllPages view of the Wiki. e.g. http://sharepoint.mydomain/site/Wiki/Forms/AllPages.aspx.
ListName - Name of the List. Use either name - "Wiki Pages" or GUID. See here to find the GUID of the list.
ViewGUID - GUID of the view to use. See here to find the View GUID.

EMAIL
Extracts each e-mail (PST, Public Folder, or Exchange Mailbox) as MSG. The name of the e-mail is in the format of <subject>_<version>.msg.

SourcePath - PST file path or Public Folder path (must start with \\Public Folders\..), or Exchange Mailbox Name
Username - ExchangeMailboxName, need a base name
RegExFilter - Matching of folder names. e.g. 2007 will extract for e-mails in the 2007 folder and subfolders. Blank is all.
ExchangeServer - Location of the ExchangeServer

QC
Extracts each QualityCentre test case as HTML. The name of the QC Test case is in the format of TestCase <TestCaseIdt> - <TestCaseTitle>.htm. Note: This should work for QualityCentre v9.0, however the extract isn't that intelligent and the HTML parsing may not work for your instance of QualityCentre.

SourcePath - Path to QualityCentre TestCase report.

DB
I'm not even going to attempt to document this, read the code and suit it to your own purpose. Uses DataSets, not DataReaders to improve locking contention.

** 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.

Advice - SDLC content search

2007-11-10T16:14:00.001+11:00

A freeware tool (with VB.NET source) that I wrote is available that can upload the artifacts discussed below. It is available in another post. e.g. It can upload e-mails from PST, Public Folders, and Exchange Mailboxes and place on a file system in MSG format (using Redemption). Additionally it can extract Visual Source Safe (VSS) documents, SharePoint Lists and Wikis (extracted to HTML) and QualityCentre Test Cases (Extracted to HTML), The extracted files are compatible to SharePoint naming conventions.

I've found that a wide variety of tools get used for Software Development and they are often not searchable as a whole, examples of tools in a single Microsoft Development Enterprise are :-

Visual Source Safe and/or Team Foundation Server - Code and document repository and versioning.
SharePoint (WSS 3.0) - User issues and new requirements log, Wiki for tips and tricks, and document repository for sharing with users.
HP Quality Centre - Test Cases and Test Plans
E-mails - for Support communication, project management etc
Development Bug Tracking system - Often implemented as a custom built database solution, or using TFS.
Online Help - Where your users are told about everything in the software (sigh...which they demand, but never use!)

I've seen a lot of teams struggle to find anything historical as there are just too many places to look. The dream of bringing all these items together in a single tool isn't that realistic seeing that a single tool which does everything above (and do it well) will cost a fortune - documents, e-mail etc are always going to be outside of most SDLC tools.

Example - An ongoing Enterprise Development team is still developing several years after the first release. Years have passed and a lot of the original team have moved on and unfortunately with them all previous decisions. Being a professional team everything is well documented, however they have left behind 50,000 artifacts across e-mails, documents, bug tasks, test cases, user issues etc. The team decides that a field needs to be altered, but have no idea when it was introduced or more importantly why. Team members start searching across all repositories, but each tool has its own searching syntax and some systems like e-mail, online help and even source control have either no, or extremely poor quick content searching capabilities out of the box.

Solution
Wouldn't it be great, and much simpler if all content could be searched and additionally the search facility provided was quick and powerful enough to choose which areas to search across. Obviously you can go and purchase an Enterprise searching solution with plug-ins to the many systems, but this creates an administrative mess in having to divide up the content for different purposes.

Click picture to view in better detail

I believe there is a much simplier solution by using the native features in Windows Sharepoint Services (WSS 3.0) which comes standard on Windows 2003. You can create an SDLC content searching facility by exporting the content from the above systems in a HTML, DOC, or MSG format and using SharePoint's indexing facilities to search across the content. Obviously data will be duplicated, but the SharePoint content can be seen as read-only and thus synchronised daily/weekly from the source systems. Disk is relatively cheap and most of the content here is small, however numerous.

There are 2 parts to this solution 1) the Searching Portal which the SharePoint site with a SharePoint Web Part and 2) a tool to extract and load the content into SharePoint. Point 2 will be explained in a future post

How the SDLC Content searching portal works
WSS 3.0 provides a search page to conduct searching over content. It is in the format of http://<siteurl>/_layouts/searchresults.aspx?k=<KEYWORDS>&u=<SEARCHSUBSITE>
The query string allows for standard search test and additionally the entry of search properties. e.g. instead of just 'k=Functional Specification' you can add the syntax 'k=FileExtension:"DOC" Functional Specification'. Some useful search properties I've found in my travels for WSS 3.0

Title - This is the document title, extracted from the document meta data in Office documents, or from the <title> tag in HTML documents.
FileName - This is the document filename.
Size - This is the document size
Write - This is the date the file was last written to.
Author - This is the document author
FileExtension - This is extension of the documents. e.g. FileExtension:xls
VPath - The virtual path to the item.

The last Property is the one that allows us to restrict to certain content areas. These properties get used by entering +<property>:<value>.

Steps
1. IFilters need to be installed on the Sharepoint server to scan the content uploaded from the various systems. e.g. if you have PDFs you'll need to install a PDF IFilter. For a long time a Microsoft MSG IFilter was missing for some time, however this is now available (http://blog.gavin-adams.com/2007/10/09/enabling-the-inbuilt-msg-ifilter-on-sharepoint-even-64bit/)
2. On a SharePoint site create a Document Libraries for each of the content you plan to extract. These provide a container for content and using 'VPATH' property field provide a way to differentiate content.
3. Add to the SharePoint site a 'Form Web Part' and select Source Editor.
4. Download the code and insert into the Source Editor.

This code is using check boxes to create vpath entries that are fed into the SharePoint search. Sections have been re-produced below - you need to replace the red code below with your own Sharepoint URL and the green code with your own document libraries, descriptions and code.

Click picture to view in better detail

5. Optionally I suggest adding a Content Editor Web Part with the following Content to help users :-

Use the Search facility above to search for documents and/or e-mails and/or SDLC artifacts. Enter search text and restrict by specific areas. Selecting no areas will search all areas. Additionally search via document properties. Syntax : e.g. Title:"Load Balancing"Properties available :

Title - This is the document title, extracted from the document meta data in Office documents, or from the <title> tag in markup documents.
FileName - This is the document filename.
Size - This is the document size
Write - This is the date the file was last written to.
Author - This is the document author
FileExtension - This is extension of the documents. e.g. FileExtension:xls

That's it!

Freeware - VSSMonitor

2007-11-05T13:35:00.002+11:00

A VBScript utility that continually monitors a VSS database and sends emails when certain patterns are detected.

This is an extension on the free utility written by Doug Schmidt called SSMonitor. You can view the original functionality at the CodeProject web site:

http://www.codeproject.com/vbscript/ssmonitor.asp

Thanks Doug for a great utility.

From the original functionality it has been extended to provide the following :-
1. Highwater marker (basically a file offset) to ensure that if the PC is rebooted the ‘cached’ events aren’t lost. This is only moved along when there are no events cached.
2. Checked-out status – using VSS automation conducting a check of files that have been Checked Out for an configurable number of days.
3. Supporting day or the week notifications – e.g. not just every 15 mins, or daily at 5:00pm, but Friday 5:00pm.
4. Regular Expression check on username – same as the filename expression check, but on usernames.

I’ve used the new features above for the following checks
- before the weekend notifying all developers of code checked out.
- notified when a particular person checks in code. Perfect for checking what new starters or recently terminated staff may be up to.
- tracking for interested parties when only a particular person makes a change.

Download. VBScript source is included in download.

*************************************************
To install:
1. Copy and Unzip the VSSMonitor.zip archive to a folder on your harddrive
2. As per SSMonitor installation obtain a copy of SMTP.ocx and copy to your harddrive - http://www.ostrosoft.com/smtp_component.asp. Additionally register the component.
3. For ‘Check Out’ functionality if VSS 6.0 isn’t installed on the PC that you’ll be running VSSMonitor then copy the Visual Source Safe automation dlls SSUS.dll and SSAPI.DLL to your VSSMonitor folder. Register SSAPI.DLL.
4. Configure the config.xml file which your SMTP, VSS and Pattern events. A sample file is included in the zip.
5. Run the batch file VSSMonitor.cmd which will commence the monitoring - a trace file produced in the Log folder.
*************************************************

Additional Config.xml values beyond the SSMonitor standard :-
** Database **
VSSINIPath - location to the srcsafe.ini file
VSSUsername - VSS Username
VSSPassword - VSS Password

** Pattern **
fixedReportTime – supports ‘ddd hh:mm’ format. E.g. ‘Fri 8:00 AM’ or ‘8:00 AM’

userFilter – Regular Expression of usernames for filtering

eventFilter – new type being ‘Checked Out’
checkedOutDays – Number of days which a file must be checked out for to trigger being included in the Checked Out notification
pathTopLevel - Top level(s) of VSS that the CheckOut pattern should commence searching from, reduces query time if more specific, comma seperated for more than 1 path

** Legal **
This program is free software; you can redistribute it and/or modify it.
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.

Freeware - Nockylock

2007-11-05T08:26:00.002+11:00

Nockylock is a freeware program that stops a 2 year old from harming the computer by locking the keyboard and locking the mouse. This software was inspired by my 2 year old who enjoys watching 'The Wiggles'. Unfortunately she also enjoys banging the keyboard and clicking the mouse in the process. This simple utility allows the parent to lock the computer from all input (BlockInput API) and then using ALT-CTRL-DEL select Cancel and then stop the utility

Download. VB6 Source is included in download.

*************************************************
To install:
1. Copy the Nockylock folder to a location on your harddrive
2. Run the program Nockylock.exe
3. Select 'Start' to commence the countdown to the locking of the PC input. Note: Nockylock will continue to lock the input even if it has lost focus on the desktop. e.g. you can full screen a media player.
4. Once locked select ALT-CTRL-DEL and then select Cancel from the dialog
5. Navigate to the Nockylock window and select 'Stop'
*************************************************

This application has been tested under various Windows versions - tested under Windows XP, Windows 2000 and Windows 2003.

** 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.

Freeware - Firstlines

2007-11-05T08:19:00.002+11:00

FirstLines is a freeware program inspired by a Penguin Classics screensaver that was released in 2005. Unfortunately it only showed 3 quotes, so I decided to write my own.

Using this application you can configure the quote file to use, font details, and the colour. Follow the format in the sample quote file (FirstLines.txt) to add new quotes. There is also provided an extended quotes file (FirstLinesExtended.txt) that provides a lot more quotes.

Download. VB6 source is included in download.

*************************************************
To install:
1. Copy the FirstLines. folder to a location on your harddrive
2. Right-Click on the FirstLines.scr after the copy and select Install
3. Configure the settings and then preview.
*************************************************

Quotes obtained from http://www.people.cornell.edu/pages/jad22/index.html#Categories

This application has been tested under various Windows versions - tested under Windows XP, Windows 2000 and Windows 2003.

** 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.

About Gluegood

2007-11-05T07:43:00.002+11:00

Welcome to Gluegood Software

I specialise in providing advice and building tools that are useful for Enterprises that utilise Microsoft applications.

My philosophy is that all utilities available on this site should be Licensed under GNU (e.g. Freeware). Having other things in my life I'm unable to offer extensive support.

By day I'm a Software Development Manager with a passion for functional software.

Hope you enjoy your time in the site. If you have any comments please e-mail me using the e-mail address – gluegood at gmail dot com