Archive
Highlighted

Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Communicator

Hello,

I'd like to insert or update my table on SQL Server with using search results through Splunk DB Connect.

I defined the following SQL Query for Database Lookups settings.


INSERT INTO table_name (column01, column02) VALUES ($value1$, $value2$)

But I failed.
So, I want to use a function like "mysqloutput" provided by "MySQLConnector".

If not, I would appreciate your alternative ways to update my DB tables.

Thank you.

Tags (2)
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

SplunkTrust
SplunkTrust

The DB Connect app comes with a similar command, dboutput. There appears to be little documentation though 😞 maybe you can gather at least some insight from the dboutput.conf.spec file.

View solution in original post

Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Communicator

Thank you for your quick answer. I'll try to use "dboutput".

0 Karma
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Engager

Hi Suda,
Please can you confirm whether you got the 'dboutput' command to work? I would also hugely appreciate an example of what your search and 'dboutput' statement looked like. I am very new to splunk and feeling my way through the process of getting some data out of splunk and into a MSSQL database.

Thanks

Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Communicator

Yes, of cource. I suceeded to use 'dboutput'.
Before I test 'dboutput', I create my test table which has 2 columns; "source" and "sourcetype".
I execute the following search command.
index=_internal | dedup source sourcetype | head 5 | table source sourcetype | dboutput type=insert database=MsSQL table=dbx source sourcetype
I can confirm 5 new entries on my MS SQL Server 2008.

I hope it helps you.

0 Karma
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Communicator

I fail to use 'dboutput', when I specify my stanza defined in dbx/local/dboutput.conf.

0 Karma
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Path Finder

I think .. "mysqloutput" command in MySQLConnector App have some issue.

        1.  just put data into MYSQL DB table..
        2.  it can not control specfic field value using primary key.. 

` such as "UPDATE OUTPUT set count=count('10') where IP='120.191.60.130'"

As a Result, It can not control Key value.. using MySQLConnector, DBx App ..

0 Karma
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Communicator

Could you check 'README/dboutput.conf.spec'?
I believe you can control how 'dboutput' works with your specified 'dboutput.conf' configurations.
What is the key field? How to insert? How to update? You can control them, I believe.

0 Karma
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Explorer

Can dboutput be used for textbox values? Say if I have a DB table mytable with 3 columns A, B , C and I have 3 text fields on my splunk dashboard panel can I save the user entries in these text boxes to SQL database table similar to insert into mytable(A,B,C) values('text1','text2','text3'). Any help would be really appreciated!

0 Karma
Highlighted

Re: Can I insert/update my DB table with search result fields value through Splunk DB Connect?

Explorer

Good morning,

Following the above example I was able to write to my Oracle database, into the test table: dbx, using command:

index=internal | dedup source sourcetype | head 5 | table source sourcetype | dboutput type=insert database=CertlabDMZViewer1 table=dbx source sourcetype

But I would like to specify different tables within the same Oracle schema, and insert into these based on the dboutput.conf.spec [] value. My understanding after looking at the spec, is that I can created different [] sections, each tied to different ables. So I updated my dboutput.conf to include:

[testdbxinsert]
database=Certlab_DMZViewer1
table=dbx
mode=insert
fields=source,sourcetype

sql

advanced=false

But My question is: how do I reference the 'testdbxinsert' when calling the command: dboutput from the search web gui? I do not see how to reference the specific name section in that command. Advice appreciated.

Thanks,
M