Thursday, December 11, 2008

Extracting data using Microsoft Logparser

I've fielded several questions lately about loading Group membership information from Active Directory. The Active Directory sample in the Qlikview Cookbook uses AdsDSO and can load "single-valued" fields such as Name and Mail. AdsDSO will not load "multi-valued" fields such as "memberOf" or "member" -- fields that define group members.

To read multi-valued fields, you'll have to install some sort of tool to extract the data into a format QV can load. There are a number of free and commercial utilities available that will extract AD information into a text file.

My favorite tool for AD extracts is the free Microsoft Logparser. Google for it, you'll find lots of information as well as the download link. There is also a Logparser book and forum available.

Logparser can read data from many different inputs -- Active Directory, IIS logs, Windows Event logs, Registry -- to name a few. Logparser can write to several different output formats, CSV being the most useful for QV.

Logparser uses a SQL syntax for it's queries. Here's an example:

logparser -objClass:Group "select cn, member into tmp\AdGroups.csv from LDAP://mydomainController"

This Logparser query will create the output file "tmp\AdGroups.csv". The file will contain one row for each group (cn). The members of the group will be returned as a single field with the members separated by the pipe "|" character. The members are easily separated in the QV load using the QV subfield() function:

subfield(member, '|') as member

Other uses I've found for using Logparser with Qlikview:

  • Extracting data from Windows Event logs.
  • Preprocessing IIS log files. The fields contained in a IIS log can vary between sites and may also change dynamically within the same physical file. Logparser can neutralize these differerences and produce a common input for QV load.

Logparser is a favorite tool of mine. I use it frequently for non-Qlikview tasks as well.

-Rob

Update 12/12/2008 I've published a complete example of using logParser to extract Group and User data from AD for loading into QV. The example is in version 9 of the Qlikview Cookbook available at http://robwunderlich.com/Download.html