How to create dynamic columns (MACROS) in Syslog-ng csv-parser

Question

My application is writing message in to syslog-ng and syslog-ng is storing this message in to SQlite by separating the incoming message in to different columns.

My application log legend looks like :

unique-record-id usename date    Quantity    BOQ  comma,separated,profiles  Count   Vendor  applicable,vendor,categories    known,request,types vendor_code credit

The above message has 12 values, those are separated by tab (\t). In those 12 values some of them having comma separated strings. Those comma separated values are may vary for each log line, sometimes they may come 4 and sometimes they may come 2 but the position of comma separated values in the table if fixed say 6th,9th,10th columns.

sample log line :

140189 "john@192.168.0.22" "03/Mar/2015:10:05:01" 1593 89 "apple,mango,banana" 15 "google" "fruits,games,social,business" "check,block" 123 50

I can able to parse that message in to SQLite table (TABLE1) using the following parser

parser p_tab_space {
    csv-parser(columns("LOG.ID", "LOG.USERNAME", "LOG.DATE",
        "LOG.QUANTITY", "LOG.BOQ", "LOG.PROFILES",
        "LOG.COUNT", "LOG.VENDOR", "LOG.CATEGORIES",
        "LOG.REQUESTS", "LOG.CODE","LOG.CREDIT")
         delimiters("\t")
         );
};

Now i need to again parse LOG.PROFILES in to another table(PROFILE_TABLE) as

id | profiles | quantity
1  | apple    | 1593 
2  | mango    | 1593 
3  | banana   | 1593

as the same way i have to do this for remaining comma separated values in to different tables(CATEGORY_TABLE,REQ_TABLE).

example :

  id | categories | quantity
  1  | fruits     | 1593 
  2  | games      | 1593  
  3  | social     | 1593 
  4  | business   | 1593

Here the problem is we do not know how many coma separated values.

The main input message consist of 12 tab separated values we knew that already that's why we created 12 columns (MACROS) in that p_tab_space parser. But while we parse comma separated list we dont know how many values will be come ? therefore cant able to create macros.

I have gone through this link but no help.

If any of you can throw some ideas it will be so help full.

Thank you in advance!

score 1 · Answer 1 · answered Mar 07 '16 at 11:54

For this, you'll need a recent version of syslog-ng (at least 3.7). In this version, you'll have to write a custom template function in python that will split the relevant comma-separated parts of the log message, and create a macro for each dynamic value that you can use in the sql destination. To create a new macro, you can use the log_message["new-macro-name"]="value" format (as I see, this is not documented yet in the linked docs, I'll update it hopefully soon).

How to create dynamic columns (MACROS) in Syslog-ng csv-parser

1 Answers1