Parsing Log File Records

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Appendix A: Legacy Adapters and Agents > Log File Adapter >

Parsing Log File Records

Previous pageReturn to chapter overviewNext page

Note: This topic describes a legacy adapter. Unless you have used these in the past, we recommend using the Extensible Common Data Adapter and Agent instead of these legacy adapters and agents.

Individual function examples:

oFIXED_COLUMN_LOG_PARSE
oVARIABLE_COLUMN_LOG_PARSE
oSYS_LOG_PARSE
oW3C_LOG_PARSE
oREGEX_LOG_PARSE
oFAST_REGEX_LOG_PARSER

Examples of row parse functions using views and tables

oFIXED_COLUMN_LOG_PARSE
oVARIABLE_COLUMN_LOG_PARSE
oSYS_LOG_PARSE
oW3C_LOG_PARSE

Individual function examples

The examples below illustrate using FIXED_COLUMN_LOG_PARSE, VARIABLE_COLUMN_LOG_PARSE, SYS_LOG_PARSE, and W3C_LOG_PARSE.

FIXED_COLUMN_LOG_PARSE

select t.r.a, t.r.b from

(values (FIXED_COLUMN_LOG_PARSE

         ( '  1   true'

          , 'a TYPE Integer START 0 FOR 3, b TYPE boolean START 4'

         )))

t(r);

 

whose output would appear as

         +----+-------+

         | A  |   B   |

         +----+-------+

         | 1  | true  |

         +----+-------+

         1 row selected

You can also set output format using the following command:

         !set outputformat csv,

 

the output would appear as

         'A','B'

         '1','true'

         1 row selected

 

A second example:

select t.r.a, t.r.b, t.r.c from

(values (FIXED_COLUMN_LOG_PARSE

         ( '  1   true 423.345 '

          , 'a TYPE Integer START 0 FOR 3, b TYPE boolean

               START 4 FOR 6, c TYPE numeric(5,2) START 10'

         )))

t(r);

 

whose output would appear as

         +----+-------+---------+

         | A  |   B   |    C    |

         +----+-------+---------+

         | 1  | true  | 423.35  |

         +----+-------+---------+

         1 row selected

Further examples

Multiline string literal

select t.r.a, t.r.b, t.r.c from

(values (FIXED_COLUMN_LOG_PARSE('  1   true 423.345 ',

   'a TYPE Integer START 0 FOR 3, '

   'b TYPE boolean START 4 FOR 6, '

   'c TYPE numeric(5,2) START 10')))

t(r);

 

whose output would appear as

'A','B','C'

'1','true','423.35'

1 row selected

NOT NULL types

When the NOT NULL restriction is added to the select query, log records in which any such field is missing are dropped, because they do not meet the business rules requirements that caused the NOT NULL restriction to be included.

In the following case, all expected fields are present, so the query does produce output, with all fields present.

select t.r.a, t.r.b, t.r.c from

(values (FIXED_COLUMN_LOG_PARSE('  1   true 423.345 ',

   'a TYPE Integer NOT NULL START 0 FOR 3, '

   'b TYPE boolean NOT NULL START 4 FOR 6, '

   'c TYPE numeric(5,2) NOT NULL START 10')))

t(r);

 

In this case the output would be the same as above for multiline string literals.

However, in the following case, fields b and c are not in the log file record, which contains only field a. In this case, no output is produced.

select t.r.a, t.r.b, t.r.c from

(values (FIXED_COLUMN_LOG_PARSE('  1 ',

   'a TYPE Integer NOT NULL START 0 FOR 3, '

   'b TYPE boolean NOT NULL START 4 FOR 6, '

   'c TYPE numeric(5,2) NOT NULL START 10')))

t(r);

         +----+----+----+

         | A  | B  | C  |

         +----+----+----+

         +----+----+----+

         No rows selected

 

If the NOT NULL restriction is removed, for example from field c, then a row is returned for that record (with c empty) even though no value for c is in the log file record, as follows:

select t.r.a, t.r.b, t.r.c from

   (values (FIXED_COLUMN_LOG_PARSE('  1   1  ',

     'a TYPE Integer NOT NULL START 0 FOR 3, '

     'b TYPE boolean NOT NULL START 4 FOR 6, '

     'c TYPE numeric(6,3)  START 10')))

  t(r);

           +----+-------+----+

           | A  |   B   | C  |

           +----+-------+----+

           | 1  | true  |    |

           +----+-------+----+

           1 row selected

select t.r.a, t.r.b, t.r.c from

   (values (FIXED_COLUMN_LOG_PARSE('  1   1  ',

     'a TYPE Integer NOT NULL START 0 FOR 3, '

     'b TYPE boolean  START 4 FOR 6, '

     'c TYPE numeric(6,3)  START 10')))

  t(r);

           +----+-------+----+

           | A  |   B   | C  |

           +----+-------+----+

           | 1  | true  |    |

           +----+-------+----+

           1 row selected

 

