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 tmpAdGroups.csv from LDAP://mydomainController”
This Logparser query will create the output file “tmpAdGroups.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
Hi, I just started a blog about Qlikview and have a link to your site in my blogroll.
Maybe you are willing to put a link to my blog at your blog!
Happy Blogging!
Gilles
Rob,
This looks really cool! Could be useful to build a web analytics app using IIS server logs…I’ve been trying to do it in my spare time, but the different lengths of the strings make it difficult. I’ll definitely be giving this a try!
Keep it up!
Hi all,
LogParser implements an SQL-like query language that queries and manipulates the input. It can handle different IIS log file formats, text log files like CSV, TSV, generic text. LogParser defines some functions to manipulate the data it extracts from the log. Thanks for sharing it……..