Using CSV Parsers (4234130)

The CSV parser works using delimiters to put different parts of the data into different columns as would be seen in a normal Comma Separated Value (CSV) list. Once the data extracted is put into different columns it can either be used or extracted even further into more columns for narrowing down exactly the data that is being looked for.

Please see the latest Syslog-ng PE admin guide's CSV Parsing chapter for details on options and for more general information.

*NOTE* When using the CSV parser the incoming logs must match in format in order for the parsing to take place effectively for all logs. If the log format changes from log to log the CSV parser will not work as expected.

Below is an example of a log with a 2-part CSV parser designed to extract data. There are notes below that should help in explaining the parser in more detail:

Example Log:

Here is the message part of the log that will be parsed out to extract the desired data:

parser p_csv1 {
csv-parser(
columns("PCSV1_1", "PCSV1_2", "PCSV1_3", "PCSV1_4", "PCSV1_5", "PCSV1_6", "PCSV1_7", "PCSV1_8", "PCSV1_9", "PCSV1_10", "PCSV1_11", "PCSV1_12", "PCSV1_13", "PCSV1_14", "PCSV1_15", "PCSV1_16", "PCSV1_17", "PCSV1_18", "PCSV1_19", "PCSV1_20", "PCSV1_21", "PCSV1_22", "PCSV1_23", "PCSV1_24", "PCSV1_25", "PCSV1_26", "PCSV1_27")
flags(escape-none, drop-invalid, strip-whitespace)
delimiters(chars("""))
template("${MESSAGE}")
);
};

Once parsed out the data pertaining to each macro (column) can be seen below:

Things are starting to look like usable data, however, the message still needs additional parsing to extract the data fully.

To do so, a second parser is created calling the ${PCSV1_27} macro (column) in the template to further parse out the data within to extract the actual message that is being sent from the source.

Here the delimiter is using the : character and the greedy flag is set so after the second macro of data is stored the remaining data, regardless of whether or not there are more : characters present, will be stored in the third macro (column). Any whitespace before/after the delimiter character is stripped using the strip-whitespace flag and no escaping for the quote character occurs as the escape-none flag is set. 

parser p_csv2 {
        csv-parser(
                columns("PCSV2_1", "PCSV2_2", "PCSV2_3")
                flags(escape-none, greedy, strip-whitespace)
                delimiters(chars(":"))
                template("${PCSV1_27}")
        );
};

Next a rewrite rule is created to store the extracted information into customized .SDATA. metadata which can be sent along with Syslog-IETF logs and which can be used in the template of the destination to change how the message is formatted.

rewrite r_sdata_metadata {
        set("${PCSV1_2}", value(".SDATA.custom@18372.4.Event_Category"))
        set("${PCSV1_4}", value(".SDATA.custom@18372.4.Event_Facility"))
        set("${PCSV1_6}", value(".SDATA.custom@18372.4.Event_ID"))
        set("${PCSV1_8}", value(".SDATA.custom@18372.4.Event_Level"))
        set("${PCSV1_10}", value(".SDATA.custom@18372.4.Event_Name"))
        set("${PCSV1_12}", value(".SDATA.custom@18372.4.Event_RecNum"))
        set("${PCSV1_14}", value(".SDATA.custom@18372.4.Event_SID"))
        set("${PCSV1_16}", value(".SDATA.custom@18372.4.Event_Source"))
        set("${PCSV1_18}", value(".SDATA.custom@18372.4.Event_Task"))
        set("${PCSV1_20}", value(".SDATA.custom@18372.4.Event_Type"))
        set("${PCSV1_22}", value(".SDATA.custom@18372.4.Event_Username"))
        set("${PCSV1_24}", value(".SDATA.custom@18372.4.Event_MetaSequenceID"))
        set("${PCSV1_26}", value(".SDATA.custom@18372.4.Event_SysUpTime"))
        set("${PCSV2_3}", value(".SDATA.custom@18372.4.Event_Message"));
};

The custom .SDATA. macros with the correlating data from the example log can be seen below:

# ${.SDATA.custom@18372.4.Event_Category} = Special Logon
# ${.SDATA.custom@18372.4.Event_Facility} = 16
# ${.SDATA.custom@18372.4.Event_ID} = 4672
# ${.SDATA.custom@18372.4.Event_Level} = 0
# ${.SDATA.custom@18372.4.Event_Name} = Security
# ${.SDATA.custom@18372.4.Event_RecNum} = 1581401
# ${.SDATA.custom@18372.4.Event_SID} = N/A
# ${.SDATA.custom@18372.4.Event_Source} = Microsoft Windows security auditing.
# ${.SDATA.custom@18372.4.Event_Task} = Special Logon
# ${.SDATA.custom@18372.4.Event_Type} = Success Audit
# ${.SDATA.custom@18372.4.Event_Username} = USER\\MACHINE$
# ${.SDATA.custom@18372.4.Event_MetaSequenceID} = 1005628
# ${.SDATA.custom@18372.4.Event_SysUpTime} = 86446
# ${.SDATA.custom@18372.4.Event_Message} = [Success Audit] Special privileges assigned to new logon.

log{
source(s_example_source);
parser(p_pcsv1);
parser(p_pcsv2);
rewrite(r_sdata_metadata);
destination(d_tcp);
};

1 2020-04-24T07:49:39-03:00 MACHINE.ad.domain.com Microsoft_Windows_security_auditing. 1852 - Date=2020-04-24T07:49:39-03:00, Host=MACHINE.ad.domain.com, Event_Message = [Success Audit] Special privileges assigned to new logon., Event Category = Special Logon, Event Facility = 16, Event ID = 4672, Event Level = 0, Event Name = Security, Event RecNum = 1581401, Event SID = N/A, Event Source = Microsoft Windows security auditing., Event Task = Special Logon, Event Type = Success Audit, Event Username = USER\\MACHINE$, Event MetaSequenceID = 1005628, Event SysUpTime = 86446