Format XML logfile and forward to rsyslog

mcoussi · October 21, 2022, 1:53pm

Hi,

i want to forward XML log of an oracle Database to a Graylog.

Th XML message start with

Example of message :

<msg time='2022-10-16T04:00:43.098+02:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='database_server' host_addr='XX.XX.XX.XX' module=''
 pid='123598'>
 <txt>  Current log# 3 seq# 239479 mem# 0: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_3_jfrygny3_.log
 </txt>
</msg>

But i don’t want to forward all the information.

I just to want to forward the message in the like this

logger -t ora-dbname-alertlog "Current log# 3 seq# 239479 mem# 0: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_3_jfrygny3_.log"

Do you know if it’s possible and how can i do it?

Thanks for your help,

Matt

ihe · October 21, 2022, 2:12pm

Hi @mcoussi
I’d suggest you use some Pipelines and to have some Grok patterns in a rule to get your message into the right fields. Use the remove_field-function to get rid of stuff you don’t want. If you search for “grok debugger” you will find plenty of tools helping you to get it working.

gsmith · October 21, 2022, 9:19pm

Hello @mcoussi

I agree with @ihe statement, you can use a log shipper and adjust the configurations then send what you want to Graylog.

Example:

<Input from_file>
    Module    im_file
    File      '/tmp/input'
    <Exec>
        # Parse $raw_event and create fields
        parse_xml();

        # Change the value of the $Severity field
        if ($Severity == 'NOTICE')
            $Severity = 'INFO';

        # Delete core fields that are not required
        delete($SourceModuleType);
        delete($SourceModuleName);

        # Convert fields back to XML
        to_xml();
    </Exec>
</Input>

mcoussi · October 31, 2022, 9:02am

Hi,
I have to use NxLog to Format my XML trace and send it to Graylog ?

Thanks,
Matt

mcoussi · October 31, 2022, 9:51am

Hi,

I have installed NxLog on a Database Server Test.
I have configure an input in Graylog as it writes in documentation.

But now i don’t know how can i configure nxlog to read my XML file, format it and forward the information to GrayLog.

Thanks for your help,

Matt

mcoussi · October 31, 2022, 1:56pm

Hi,

I have configured NXLOG to forward a text file and i get the log in the Graylog.

My config file :

<Extension gelf>
    Module        xm_gelf
</Extension>


<Input db-alertlog>
    Module  im_file
    File    "/u02/app/oracle/diag/rdbms/database/DATABASE/trace/alert_DATABASE.log"
</Input>

<Output graylog>
    Module        om_tcp
    Host          XX.XX.XX.XX
    port          XXXXX
    OutputType    GELF_TCP
</Output>

<Route db-alertlog_to_graylog>
        Path db-alertlog => graylog
</Route>

Now i want to format and send the XML.

<msg time='2022-10-16T04:00:43.098+02:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='database_server' host_addr='XX.XX.XX.XX' module=''
 pid='123598'>
 <txt>  Current log# 3 seq# 239479 mem# 0: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_3_jfrygny3_.log
 </txt>
</msg>

Thanks for your help,

Matt

mcoussi · October 31, 2022, 2:07pm

Hi,

I look the documentation for the module [xm_xml], i understand that the module is used for convert log to XML format “it can convert log messages to XML format and can parse XML into fields”.
Or i don’t understand (it’s possible)…

For my example, i have a log IN XML format and i want to forward the content to Graylog. And before send the content to graylog i want to format the output.

Thanks for your help,

Matt

gsmith · October 31, 2022, 10:22pm

Hey @mcoussi

Have you seen this?

NXLog Community Edition Reference Manual

mcoussi · November 2, 2022, 8:17am

Hi, @gsmith

Yes i have test it like this

<Extension multiline>
    Module          xm_multiline
    HeaderLine      /^<msg/
    EndLine         /^</msg>/
</Extension>

<Extension xmlparser>
    Module          xm_xml
</Extension>

<Extension json>
    Module          xm_json
</Extension>

<Input filein>
    Module          im_file
    File            "/u02/app/oracle/diag/rdbms/database/DATABASE/alert/log.xml"
    InputType       multiline
    <Exec>
        # Discard everything that doesn't seem to be an xml msg
        if $raw_event !~ /^<msg/ drop();

        # Parse the xml msg
        parse_xml();

        # Rewrite some fields
        $EventTime = parsedate($timestamp);
        delete($timestamp);
        delete($EventReceivedTime);

        # Convert to JSON
        to_json();
    </Exec>
