Oracle telegraf to influxdb

Hello Community
first… I’m Luis from Barcelona, I’m new in this world…and I start working with telegraf + influxdb + chronograf last week.

As Oracle dba, I would like to get metrics with this amazing system.

So, I create in my local PC the environment… I have influxdb working, telegraf and chronograf.

I use inputs.exec in order to grab Oracle data and send it throught the system

The .sh script is:

#!/bin/env bash

su - oracle -c "
export ORACLE_HOME=/home/oracle/app/oracle/product/122/db_home;export ORACLE_SID=orcl;
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus -s ldiaz/xxxx <<EOF
SET PAGES 0
set heading off
SET FEEDBACK OFF
select ‘Active_session’||’ '||‘count=’||count(1) cnt from v\$session where status=‘ACTIVE’ and type<>‘BACKGROUND’;
EXIT;
EOF
"
If I try with Telegraf I get the following output:

telegraf --config /etc/telegraf/telegraf.conf --test --input-filter=exec
2019-11-05T15:41:08Z I! Starting Telegraf 1.12.3

Active_session,host=mipc count=1 1572968469000000000

Look fine, I have the 3 fields expected by influxdb
But If I take a look to the /var/log/message log I have this:

Nov 5 16:42:20 mipc telegraf: 2019-11-05T15:42:20Z E! [inputs.exec] Error in plugin: exec: fork/exec /home/oracle/scripts/events.sh: permission denied for command ‘/home/oracle/scripts/events.sh’:

Can anyone tell me what is the problem?
Telegraf have been installed as root user
This is the permissions of the events.sh scripts

-rwxrwxrwx 1 telegraf telegraf 366 nov 5 16:10 events.sh

Many thanks

@luisete2106,
Hello Luis (from Barcelona! <3 ),
Thanks for your question and welcome!
So you’re unsuccessful in writing points to InfluxDB with Telegraf exec plugin? Have you tried setting debug = true on your telegraf plugin?
Can you please share your telegraf config? That might help in debugging.

Hi Anais
Whooww , my daugther’s name is Anaïs too !!

Well, I can’t attach file, I have this message:
“Sorry, new users can not upload attachments.”

But anyway, this is the bashs script:

#!/bin/env bash

export ORACLE_HOME=/home/oracle/app/oracle/product/122/db_home;export ORACLE_SID=orcl;
export PATH=\$PATH:\$ORACLE_HOME/bin

/home/oracle/app/oracle/product/122/db_home/bin/sqlplus -s user/xxxx <<EOF
SET PAGES 0
set heading off
SET FEEDBACK OFF
select replace(ses.event,' ','_')||' '||'Count='||count(1) from v\$session ses inner join v\$session_wait sw ON ses.sid=sw.sid and sw.wait_class<>'idle' where ses.event is not null group by ses.event;
EXIT;
EOF
indent preformatted text by 4 spaces

The output of the query from database is:

REPLACE(SES.EVENT,’’,’_’)||’’||‘COUNT=’||COUNT(1)
wait_for_unread_message_on_broadcast_channel Count=1
Data_Guard:_Timer Count=1
heartbeat_redo_informer Count=1
pman_timer Count=1
pmon_timer Count=2
rdbms_ipc_message Count=15
watchdog_main_loop Count=2
smon_timer Count=1
LGWR_worker_group_idle Count=2

This code runs fine with telegraf and infuxdb.
But, All the rows returned by this query create a MEASURMENT into influx… How can I create a category in order to have, from the data explore tab, Oracle category, as I have postgres or system?

The query generated is:

SELECT mean(“Count”) AS “mean_Count” FROM “telegraf”.“autogen”.“Data_Guard:_Gap_Manager” WHERE time > now() - 1h AND “host”=‘mipc’ GROUP BY time(:interval:) FILL(null)

Hope you can help on this
Many thanks

Look Anais

from influxdb, I connect with telegraf DB and show measurements:

show measurements
name: measurements
name

