Deployment Architecture

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

Suda
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)
1 Solution

martin_mueller
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

koshyk
Super Champion

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

mikefapex
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=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

qkwltk
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

anshu2812
Explorer

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!

0 Karma

Suda
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

bozza
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

Suda
Communicator

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

0 Karma

Suda
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

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

Suda
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...