</Input>

<Output fileout>
    Module          om_file
    File            'tmp/output'
</Output>

<Route parse_xml>
    Path            filein => fileout
</Route>

I don’t know where i have to configure [ParseAttributes] (XML (xm_xml) :: NXLog Documentation), and manipulate the data from the XML Parse.

Thanks,

Matt

mcoussi · November 2, 2022, 9:37am

Hi,

I tried to configure the ParseAttributes in the extension modul xm_xml like this :

<Extension xmlparser>
    Module           xm_xml
    ParseAttributes  TRUE
</Extension>

But when i restard the NxLog, i get the message :
ERROR invalid keyword: ParseAttributes at /etc/nxlog/nxlog.d/database.conf:9

Matt

mcoussi · November 2, 2022, 3:22pm

Finally,

I gave up the idea to work with XML of database log.

I work now with the AlertLog of the database.

With this configuration, i received all the information of the AlertLog into our Graylog Server.

<Extension gelf>
    Module        xm_gelf
</Extension>


<Input ora-database-alterlog>
    Module          im_file
    File            "/u02/app/oracle/diag/rdbms/database/DATABASE/trace/alert_DATABASE.log"
    Exec            if $raw_event =~ /^202/ drop();		
</Input>

<Output fileout>
    File            'tmp/output'
    Module          om_file
</Output>

<Output etab-graylog>
	Module om_tcp
	Host XX.XX.XX.XX
	Port XXXX
    OutputType    GELF_TCP
</Output>

<Route parse_xml>
    Path            ora-database-alterlog => etab-graylog
</Route>

But the lines isn’t in the order and the message are not complete…

Example in my AlertLog file :

Thread 1 advanced to log sequence 70909 (LGWR switch)
  Current log# 1 seq# 70909 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_1_j0zwh7qz_.log
  Current log# 1 seq# 70909 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_1_j0zwh7s2_.log
ARC1 (PID:139160): Archived Log entry 58751 added for T-1.S-70908 ID 0x564677b3 LAD:1

I have the line in this order in Graylog.

Current log# 1 seq# 70909 mem# 1: /u02/app/oracle/oradata/fra_
ARC1 (PID:139160): Archived Log entry 58751 added for T-1.S-7090
Thread 1 advanced to log sequence 70909 (LGWR switch)
Current log# 1 seq# 70909 mem# 0: /u02/app/oracle/oradata/data

How can force the order and keep all information ?

Thanks for your help,

Matt

mcoussi · November 2, 2022, 4:03pm

Hi,
My problem of message truncated is solved by configured in extension module xm_gelf ShortMessageLength [NXLog Community Edition Reference Manual].

But i still get my problem for the order of message.

Thanks,
Matt

gsmith · November 2, 2022, 9:28pm

Hello,

So you have a multi-line log file, is this correct?

<Extension multiline_parser>
    Module      xm_multiline
    HeaderLine  "---------------"
    EndLine     "END------------"
</Extension>

<Input in>
    Module      im_file
    File        "/var/log/app-multiline.log"
    InputType   multiline_parser
</Input>

Parsing multi-line logs :: NXLog Documentation

I don’t think you need this output.

<Output fileout>
    File            'tmp/output'
    Module          om_file
</Output>

I believe what that does was take your XML log turn it into JSON log and places it in to a file. From there its sent out to the Central log server.

mcoussi · November 3, 2022, 7:08am

Hi @gsmith,

No i use now a log with line by line.

I know that i don’t need this output, it’s just a test to compare the content in graylog and in a file redirected.

<Output fileout>
    File            'tmp/output'
    Module          om_file
</Output>

Now i only have one problem. The problem is the order of the event in Graylog. There are not in order as same as the source logfile.
I have configured an output to send event in an other logfile and in the new log the event are in the same order as the source logfile.

Configuration to the UDP Output Graylog where the event are not in the same order as the source logfile.

<Extension gelf>
    Module        xm_gelf
    ShortMessageLength 200
</Extension>

<Input ora-database-rman>
    Module          im_file
    File            "/oracle/rman/logs/DATABASE_*.log"
        Exec sleep(100);
</Input>

<Output ext-graylog>
        Module om_udp
        Host XX.XX.XX.XX
        Port XXXXX
    OutputType    GELF_UDP
</Output>

<Route database-rman
    Path            ora-database-rman => ext-graylog
</Route>

Configuration to the om_file module local where the event are in the same order as the source logfile.

<Input ora-database-rman>
    Module          im_file
    File            "/oracle/rman/logs/DATABASE_*.log"
