Logging Active Users (2/2)

Welcome to part two. In part one I described how and where to find login attempts. This post explains how to connect and extract login infos via Integration Services.

SSIS

By default, Datazen creates logfiles for several days. You can adjust this setting in a config file. [1] Thus, we have to loop through each available log file.

For each log file, we store extracted login information to a table (e.g. “T_DZ_LOGINS”).

Data Flow

My Data Flow Task for extracting logins looks like this.

LOAD LOGFILE: Loads log file as File System Task.
EXTRACT LOGIN ATTEMPTS: Searches login attempts using Conditional Split Transformation Task.
EXTRACT USER_NAME: Extracts username via Expressions in a Derived Column Transformation
STRING_CONVERSION: Converting to DT_WSTR
TRANSFER TO BI_TOOL: Simple task for saving to table.

Load logfile

Load Logfile as Flat File

Extract Login Attempts

SSIS Expression:=FINDSTRING(ROW, “Datazen.Server.Core.Providers.AdAuthentificationProvider Attempt AD FindByIdentity:”,1)>0

Login attempt expression

Get Username and Date

SSIS Expression:

USER_NAME=REPLACE(ROW, “Datazen.Server.Core.Providers.AdAuthentificationProvider Attempt AD FindByIdentity:”,””)

DATE=(DT_DBTIMESTAMP)(LEFT(DATE,18))

Expression for Username and Date

References:
[1] http://www.datazen.com/docs/?article=server/server_logs