Hi Everyone,
I developed a script file for parsing the oracle alert log file but the issue is how to set up the telegraf configuration file in linux machine and which plugin I will use for parsing the oracle log file.
Anyone give the sample telegraf configuration file for Linux machine.
Hi. Do you mean, like a log parser plugin
If you want to use the script you wrote you should probably look at the the exec plugin
HI philb,
I am using only exec plugin…
Script file :
adrcihome=“diag/rdbms/orcl/orcl”
logfile="/tmp/adrci.log"
incident=""
alert=""
alertdescription=""
#unixtime=""
#oldunixtime=""
# get ORA messages from the last 5 minutes
$adrci -exec “set\ home\ $adrcihome;show\ alert\ -p\ “message_text like ‘%ORA-%’\ and\ originating_timestamp\ >\ systimestamp-1/288” -term;” > $logfile
# get ORA messages from the last day
# $adrci -exec “set\ home\ $adrcihome;show\ alert\ -p\ “message_text like ‘%ORA-%’\ and\ originating_timestamp\ >\ systimestamp-1” -term;” > $logfile
# convert line endings (if logfile was created on windows)
sed -i ‘s/\r$//’ $logfile
while read line; do
if [[ -z “$line” ]]; then
continue ;
else
# check if line contains datetime
regex=’^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}.[0-9]{6} [±][0-9]{2}:[0-9]{2}’
if [[ $line =~ regex ]]; then # the alert time and convert it nanosecond-precision Unix time unixtime=(date -d “$line” +"%s%N")
# reset variables
incident=""
alert=""
alertdescription=""
else
if [[ $unixtime != “” ]]; then
# check if line contains incident number
if [[ line =~ (\(incident=(.*?)\)) ]]; then incident=(echo “${BASH_REMATCH}” | grep -o ‘[0-9]+’)
fi
# check if ORA message
if [[ $line =~ (ORA-[0-9]{5}) ]]; then
# get the ORA error string
alert=$(echo "${BASH_REMATCH}")
# get the alert description; escape special characters
#alertdescription=$(echo $line | grep $alert | cut -d\ -f2- | sed 's/ /\\ /g' | sed 's/,/\\,/g' | sed 's/\"/\\\"/g')
alertdescription=$(echo $line | grep $alert | cut -d\ -f2- | sed 's/ /_/g' | sed 's/,//g' | sed 's/\"/\\\"/g')
fi
fi
fi
fi
if [[ $unixtime != “” && $incident != “” && $alert != “” ]]; then
echo “oracle_alert_logs alert=”$alert",alertdescription="$alertdescription",incident=$incident $unixtime"
else
if [[ $unixtime != “” && $incident == “” && $alert != “” ]]; then
echo “oracle_alert_logs alert=”$alert",alertdescription="$alertdescription",incident=0 $unixtime"
fi
fi
#if [[ $unixtime != “” && $unixtime != $oldunixtime ]]; then
# oldunixtime=$unixtime
#fi
done <$logfile
# delete the adrci log file
rm $logfile
AgentBase Configuration file :
###############################################################################
# Rockwell FTAM - Data Collection - Telegraf Agent Base Configuration
###############################################################################
# This is the central agent base configuration used by all Telegraf agents.
# Remark: Telegraf input settings must not be configured here. They are located in the component specific configuration files.
###############################################################################
# Installation
###############################################################################
# Windows service:
# 1.) Extract Telegraf to: c:\Rockwell\FTApplicationMonitoring\telegraf
# 2.) Copy this file to: c:\Rockwell\FTApplicationMonitoring\telegraf
# 2.1.) Copy the desired component metrics configurations to: c:\Rockwell\FTApplicationMonitoring\telegraf\metrics
# 3.) Create Telegraf service by running this command as admin:
# c:\Rockwell\FTApplicationMonitoring\telegraf\telegraf.exe --service install --config c:\Rockwell\FTApplicationMonitoring\telegraf\AgentBaseConfiguration-telegraf.conf --config-directory c:\Rockwell\FTApplicationMonitoring\telegraf\metrics
# 4.) Start Telegraf service by running this command as admin: c:\Rockwell\FTApplicationMonitoring\telegraf\telegraf.exe --service start
# Linux service:
# TASK: Describe
# Command line testing:
# Use this command: telegraf -config AgentBaseConfiguration-telegraf.conf -config-directory YourConfigDirectoryMatchingTheComponentsOfTheServer
# E.g. c:\Rockwell\FTApplicationMonitoring\telegraf\telegraf.exe --config c:\Rockwell\FTApplicationMonitoring\telegraf\AgentBaseConfiguration-telegraf.conf --config-directory c:\Rockwell\FTApplicationMonitoring\telegraf\metrics
###############################################################################
# AGENT
###############################################################################
# Telegraf is entirely plugin driven. All metrics are gathered from the
# declared inputs, and sent to the declared outputs.
# Plugins must be declared in here to be active.
# To deactivate a plugin, comment out the name and any variables.
# Use ‘telegraf -config telegraf.conf -test’ to see what metrics a config
# file would generate.
# Environment variables can be used anywhere in this config file, simply prepend
# them with $. For strings the variable must be within quotes (ie, “$STR_VAR”),
# for numbers and booleans they should be plain (ie, $INT_VAR, $BOOL_VAR)
# Global tags can be specified here in key=“value” format.
[global_tags]
# dc = “us-east-1” # will tag all metrics with dc=us-east-1
# rack = “1a”
## Environment variables can be used as tags, and throughout the config file
# user = “$USER”
# Configuration for telegraf agent
[agent]
## Default data collection interval for all inputs
interval = “10s”
## Rounds collection interval to ‘interval’
## ie, if interval=“10s” then always collect on :00, :10, :20, etc.
round_interval = true
## Telegraf will send metrics to outputs in batches of at most
## metric_batch_size metrics.
## This controls the size of writes that Telegraf sends to output plugins.
metric_batch_size = 1000
## For failed writes, telegraf will cache metric_buffer_limit metrics for each
## output, and will flush this buffer on a successful write. Oldest metrics
## are dropped first when this buffer fills.
## This buffer only fills when writes fail to output plugin(s).
metric_buffer_limit = 10000
## Collection jitter is used to jitter the collection by a random amount.
## Each plugin will sleep for a random time within jitter before collecting.
## This can be used to avoid many plugins querying things like sysfs at the
## same time, which can have a measurable effect on the system.
collection_jitter = “0s”
## Default flushing interval for all outputs. Maximum flush_interval will be
## flush_interval + flush_jitter
flush_interval = “10s”
## Jitter the flush interval by a random amount. This is primarily to avoid
## large write spikes for users running a large number of telegraf instances.
## ie, a jitter of 5s and interval 10s means flushes will happen every 10-15s
flush_jitter = “0s”
## By default or when set to “0s”, precision will be set to the same
## timestamp order as the collection interval, with the maximum being 1s.
## ie, when interval = “10s”, precision will be “1s”
## when interval = “250ms”, precision will be “1ms”
## Precision will NOT be used for service inputs. It is up to each individual
## service input to set the timestamp at the appropriate precision.
## Valid time units are “ns”, “us” (or “µs”), “ms”, “s”.
precision = “”
## Logging configuration:
## Run telegraf with debug log messages.
debug = false
## Run telegraf in quiet mode (error log messages only).
quiet = false
## Specify the log file name. The empty string means to log to stderr.
logfile = “/var/lib/RockwellWork/telegraf/telegraf.log”
## Override default hostname, if empty use os.Hostname()
hostname = “”
## If set to true, do no set the “host” tag in the telegraf agent.
omit_hostname = false
###############################################################################
# OUTPUTS
###############################################################################
# Configuration for sending metrics to InfluxDB
[[outputs.influxdb]]
## The full HTTP or UDP URL for your InfluxDB instance.
urls = [“[http://nvussjc-ftam5:8086](http://nvussjc-ftam5:8086/)”] ##Later we will change as MONITORING_SERVER
## The target database for metrics; will be created as needed.
database = “server_logs” # default naming convention
## If true, no CREATE DATABASE queries will be sent. Set to true when using
## Telegraf with a user without permissions to create databases or when the
## database already exists.
# skip_database_creation = false
## Name of existing retention policy to write to. Empty string writes to
## the default retention policy. Only takes effect when using HTTP.
retention_policy = “autogen”
## Write consistency (clusters only), can be: “any”, “one”, “quorum”, “all”.
## Only takes effect when using HTTP.
# write_consistency = “any”
## Timeout for HTTP messages.
# timeout = “5s”
## HTTP Basic Auth
# username = “telegraf”
# password = “metricsmetricsmetricsmetrics”
## HTTP User-Agent
# user_agent = “telegraf”
## UDP payload size is the maximum packet size to send.
# udp_payload = “512B”
## Optional TLS Config for use on HTTP connections.
# tls_ca = “/etc/telegraf/ca.pem”
# tls_cert = “/etc/telegraf/cert.pem”
# tls_key = “/etc/telegraf/key.pem”
## Use TLS but skip chain & host verification
# insecure_skip_verify = false
## HTTP Proxy override, if unset values the standard proxy environment
## variables are consulted to determine which proxy, if any, should be used.
# http_proxy = “[http://corporate.proxy:3128](http://corporate.proxy:3128/)”
## Additional HTTP headers
# http_headers = {“X-Special-Header” = “Special-Value”}
## HTTP Content-Encoding for write request body, can be set to “gzip” to
## compress body or “identity” to apply no encoding.
# content_encoding = “identity”
## When true, Telegraf will output unsigned integers as unsigned values,
## i.e.: “42u”. You will need a version of InfluxDB supporting unsigned
## integer values. Enabling this option will result in field type errors if
## existing data has been written.
# influx_uint_support = false
###############################################################################
# INPUT
###############################################################################
## Oracle Log File Plugin
[[inputs.exec]]
commands = [“sh /etc/telegraf/scripts/oracle.sh”]
data_format = “influx”
interval = “1m”
name_suffix = “oracle_logs”
Can anyone suggest me I did mistake.
I am struggling this issue from past 2 week.
Hi, the formatting makes it a little difficult to read. A couple of questions.
- this script runs on a linux server, is oracle installed on the linux server?
- if you run the script manually, does it collect the metrics?
- does the script run with telegraf. Meaning, when run from the exec plugin does it run but fails to insert data. if so what is the output from the log files?
- if it doesn’t run through the exec plugin, are there any errors? Does telegraf have permission to run your script?
- what format is the data you want to insert into influx? JSON or influx line protocol?
Could you show an example of the output you expect in influx? the fields, tags and values you expect to be sent in?
- Script is running in Linux machine and oracle also installed in Linux machine.
- If a run the script by using command : sudo -u telegraf /etc/telegraf/scripts/oracle.sh .
It is showing this error :
[root@nvussjc-ftam5 ~]# sudo -u telegraf /etc/telegraf/scripts/oracle.sh
/etc/telegraf/scripts/oracle.sh: line 16: $logfile: ambiguous redirect
sed: no input files
/etc/telegraf/scripts/oracle.sh: line 68: $logfile: ambiguous redirect
rm: missing operand - data format is influx
It looks like it’s possibly your script that is the issue. If you run the script as your user, not with telegraf - are there any errors?
The ambigous redirect (i think) can be related to spaces, blank values or in correct line endings or when the redirect itself contains a redirect.
If the variable $logfile is on line 16 and 68 in your script then could you wrap the variable in double quotes?
Hi philp,
Now script is running fine by using sudo -u telegraf /etc/telegraf/scripts/oracle.sh.
but when we connect with influxdb measurement is not creating.
It is showing
[inputs.exec] Error in plugin: exec: exit status 1 for command ‘sh /etc/telegraf/scripts/oracle.sh’: /etc/telegraf/scripts/oracle.sh: line 16: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log: Permission denied…
error
And another doubt is in the script file we have to mention the alert log file location ?
Like
#!/bin/bash
adrci="/home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/adrci"
adrcihome="/home/oracle/app/oracle/diag/rdbms/orcl/orcl"
logfile="/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log"
Does telegraf/the script have permission to read the log file?
Yes. I have gave permission and when I run the scripts its showing the output also but the issue is measurements is not creating
If the exec plugin is failing, then there won’t be any data.
How does your script insert the data? I’m thinking…
- the exec plugin fails, meaning no data is inserted.
- If you were running the script manually, you should have some data. If this isn’t the case then Influx might have an issue with the format of the data you’re sending.
Your config states the “influx” data protocol, so the output from the script needs to match Influx’s line protocl.
telegraf.txt (8.0 KB) oracle.txt (2.4 KB)
I attached telegraf configuration file and oracle script file.
could you please check where I did mistake?
why measurement is not created?
Hi philb,
Now Iam running the script by using this command
telegraf --config /etc/telegraf/telegraf.conf --test --input-filter=exec --debug
and the out also showing right now but the issue is measurement is not craeted
HI Susil,
sorry for not coming back to you. I’m at work and been struggling for time. I’ll try and get back to you later today when I’m on lunch.
Hi philb,
Now the script is working fine and the data also coming into influxdb successfully.
Gret stuff @Susil, what was the cause?
I was thinking the data formatting, i couldn’t see in your script where you were formatting the data for Influx’s line protocol
Be good to know what the cause was though 