</Input>

<Output fileout>
    File            'tmp/output'
    Module          om_file
</Output>

<Route database-rman
    Path            ora-database-rman => fileout
</Route>

Do you have an idea how to get the event in the order to the OUTPUT TCP ?

Thanks for your help,

Matt

gsmith · November 3, 2022, 9:06pm

Hello,

Don’t believe I had that issue yet. I think after reading back over this post, your logs are multi-line.

Example

<Extension multiline_parser>
    Module      xm_multiline
    HeaderLine  "---------------"
    EndLine     "END------------"
</Extension>

<Input in>
    Module      im_file
    File        "/var/log/app-multiline.log"
    InputType   multiline_parser
</Input>

When I grab message from a log file I normally use this setup for my input.

Example /w Explanation.

<Input log_file>
  Module   im_file
  File    "C:\some\path\to\old\file\greg_labtop.log"
  SavePos TRUE //This saves the Posistion after NXLOg restarts or starts. Set to FALSE to scan all of the Log file. When done sending set it back if need be.
  ReadFromLast TRUE //If ReadFromLast is FALSE, the module will read all logs from the file. .
  PollInterval 1
</Input>

That is only configuration I can think of right now.

EDIT: Have you tested Graylog-Sidecar /w FileBeat? That is a good multi-line log shipper.

mcoussi · November 4, 2022, 7:07am

Hi @gsmith,

Now i don’t use the XML trace but the AlterLog of my database.

Here is my logfile :

2022-11-04T07:31:15.307198+01:00
Thread 1 advanced to log sequence 71103 (LGWR switch)
  Current log# 3 seq# 71103 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_3_j0zwh85v_.log
  Current log# 3 seq# 71103 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_3_j0zwh87h_.log
2022-11-04T07:31:15.321369+01:00
ARC2 (PID:139162): Archived Log entry 58945 added for T-1.S-71102 ID 0x564677b3 LAD:1
2022-11-04T07:46:15.981355+01:00
Thread 1 advanced to log sequence 71104 (LGWR switch)
  Current log# 1 seq# 71104 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_1_j0zwh7qz_.log
  Current log# 1 seq# 71104 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_1_j0zwh7s2_.log
2022-11-04T07:46:15.996453+01:00
ARC3 (PID:139164): Archived Log entry 58946 added for T-1.S-71103 ID 0x564677b3 LAD:1

This log is not a multiline, in my configuration i just drop the line where the date is present. I tried your configuration with SavePos/ReadFromLast/ReadFromLast

<Input ora-database-alterlog>
    Module          im_file
    File            "/u02/app/oracle/diag/rdbms/tcarlgm/TCARLGM/trace/alert_TCARLGM.log"
    SavePos         TRUE
    ReadFromLast    TRUE
    PollInterval    1
    Exec  if $raw_event =~ /^202/ drop();
</Input>

<Output ext-graylog>
        Module       om_udp
        Host         XXX.XXX.XXX.XXX
        Port         XXXXX
        OutputType   GELF_UDP
</Output>

<Route database-alertlog
    Path            ora-database-alterlog => ext-graylog
</Route>

And it’s the same, the log in graylog aren’t in the same order.

In my source filelog :

2022-11-04T08:01:52.851581+01:00
Thread 1 advanced to log sequence 71106 (LGWR switch)
  Current log# 3 seq# 71106 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_3_j0zwh85v_.log
  Current log# 3 seq# 71106 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_3_j0zwh87h_.log
2022-11-04T08:01:52.864711+01:00
ARC1 (PID:139160): Archived Log entry 58948 added for T-1.S-71105 ID 0x564677b3 LAD:1

Order in Graylog :

Thread 1 advanced to log sequence 71106 (LGWR switch)
ARC1 (PID:139160): Archived Log entry 58948 added for T-1.S-71105 ID 0x564677b3 LAD:1
Current log# 3 seq# 71106 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_3_j0zwh87h_.log
Current log# 3 seq# 71106 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_3_j0zwh85v_.log

Matt

gsmith · November 4, 2022, 10:46pm

Hey,
This type of log/s unique, I haven’t worked with them before.
Hope you don’t mind. I uploaded your example of the log file in my lab to see if I could mimic this situation, What I found was, and not sure if I did this right but here is what I did.

Created log file with the example you posted, using NXlog to send them in a GELF UDP input 12201

Simple configuration to send these file into Graylog.

<Extension _syslog>
    Module      xm_syslog
