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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...