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

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

Suda — Fri, 14 Dec 2012 08:02:00 GMT

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.

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

martin_mueller — Fri, 14 Dec 2012 08:57:17 GMT

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.

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

Suda — Fri, 14 Dec 2012 14:40:59 GMT

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

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

bozza — Thu, 27 Dec 2012 09:01:38 GMT

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

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

qkwltk — Tue, 01 Jan 2013 06:25:31 GMT

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 ..

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

Suda — Thu, 10 Jan 2013 06:14:05 GMT

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.

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

Suda — Thu, 10 Jan 2013 06:20:19 GMT

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.

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

Suda — Thu, 10 Jan 2013 08:02:22 GMT

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

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

anshu2812 — Mon, 28 Sep 2020 13:20:04 GMT

Can dboutput be used for textbox values? Say if I have a DB table my_table 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 my_table(A,B,C) values('text1','text2','text3'). Any help would be really appreciated!

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

mikefapex — Mon, 28 Sep 2020 13:54:27 GMT

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=Certlab_DMZViewer1 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:

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

sql

advanced=false

But My question is: how do I reference the 'test_dbx_insert' 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

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

koshyk — Sat, 29 Mar 2014 16:01:24 GMT

Documentation at: http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Commands#dboutput

For SQlite, i tried..

-- Created a Sample in normal SQlite Command line to see if everything works
  CREATE TABLE [Genre]
    (
        [GenreId] INTEGER  NOT NULL,
        [Name] NVARCHAR(120)
    );

INSERT INTO [Genre] ([GenreId], [Name]) VALUES (1, 'Rock');

After installing Splunk DB connect, via Splunk Search I tried Select Query

 | dbquery "mySampleDB" "SELECT * FROM Genre" limit=1000

Output is

GenreId     Name
1       Rock

Sample Insert Query is:

*| head 1|eval GenreId=2 | eval Name="myOpera" | table GenreId,Name | table GenreId,Name| dboutput type=insert database=mySampleDB table=Genre GenreId Name

Selecting again:

| dbquery "mySampleDB" "SELECT * FROM Genre" limit=1000

Output is now:

GenreId Name
1       Rock
2       myOpera