</Extension>
<Extension json>
    Module      xm_json
</Extension>
<Extension _gelf>
    Module      xm_gelf
</Extension>


<Input nginx>
      Module im_file
      File "/var/log/test.log"
      SavePos FALSE
      ReadFromLast FALSE
      PollInterval 1
      exec $message = $raw_event;
      #Exec     to_syslog_snare();
 </Input>
#<Input syslog>
#      Module im_file
#      File "/var/log/*.log"
#      SavePos TRUE
#      ReadFromLast TRUE
#      PollInterval 1
#      exec $message = $raw_event;
# </Input>

<Output fileout1>
    Module      om_udp
    Host        8.8.8.8
    Port        12201
    OutputType  GELF
    Exec    $ShortMessage = $raw_event;
    #Exec     to_syslog_snare();
</Output>

Results:

My apologies the naming convention used was from previous lab test, Not sure if this is what your actually getting.

My first attempt to ingest the example log files you had did not look like this, each Message field had a separate line.

This maybe do to how the logs were sent.

mcoussi · November 9, 2022, 2:39pm

Hi @gsmith,

I tried your configuration :

<Extension _syslog>
    Module      xm_syslog
</Extension>
<Extension json>
    Module      xm_json
</Extension>
<Extension _gelf>
    Module      xm_gelf
</Extension>


<Input dblog>
      Module im_file
      File "/u02/app/oracle/diag/rdbms/database/DATABASE/trace/alert_DATABASE.log""
      SavePos FALSE
      ReadFromLast FALSE
      PollInterval 1
      exec $message = $raw_event;
      #Exec     to_syslog_snare();
 </Input>
#<Input syslog>
#      Module im_file
#      File "/var/log/*.log"
#      SavePos TRUE
#      ReadFromLast TRUE
#      PollInterval 1
#      exec $message = $raw_event;
# </Input>

<Output graylog>
    Module      om_udp
    Host        XX.XX.XX.XX
    Port        XXXX
    OutputType  GELF
    Exec    $ShortMessage = $raw_event;
    #Exec     to_syslog_snare();
</Output>


<Route dblog_to_graylog
        Path dblog => graylog
</Route>

But it’s the same the log are not in the order :

My source logfile :

2022-11-09T15:36:44.564933+01:00
Thread 1 advanced to log sequence 71657 (LGWR switch)
  Current log# 2 seq# 71657 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_2_j0zwh7yk_.log
  Current log# 2 seq# 71657 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_2_j0zwh80r_.log
2022-11-09T15:36:44.579200+01:00
ARC1 (PID:2166): Archived Log entry 59499 added for T-1.S-71656 ID 0x564677b3 LAD:1

Log in Graylog :

2022-11-09 15:36:45 +01:00	serv-database
Current log# 2 seq# 71657 mem# 0: /u02/app/oracle/oradata/datafiles_DATABASE/DATABASE/onlinelog/o1_mf_2_j0zwh7yk_.log
2022-11-09 15:36:45 +01:00	serv-database
Current log# 2 seq# 71657 mem# 1: /u02/app/oracle/oradata/fra_DATABASE/DATABASE/onlinelog/o1_mf_2_j0zwh80r_.log
2022-11-09 15:36:45 +01:00	serv-database
ARC1 (PID:2166): Archived Log entry 59499 added for T-1.S-71656 ID 0x564677b3 LAD:1
2022-11-09 15:36:45 +01:00	serv-database
Thread 1 advanced to log sequence 71657 (LGWR switch)
2022-11-09 15:36:45 +01:00	serv-database
2022-11-09T15:36:44.564933+01:00
2022-11-09 15:36:45 +01:00	serv-database
2022-11-09T15:36:44.579200+01:00

Matt

system · November 23, 2022, 2:39pm

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Forward logfile to graylog Graylog Central (peer support)	4	2424	November 4, 2022
Send Oracle logs to Graylog Graylog Central (peer support) sidecar , nxlog , filebeat-linux , filebeat-windows , nodatanx , multi-linenx , nosendlogfblx	7	5660	July 7, 2017
Best Method for Forwarding A Variety of Application ".Log" Files Graylog Add-ons sidecar , nxlog , filebeat-windows , winlogbeat , nodatanx	3	3111	April 10, 2017
NXlog rsyslog template Graylog Central (peer support)	2	1558	November 13, 2017
NXLog to Gray Log Graylog Central (peer support) sidecar , nxlog , nodatanx	6	1235	February 4, 2020

Format XML logfile and forward to rsyslog

Related topics