It is worth noting that for b, a boolean, 0 returns false, any non-zero number returns true, and anything else causes no record to be returned.

Two additional parsing examples:

select t.r.a, t.r.b from

 (values (FIXED_COLUMN_LOG_PARSE('one two', 'a START 0 FOR 3, b START 4')))

 t(r);

returns

           +----------+----------+

           |    A     |    B     |

           +----------+----------+

           | one      | two      |

           +----------+----------+

           1 row selected

and

select t.r.a, t.r.b from

 (values (FIXED_COLUMN_LOG_PARSE(cast('one two' as CHAR(10)), 'a START 0 FOR 3, b START 4')))

 t(r);

returns

           +-------------+-------------+

           |      A      |      B      |

           +-------------+-------------+

           | one         | two         |

           +-------------+-------------+

           1 row selected

showing extra spaces due to the cast.

Missing 2nd field

select t.r."a&c", t.r."b" from

 (values (FIXED_COLUMN_LOG_PARSE('one', '"a&c" START 0 FOR 3, "b" START 4')))

 t(r);

returns

           +------+----+

           | a&c  | b  |

           +------+----+

           | one  |    |

           +------+----+

           1 row selected

VARIABLE_COLUMN_LOG_PARSE

Examples

With column count

select t.r."COLUMN1", t.r."COLUMN2", t.r."COLUMN3" from