AQPC_idle
DIAG_idle_wait
Data_Guard:_Gap_Manager
Data_Guard:_Timer
LGWR_all_worker_groups
LGWR_any_worker_group
LGWR_worker_group_idle
LGWRallworkergroups
LGWRanyworkergroup
OFS_idle
Oracle_EventsAQPC_idle
Oracle_EventsDIAG_idle_wait
Oracle_EventsData_Guard:_Gap_Manager
Oracle_EventsData_Guard:_Timer
Oracle_EventsLGWR_worker_group_idle
Oracle_EventsOFS_idle
Oracle_EventsPGA_memory_operation
Oracle_EventsSQLNet_message_from_client
Oracle_EventsSpace_Manager:_slave_idle_wait
Oracle_EventsStreams_AQ:_qmn_coordinator_idle_wait
Oracle_EventsStreams_AQ:_qmn_slave_idle_wait
Oracle_EventsStreams_AQ:_waiting_for_time_management_or_cleanup_tasks
Oracle_EventsVKRM_Idle
Oracle_EventsVKTM_Logical_Idle_Wait
Oracle_Events_AQPC_idle
Oracle_Events_DIAG_idle_wait
Oracle_Events_Data_Guard:_Gap_Manager
Oracle_Events_Data_Guard:_Timer
Oracle_Events_LGWR_worker_group_idle
Oracle_Events_OFS_idle
Oracle_Events_PGA_memory_operation
Oracle_Events_SQL
Net_message_from_client
Oracle_Events_Space_Manager:_slave_idle_wait
Oracle_Events_Streams_AQ:_qmn_coordinator_idle_wait
Oracle_Events_Streams_AQ:_qmn_slave_idle_wait
Oracle_Events_Streams_AQ:_waiting_for_time_management_or_cleanup_tasks
Oracle_Events_VKRM_Idle
Oracle_Events_VKTM_Logical_Idle_Wait
Oracle_Events_heartbeat_redo_informer
Oracle_Events_lreg_timer
Oracle_Events_pman_timer
Oracle_Events_pmon_timer
Oracle_Events_rdbms_ipc_message
Oracle_Events_smon_timer
Oracle_Events_wait_for_unread_message_on_broadcast_channel
Oracle_Events_watchdog_main_loop
Oracle_Eventsheartbeat_redo_informer
Oracle_Eventslreg_timer
Oracle_Eventspman_timer
Oracle_Eventspmon_timer
Oracle_Eventsrdbms_ipc_message
Oracle_Eventssmon_timer
Oracle_Eventswait_for_unread_message_on_broadcast_channel
Oracle_Eventswatchdog_main_loop
PGA_memory_operation
PGAmemoryoperation
SQL*Net_message_from_client
Space_Manager:_slave_idle_wait
Streams_AQ:_load_balancer_idle
Streams_AQ:_qmn_coordinator_idle_wait
Streams_AQ:_qmn_slave_idle_wait
Streams_AQ:_waiting_for_time_management_or_cleanup_tasks
Usuarios_Oracle=CTXSYS
Usuarios_Oracle=DBSFWUSER
Usuarios_Oracle=DBSNMP
Usuarios_Oracle=DIP
Usuarios_Oracle=DVF
Usuarios_Oracle=DVSYS
Usuarios_Oracle=GGSYS
Usuarios_Oracle=GSMADMIN_INTERNAL
Usuarios_Oracle=GSMCATUSER
Usuarios_Oracle=GSMUSER
Usuarios_Oracle=LBACSYS
Usuarios_Oracle=LDIAZ
Usuarios_Oracle=MDDATA
Usuarios_Oracle=MDSYS
Usuarios_Oracle=OJVMSYS
Usuarios_Oracle=OLAPSYS
Usuarios_Oracle=ORACLE_OCM
Usuarios_Oracle=ORDDATA
Usuarios_Oracle=ORDPLUGINS
Usuarios_Oracle=ORDSYS
Usuarios_Oracle=OUTLN
Usuarios_Oracle=REMOTE_SCHEDULER_AGENT
Usuarios_Oracle=SI_INFORMTN_SCHEMA
Usuarios_Oracle=SPATIAL_CSW_ADMIN_USR
Usuarios_Oracle=SYS
Usuarios_Oracle=SYS$UMF
Usuarios_Oracle=SYSBACKUP
Usuarios_Oracle=SYSDG
Usuarios_Oracle=SYSKM
Usuarios_Oracle=SYSRAC
Usuarios_Oracle=SYSTEM
Usuarios_Oracle=WMSYS
Usuarios_Oracle=XDB
Usuarios_Oracle=XS$NULL
VKRM_Idle
VKTM_Logical_Idle_Wait
control_file_parallel_write
controlfileparallelwrite
cpu
db_file_async_I/O_submit
db_file_scattered_read
db_file_sequential_read
dbfileasyncI/Osubmit
dbfilescatteredread
dbfilesequentialread
direct_path_read
directpathread
disk
diskio
event
external_table_read
externaltableread
heartbeat_redo_informer
jobq_slave_wait
kernel
latch free
latch:_shared_pool
latch_free
latchfree
log_file_parallel_write
log_file_sync
logfileparallelwrite
logfilesync
lreg_timer
mem
oracle_thread_bootstrap
oraclethreadbootstrap
os thread creation
os_thread_creation
osthreadcreation
pman_timer
pmon_timer
postgresql
processes
rdbms_ipc_message
session_id
smon_timer
swap
system
wait_for_unread_message_on_broadcast_channel
watchdog_main_loop

