-
Title
Using CSV Parsers -
Description
There is data within a log that needs to be extracted to be used in a template and or rewrite rule to enrich the log(s) being sent. -
Resolution
One of the ways to extract data from within a Syslog-ng log is by using the CSV Parser included within Syslog-ng. By using this parser the data can be extracted for use with custom .SDATA. metadata, rewrite rules to rewrite parts of the message, or in templates to add additional data to the log itself for better management at the endpoint.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:
1 2020-04-24T07:49:39-03:00 MACHINE.ad.domain.com Microsoft_Windows_security_auditing. 1852 - [EVENT_CATEGORY="Special Logon" EVENT_FACILITY="16" EVENT_ID="4672" EVENT_LEVEL="0" EVENT_NAME="Security" EVENT_REC_NUM="1581401" EVENT_SID="N/A" EVENT_SOURCE="Microsoft Windows security auditing." EVENT_TASK="Special Logon" EVENT_TYPE="Success Audit" EVENT_USERNAME="USER\\MACHINE$"][meta sequenceId="1005628" sysUpTime="86446"] USER\MACHINE$: Security Microsoft Windows security auditing.: [Success Audit] Special privileges assigned to new logon.Here is the message part of the log that will be parsed out to extract the desired data:
# ${MESSAGE} = [EVENT_CATEGORY="Special Logon" EVENT_FACILITY="16" EVENT_ID="4672" EVENT_LEVEL="0" EVENT_NAME="Security" EVENT_REC_NUM="1581401" EVENT_SID="N/A" EVENT_SOURCE="Microsoft Windows security auditing." EVENT_TASK="Special Logon" EVENT_TYPE="Success Audit" EVENT_USERNAME="USER\\MACHINE$"][meta sequenceId="1005628" sysUpTime="86446"] USER\MACHINE$: Security Microsoft Windows security auditing.: [Success Audit] Special privileges assigned to new logon.
- The first parser is below with the extracted data below that showing what macro (column) is pulling the different data.
- The delimiter being used is the " character which is removed when being used as the delimiter.
- The strip-whitespace flag removes any leading or trailing whitespace around the delimiter being used.
- The escape-none flag ensures the parsed message does not use any escaping for using the quote character literally.
- The drop-invalid flag ensures that the parser is not processed on logs that do not match the parser. For instance, in regards to the example below, logs with more than 27 columns or less than 27 columns will not be processed and thus the parser will not apply. This is important for logs that are not meant for the parser.
- The greedy flag, which is shown in the 2nd CSV parser below, ensures that the remainder of the message is assigned to the last macro (column) regardless of the number of delimiters set.
- The template, which in the example below is using the ${MESSAGE} macro, sets which data to be called which is to be parsed out. In the examples below only certain macros (columns) need to be parsed out to extract data which can be seen.
- The delimiters set which character or string is used to specify where and how the data should be parsed out into separate macros (columns).
- The columns are the different macros (columns) in which the data that is extracted is being put into.
The parser below has 27 columns of data, any logs with more or less than 27 columns will not be affected because the drop-invalid flag is set. 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. Lastly, only data from the ${MESSAGE} part of the log as seen above is being used to parse out.
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:
# ${PCSV1_1} = [EVENT_CATEGORY= # ${PCSV1_2} = Special Logon # ${PCSV1_3} = EVENT_FACILITY= # ${PCSV1_4} = 16 # ${PCSV1_5} = EVENT_ID= # ${PCSV1_6} = 4672 # ${PCSV1_7} = EVENT_LEVEL= # ${PCSV1_8} = 0 # ${PCSV1_9} = EVENT_NAME= # ${PCSV1_10} = Security # ${PCSV1_11} = EVENT_REC_NUM= # ${PCSV1_12} = 1581401 # ${PCSV1_13} = EVENT_SID= # ${PCSV1_14} = N/A # ${PCSV1_15} = EVENT_SOURCE= # ${PCSV1_16} = Microsoft Windows security auditing. # ${PCSV1_17} = EVENT_TASK= # ${PCSV1_18} = Special Logon # ${PCSV1_19} = EVENT_TYPE= # ${PCSV1_20} = Success Audit # ${PCSV1_21} = EVENT_USERNAME= # ${PCSV1_22} = USER\\MACHINE$ # ${PCSV1_23} = ][meta sequenceId= # ${PCSV1_24} = 1005628 # ${PCSV1_25} = sysUpTime= # ${PCSV1_26} = 86446 # ${PCSV1_27} = ] USER\MACHINE$: Security Microsoft Windows security auditing.: [Success Audit] Special privileges assigned to new logon.
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}") ); };
Once parsed out the data pertaining to each macro (column) can be seen below:# ${PCSV2_1} = ] USER\MACHINE$ # ${PCSV2_2} = Security Microsoft Windows security auditing. # ${PCSV2_3} = [Success Audit] Special privileges assigned to new logon.
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.Lastly, using the template option in the destination statement the message of the log is rewritten into a format that, in this case, is more desirable.destination d_tcp { syslog( "10.10.10.10" transport("tcp") port(601) template("Date=${R_DATE}, Host=${HOST}, Event_Message = ${.SDATA.custom@18372.4.Event_Message}, Event Category = ${.SDATA.custom@18372.4.Event_Category}, Event Facility = ${.SDATA.custom@18372.4.Event_Facility}, Event ID = ${.SDATA.custom@18372.4.Event_ID}, Event Level = ${.SDATA.custom@18372.4.Event_Level}, Event Name = ${.SDATA.custom@18372.4.Event_Name}, Event RecNum = ${.SDATA.custom@18372.4.Event_RecNum}, Event SID = ${.SDATA.custom@18372.4.Event_SID}, Event Source = ${.SDATA.custom@18372.4.Event_Source}, Event Task = ${.SDATA.custom@18372.4.Event_Task}, Event Type = ${.SDATA.custom@18372.4.Event_Type}, Event Username = ${.SDATA.custom@18372.4.Event_Username}, Event MetaSequenceID = ${.SDATA.custom@18372.4.Event_MetaSequenceID}, Event SysUpTime = ${.SDATA.custom@18372.4.Event_SysUpTime}) ); };
In order to use the rewrites and parsers, they need to be called within the log path in the order in which the parsers are called.Below is an example of a log path statement using the parsers, rewrite, and destination statement above.log{ source(s_example_source); parser(p_pcsv1); parser(p_pcsv2); rewrite(r_sdata_metadata); destination(d_tcp); };
Once done, the below log is what the new log written by the template will look like after parsing and rewriting of the different macros: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