(values (VARIABLE_COLUMN_LOG_PARSE

             ('John Doe,4919 Lido Sands Drive, Newport Beach, CA, 92661,$15.22'

             , 3, ',', '\')))

 t(r);

returns +------------------------------------------------------------------+-----------+ | COLUMN1 | | +------------------------------------------------------------------+-----------+ | John Doe | 4919 Lido | +------------------------------------------------------------------+-----------+ 1 row selected

With column names

select t.r.Name, t.r.Address, t.r.Amount from

 (values (VARIABLE_COLUMN_LOG_PARSE

             ('John Doe,"4919 Lido Sands Drive, Newport Beach, CA, 92661",$15.22'

             , 'Name, Address, Amount', ',', '\', '"'

             )))

 t(r);

returns

+--------------------------------------------------------------------+---------+ | NAME | | +--------------------------------------------------------------------+---------+ | John Doe | 4919 Li | +--------------------------------------------------------------------+---------+ 1 row selected

With column names with types

select t.r.Entry, t.r.flag,

      t.r.Amount, t.r.tm from

(values (VARIABLE_COLUMN_LOG_PARSE

            ('14,true,50.32,06:01:01',

             'Entry TYPE smallint, flag TYPE boolean, Amount TYPE real, tm TYPE time',

             ',', '\', '"'

   )))

 t(r);

returns +--------+-------+---------+-----------+ | ENTRY | FLAG | AMOUNT | TM | +--------+-------+---------+-----------+ | 14 | true | 50.32 | 06:01:01 | +--------+-------+---------+-----------+ 1 row selected

Timestamp type

select t.r.tm, t.r.foo from

(values (VARIABLE_COLUMN_LOG_PARSE

             ('2006-3-21 1:23:45.6,bar',

              'tm TYPE timestamp,foo', ','

              )))

 t(r);

Too many fields

The last field will be quietly dropped

select t.r."COLUMN1", t.r."COLUMN2" from

(values (VARIABLE_COLUMN_LOG_PARSE

          ('Stephan Zuercher,"4919 Lido Sands Drive, Newport Beach, CA, 92661",$15.22'

           , 2, ',', '\', '"')))

  t(r);

With ':' as delimiter and null escape character

select t.r."COLUMN1", t.r."COLUMN2" from

(values (VARIABLE_COLUMN_LOG_PARSE

            ('one: "two:three"'

            , 2, ':'

            , CAST(null AS VARCHAR(1)), '"'

        )))

  t(r);

With ':' as delimiter and no escape character

select t.r."COLUMN1", t.r."COLUMN2" from

(values (VARIABLE_COLUMN_LOG_PARSE('one: "two:three"', 2, ':', '', '"'))) t(r);

With null as number of columns

select t.r."COLUMN1", t.r."COLUMN2" from

(values (VARIABLE_COLUMN_LOG_PARSE('one: "two:three"',

                  CAST(null as INTEGER), ':', '', '"'))) t(r);

With null delimiter

select t.r."COLUMN1", t.r."COLUMN2" from

(values (VARIABLE_COLUMN_LOG_PARSE('one: "two:three"', 2,

                  CAST(null AS VARCHAR(1)), '', '"'))) t(r);

With null quote

select t.r."COLUMN1", t.r."COLUMN2" from

(values (VARIABLE_COLUMN_LOG_PARSE('one: "two:three"', 2, ':', '', CAST(null AS VARCHAR(1))))) t(r);

SYS_LOG_PARSE

select t.r."COLUMN1", t.r."COLUMN2" from

(values (SYS_LOG_PARSE('Apr 25 06:01:01 violet kernel: smb_request: result -104'))) t(r);

Examples

null

select t.r."COLUMN1", t.r."COLUMN2" from

(values (SYS_LOG_PARSE(cast(null as varchar(1))))) t(r);

W3C_LOG_PARSE

select t.r."COLUMN1" from

(values (W3C_LOG_PARSE('host','%h'))) t(r);

Examples

null

select t.r."COLUMN1" from

(values (W3C_LOG_PARSE(cast(null as varchar(1)),'COMMON'))) t(r);

Row parse functions using views and tables

create schema rowparsertest;

set schema 'rowparsertest';

 

Using this schema, the sections at the links below illustrate using the named functions in the context of views and tables:

FIXED_COLUMN_LOG_PARSE
VARIABLE_COLUMN_LOG_PARSE
SYS_LOG_PARSE
W3C_LOG_PARSE

FIXED_COLUMN_LOG_PARSE

Create and populate table

create table fixedlogtable (

   message varchar(256) primary key

);

insert into fixedlogtable values

   ('apple        2'),

   ('watermelon   4'),

   ('pear         six'),

   (trim('grape')),

   ('peach         '),

   ('cherry       7');

Select statements

select * from fixedlogtable;

select t.r.col1, t.r.col2 from

(select FIXED_COLUMN_LOG_PARSE(message, 'col1 start 0 for 11, col2 start 13') r

      from fixedlogtable) t;

select t.r.col1, t.r.col2 from

(select FIXED_COLUMN_LOG_PARSE(message,

   'col1 start 0 for 11, col2 type integer start 13') r

      from fixedlogtable) t;

View

create view fixedlogtable_view as

select t.r.col1, t.r.col2 from

(select FIXED_COLUMN_LOG_PARSE(message, 'col1 start 0 for 11, col2 start 13') r

      from fixedlogtable) t;

select * from fixedlogtable_view;

VARIABLE_COLUMN_LOG_PARSE

Create and populate table

create table csvlogtable (

   message varchar(256) primary key

);

insert into csvlogtable values

   ('red, one '),

   ('blue, two, 2'),

   ('purple, "three,3" '),

   ('white, quote"""'),

   ('brown, escaped comma\,'),

   (trim('yellow'));

Select statements

select * from csvlogtable;

select t.r."COLUMN1", t.r."COLUMN2" from

(select VARIABLE_COLUMN_LOG_PARSE(message, 2, ',', '\', '"') r

      from csvlogtable) t;

View

create view csvlogtable_view as

select t.r."COLUMN1", t.r."COLUMN2" from

(select VARIABLE_COLUMN_LOG_PARSE(message, 2, ',', '\', '"') r

      from csvlogtable) t;

select * from csvlogtable_view;

SYS_LOG_PARSE

Create and populate table

create table syslogtable (

   message varchar(256) primary key

);

insert into syslogtable values

   ('Jun  2 12:13:51 mat devlabel: devlabel service started/restarted'),

   ('Jun  2 12:13:51 mat kernel: hub.c: new USB device 00:1d.2-2, assigned address 12');

Select statements

select * from syslogtable;

select t.r."COLUMN1", t.r."COLUMN2" from

(select SYS_LOG_PARSE(message) r

      from syslogtable) t;

View

create view syslogtable_view as

select t.r."COLUMN1", t.r."COLUMN2" from

(select SYS_LOG_PARSE(message) r

      from syslogtable) t;

select * from syslogtable_view;

W3C_LOG_PARSE

Create and populate table

create table w3ccommon (

   message varchar(256) primary key

);

insert into w3ccommon values

(192.168.254.30-angel [24/May/2004:22:01:02-0700] "GET /icons/apache_pb.gif HTTP/1.1" 304 0),

(192.168.254.30-angel [24/May/2004:22:01:02-0700] "GET /icons/small/dir.gif HTTP/1.1" 304 0);

Select statements

select * from w3ccommon;

select t.r."COLUMN1", t.r."COLUMN2", t.r."COLUMN3", t.r."COLUMN4",

      t.r."COLUMN5", t.r."COLUMN6", t.r."COLUMN7" from

(select W3C_LOG_PARSE(message, 'COMMON') r

      from w3ccommon) t;

View

create view w3ccommon_view as

select t.r."COLUMN1", t.r."COLUMN2", t.r."COLUMN3", t.r."COLUMN4",

      t.r."COLUMN5", t.r."COLUMN6", t.r."COLUMN7" from

(select W3C_LOG_PARSE(message, 'COMMON') r

      from w3ccommon) t;

select * from w3ccommon_view;