Hi luisete ,
Can you show the exec input plugin as well telegraf configuration file.

Hi luise,
I am struggling from past 2 week write the telegraf input plugin for parsing the oracle alert log file.
I already wrote the script file but I am not able to create the measurement.
Can you please share or attached your telegraf configuration file .
It will match with my telegraf file and I will see where I did mistake.

Who is the owner/group of the script file? If I remember correctly, there is a user telegraf that runs the telegraf process. The script file would have to have the executable bit set chmod +x <script>.sh and the script file would have to be either owned by the telegraf user or the owner of the script file would have to be in the same UNIX group as the telegraf user.

HTH,
dg

Hello

yes, I attach the telegraf.conf file.

@Susil, remember that you can test your telegraf config with this code:

telegraf --config /etc/telegraf/telegraf.conf --test --input-filter=exec --debug

You should have an output like this:

My query returns this

Data_Guard:_Timer Count=1

As you see, Telegraf add the host and a timestamp

Data_Guard:_Timer,host=mipc Count=1 1573244169000000000

This format is good to create measurements into influxdb

2019-11-08T20:16:09Z I! Starting Telegraf 1.12.3

2019-11-08T20:16:09Z D! [agent] Initializing plugins

telegraf.conf.gz (46.5 KB)

Hello David

Yes, as you said, I adapt the permissions to the .sh file as this:

-rwxrwxrwx 1 telegraf telegraf 478 nov 7 21:38 events.sh

Maybe I grant too much… :slight_smile:

Hello luise,
Is it required to give the measurement name as

measurement name suffix (for separating different commands)

name_suffix = “_mycollector”

Hello Susil

Mmm look

I have this code for the exec plugin:

# Read metrics from one or more commands that can output to stdout

[[inputs.exec]]

## Commands array

commands = ["/home/oracle/scripts/events.sh"]

Hi Luise,
This is my pluig-in
[[inputs.exec]]

Commands array

commands = [“sh /etc/telegraf/scripts/oracle.sh”]

Timeout for each command to complete.

timeout = “1m”

Data format to consume.

Each data format has its own unique set of configuration options, read

more about them here:

https://github.com/influxdata/telegraf/blob/master/docs/DATA_FORMATS_INPUT.md

data_format = “influx”

measurement name suffix (for separating different commands)

name_override = “oracle_logs”
But the issue is measurement is not creating.

sudo -u telegraf – telegraf --input-filter=exec --test by using this command it is showing this error message

Hello Susi

Execute this to check:

telegraf --config /etc/telegraf/telegraf.conf --test --input-filter=exec --debug

And check the telegraf log too.

In my case, the telegraf command send this:

telegraf --config /etc/telegraf/telegraf.conf --test --input-filter=exec --debug
2019-11-09T19:25:17Z I! Starting Telegraf 1.12.3

wait_for_unread_message_on_broadcast_channelOracle_,host=mipc Count=1 1573327517000000000
Data_Guard:TimerOracle,host=mipc Count=1 1573327517000000000
heartbeat_redo_informerOracle_,host=mipc Count=1 1573327517000000000
pman_timerOracle_,host=mipc Count=1 1573327517000000000

Can you paste here the oracle.sh script?

By executing your command it is showing like this

oracle.txt (2.4 KB)

Ok, from your command, the error means that the directory where you try to write the log must have write permissions for user telegraf.

you can add telegraf user into oinstall group.

Susil

I try your script in my local orcl instance. The script don’t output data…

I don’t know if it’s for this, influxdb is not creating any measurement.

But… I’m not sure

Maybe you could try with a more simple scripts, like mine, I paste it again:

#!/bin/env bash

export ORACLE_HOME=/home/oracle/app/oracle/product/122/db_home;export ORACLE_SID=orcl;
export PATH=$PATH:$ORACLE_HOME/bin

/home/oracle/app/oracle/product/122/db_home/bin/sqlplus -s user/xxxx <<EOF
SET PAGES 0
set heading off
SET FEEDBACK OFF
select replace(ses.event,’ ‘,’_’)||’ '||‘Count=’||count(1) from v$session ses inner join v$session_wait sw ON ses.sid=sw.sid and sw.wait_class<>‘idle’ where ses.event is not null group by ses.event;
EXIT;
EOF

Luise
Is any mistake in script file?