Oracle DB alert log config error on Logstash 6.4


(sandeep) #1

Sending Logstash logs to D:/Hexa_Software/ELK/logstash-6.4.2/logs which is now configured via log4j2.properties
[2018-10-11T11:51:02,241][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2018-10-11T11:51:02,780][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"6.4.2"}
[2018-10-11T11:51:03,253][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, { at line 10, column 10 (byte 152) after filter {\n \n# Join lines based on the time\nmultiline", :backtrace=>["D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/compiler.rb:41:in compile_imperative'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/compiler.rb:49:incompile_graph'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2486:inmap'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:ininitialize'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/pipeline.rb:22:in initialize'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/pipeline.rb:90:ininitialize'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/pipeline_action/create.rb:38:in execute'", "D:/Hexa_Software/ELK/logstash-6.4.2/logstash-core/lib/logstash/agent.rb:309:inblock in converge_state'"]}
[2018-10-11T11:51:03,543][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}

====================== Config File================
input {
file {
path => "D:/Hexa_Software/ELK/logstash-6.4.2/bin/Test/alert_spf.log"
}
}

filter {

Join lines based on the time

multiline {
pattern => "%{DAY} %{MONTH} %{MONTHDAY} %{TIME} %{YEAR}"
negate => true
what => "previous"
}

Create new field: oradb_status: starting,running,shutdown

if [message] =~ /Starting ORACLE instance/ {
mutate {
add_field => [ "oradb_status", "starting" ]
}
} else if [message] =~ /Instance shutdown complete/ {
mutate {
add_field => [ "oradb_status", "shutdown" ]
}
} else {
mutate {
add_field => [ "oradb_status", "running" ]
}
}

Search for ORA- and create field if match

if [message] =~ /ORA-/ {
grok {
match => [ "message","(?ORA-[0-9]*)" ]
}
}

Extract the date and the rest from the message

grok {
match => [ "message","%{DAY:day} %{MONTH:month} %{MONTHDAY:monthday} %{TIME:time} %{YEAR:year}(?<log_message>.*$)" ]
}

mutate {
add_field => {
"timestamp" => "%{year} %{month} %{monthday} %{time}"
}
}

replace the timestamp by the one coming from the alert.log

date {
locale => "en"
match => [ "timestamp" , "yyyy MMM dd HH:mm:ss" ]
}

replace the message (remove the date)

mutate { replace => [ "message", "%{log_message}" ] }

mutate {
remove_field => [ "time" ,"month","monthday","year","timestamp","day","log_message"]
}

}

output {
elasticsearch {
hosts => ["http://XXXXX:9200"]
index => "oracle-%{+YYYY.MM.dd}"
}
stdout { codec => rubydebug }
}


(Makara) #2

Logstash combines all the files in your config directory into a single file. When there's an error, you're getting line and position information from the merged config file.

Run the following command to combine all conf file into a single file and then check line number 10.

cat /etc/logstash/conf.d/* > /tmp/single.conf

(sandeep) #3

I am using windows, please help me out on this , I am every new to this tools.


(Makara) #4

Use type command to combine all the conf files into a single file.


(sandeep) #5

following is my DB alert log format

Thu Aug 09 12:14:47 2018
Adjusting the default value of parameter parallel_max_servers
from 320 to 231 due to the value of parameter processes (300)
Starting ORACLE instance (normal) (OS id: 14860)
Thu Aug 09 12:14:47 2018
CLI notifier numLatches:13 maxDescs:519
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 8
Number of processor cores in the system is 4
Number of processor sockets in the system is 1
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\oracle\product\12.1.0\dbhome_1\RDBMS
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
Windows NT Version V6.2
CPU : 8 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:26393M/32656M, Ph+PgF:28812M/37776M
Using parameter settings in client-side pfile D:\APP\ORACLE\CFGTOOLLOGS\DBCA\SPF\INITSPFTEMPOMF.ORA on machine IN-PPM2968
System parameters with non-default values:
processes = 300
sga_target = 1520M
control_files = "D:\APP\ORACLE\CFGTOOLLOGS\DBCA\SPF\TEMPCONTROL.CTL"
db_block_size = 8192
compatible = "12.1.0.2.0"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "ingrnet.com"
dispatchers = "(PROTOCOL=TCP) (SERVICE=SPFXDB)"
audit_file_dest = "D:\APP\ORACLE\ADMIN\SPF\ADUMP"
audit_trail = "DB"
db_name = "seeddata"
db_unique_name = "SPF"
open_cursors = 300
star_transformation_enabled= "TRUE"
pga_aggregate_target = 504M
_diag_hm_rc_enabled = FALSE
diagnostic_dest = "D:\APP\ORACLE"
enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Thu Aug 09 12:15:08 2018
PMON started with pid=2, OS id=16248
Starting background process PSP0
Starting background process VKTM
Thu Aug 09 12:15:09 2018
PSP0 started with pid=3, OS id=9372
Thu Aug 09 12:15:11 2018
VKTM started with pid=4, OS id=16124 at elevated (Time Critical) priority
Thu Aug 09 12:15:11 2018
VKTM running at (5)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
Starting background process MMAN
Thu Aug 09 12:15:12 2018
MMAN started with pid=6, OS id=2692
Starting background process DIAG
Thu Aug 09 12:15:12 2018
DIAG started with pid=7, OS id=12888
Thu Aug 09 12:15:12 2018
GEN0 started with pid=5, OS id=10644
Starting background process DBRM
Thu Aug 09 12:15:13 2018
DBRM started with pid=8, OS id=15156
Starting background process VKRM
Starting background process DIA0
Thu Aug 09 12:15:13 2018
DIA0 started with pid=11, OS id=12600
Thu Aug 09 12:15:13 2018
VKRM started with pid=10, OS id=9920
Starting background process DBW0
Starting background process LGWR
Thu Aug 09 12:15:14 2018
LGWR started with pid=12, OS id=6416
Thu Aug 09 12:15:14 2018
DBW0 started with pid=9, OS id=10720
Starting background process CKPT
Thu Aug 09 12:15:15 2018
CKPT started with pid=13, OS id=12944
Starting background process SMON
Thu Aug 09 12:15:15 2018
SMON started with pid=15, OS id=2136
Starting background process RECO


(Makara) #6

Hi
Sandeep, Please check line number 10 of the logstash config file after combining all the config files into a single file using type command.


(sandeep) #7

multiline { -----Line number 10 and following is the rest of code.

pattern => "%{DAY} %{MONTH} %{MONTHDAY} %{TIME} %{YEAR}"
negate => true
what => "previous"

}

Note: I am using only one config file and i have posted above, if you want i can re-post it


(sandeep) #8

Any one Help me on this issue


(system) #9

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