» Documentation

OtherSources

Windows EventLog Source

Open "WindowsEventLogSchema.sql" execute.

The procedure SynchronizeSource uses plugin "import.dll" to read Windows Event Logs.

Here is how to access eventlog with SQL/DQL:

execute dql
begin
plugin([import.dll]:[import_eventlog]) 
    'localhost',
    'Application',
    0
    to @Results
    project by @this.Message
end 

AddSource procedure registers event log with parameters:

  • EventLog - the name of source.
  • Time - Map [Time] field with [Created] dimension.
  • join EventLog.[sources] [sources] on ([log].serverId=[sources].serverId) - join [Log] tables with [sources] in order to index SourcerName.

Example:

execute AddDimension 'EventLog','SourceName','[sources].[name]','VARCHAR',7 

Categorize facts by severity:

execute AddDimension 'EventLog','EntityPath', 
    'CASE 
    WHEN [log].level = \\\'Warning\\\' THEN \\\'Warning/EventLog\\\' 
    WHEN [log].level = \\\'Information\\\' THEN \\\'Information/EventLog\\\' 
    WHEN [log].level = \\\'Error\\\' THEN \\\'Error/EventLog\\\' 
    Else null END','VARCHAR',4 

Application Log/Nlog source

In a way it is quite similar to windows event logs, except, loading and mapping facts to dimensions. Let's look at the "NLogSchema.sql" SQL script. Its generic example and requires customization.

The table [sources] has 2 interesting parameters: [regex] and [start].

  • Regex defines a regular expression to parse the source log file. The fields m1,m2,...represents regex groups:

    - (\\w+) - field m1.
    - (\\d+) - m2.
    - body - the field represents the remaining string after the last match. 

The parsing is done by DQL plugin import.dll function "import". Example, of parsing log file in a query:

execute dql
    begin
        plugin([import.dll]:[import]) 
        '(\\w+)\\s*(\\d+)' 
        'fielPath',
        0
        to @Results 
        project by @this.m1,@this.m2,@this.body
    end 
  • Start defines the offset in bytes to start parsing the file. This is used for differential indexing, in order to read changes.

SQL Server

Insert rows to table:

EXECUTE DQL
BEGIN
open_oledb 'Provider=SQLNCLI10;Server=localhost; Database=mitsActivity; Trusted_Connection=yes','SELECT [LogSuperTypeID],[LogSuperTypeName]FROM [MitsActivity].[dbo].[LogSuperTypes]' to @T0
project by @this.[LogSuperTypeID] ,@this.[LogSuperTypeName]

insert_bulk @T0,'ActivityLog.LogSuperTypes'
SET
@this.[LogSuperTypeID]=@T0.[LogSuperTypeID] 
,@this.[LogSuperTypeName]=@T0.[LogSuperTypeName] 
END 

Select:

EXECUTE DQL
BEGIN
open_oledb 'Provider=SQLNCLI10;Server=localhost; Database=mitsActivity; Trusted_Connection=yes','SELECT [LogSuperTypeID],[LogSuperTypeName]FROM [MitsActivity].[dbo].[LogSuperTypes]' to @Results
project by @this.[LogSuperTypeID] ,@this.[LogSuperTypeName]
END