All Apps and Add-ons

Which inbuilt "source type" and "source" should we assigned to data (table data) which is retrieved from SQL Server 2012/14 Database?

Path Finder

Hello All,

I have retrieved data from SQL Server 2014 in splunk >, Now how do I store it in splunk. Please guide me.
Thanks Everyone.!!

0 Karma
1 Solution

Splunk Employee
Splunk Employee

Hi,

these values are for you, not for us; put whatever you want in them.

View solution in original post

0 Karma

Splunk Employee
Splunk Employee

Hi,

these values are for you, not for us; put whatever you want in them.

View solution in original post

0 Karma

Path Finder

Hi Jcoates, thanks for your quick response,

I have added comments.. but it seems comments are not displayed.
writing the response of last comment here...

As per your guidance in the last comment, I have set the serach query as you suggested
"> http://screencast.com/t/rUqqTuTx

  1. But the data which this query has retrived was found to be very old. You can see the date 15/09/2015. where as I am expecting that it ("_internal" index) should return the data for current date and time.

  2. My table and DB name is differnet from the DB and table name which is specifiend in the data which is being retrived in the search.
    Required Data >> http://screencast.com/t/xzBgBpnq

  3. This is my output when I retrived the data from the sql server 2014 in splunk db connect v2 >> DB Input >> see the screen shot: http://screencast.com/t/bexKE3Qh5

  4. Now the issue is, internal index is not running as per the current date and time. Is there is fixing regarding the index. so that we can get the current status of the data which is retrived in the search.

0 Karma

Path Finder

Hello Jcoates, thanks for quick reply...
So as per your guidance I did,

I ran index "mymain" in search but I didn't get any results see the screenshot > http://screencast.com/t/4HhuGu6QiIVt

This is how I have setup my Metadata in splunk db connect V2 > http://screencast.com/t/jgqE86SBJnR

Are my inputs are correct?
Let me know... thanks..

  • Rupesh Patil
0 Karma

Splunk Employee
Splunk Employee

That source is weird, but it should nevertheless work. Since your search says there are no events indexed, the next question is "why not?" To answer that, try:

index=_internal source=*dbx*

Path Finder

Hello Jcoates,
Thanks for your quick response,

As per your guidance I have setup following command in splunk search & I received this output
here is the screen shot > http://screencast.com/t/usAmAQy7YS

Here I observed that, today date 29/09/2015, but in the result it gave me data of 15/09/2015.
Also, data which I want from the table named "query_listing" (see the screenshot > http://screencast.com/t/55FADTzHo9), but in the search result it says "listing" table.

My metadata properties setup is > http://screencast.com/t/SABov7JKMz1p

Errors which I received:
1. [ERROR] [websocket.py] ERROR: A processing error "Invalid object name 'listing'." occurred..
2. [ERROR] [ws.py] [DBInput Service] ERROR: A processing error "Invalid object name 'listing'." occurred..

Thanks.. for you help.
Now, what we can do further, please let me know.

0 Karma

Splunk Employee
Splunk Employee

When the modular input tries to ask your database for the data, the database says "I don't have anything named 'listing' so please go away." Since it worked with the preview, that makes me wonder if the SQL is getting saved into your inputs.conf correctly?

Path Finder

Hey Jcoates,

So my question is that, I am able to get the data from DB Input but the problem is as you suggested I saved that result in index called "_internal". also source I assigned to it is "dbx2". In splunk there is dbx2.log file which is contineouly get automatically updated at with the interval of 60 seconds (which I have set up) but there also I am getting two errors which I have specified in my previous comment.

Another point is that, the data which< index="_internal" source= dbx > retrieves is from the date of 15/9/2015, not for the current date.

This is where I actually stuck. Thanks for your suggestions.
If you knw how to figure out this further. please help. thank you.

-Rupesh

0 Karma

Splunk Employee
Splunk Employee

you can't use _internal, or anything else that begins with underscore. Try main instead, or make a new index.

0 Karma

Path Finder

Hey Hello Jcoates,

Sorry for replying you very late.

As per your guidance, I have created a new index named "sql_query_result" and assigned it into the metadata (see the screenshot >> http://screencast.com/t/LqZsDYae4e) and saved it successfully.

Then I restart splunk (splunkd service) and ran the command in splunk search. but I didn't retrieved any results (see the screenshot >> http://screencast.com/t/PysDAmvMe8X)

0 Karma

Splunk Employee
Splunk Employee

Since your search says there are no events indexed, the next question is "why not?" To answer that, try:

index=_internal source=dbx

Path Finder

Hi Jcoates,

So, I tried in splunk search, but it didn't return any data see the screen shot here >> http://screencast.com/t/N6MTHxrB

Another point that I would like to mention here is that, splunk db connect v2 is basically suitable for sql server 2012, but not for higher version of above that. Here I am retrieving the data from sql server 2014.

So my question is that if data is coming into the splunk correctly (See this >> http://screencast.com/t/Qaogw7rLW). then it should also shown in the splunk search. Right??

-Rupesh Patil

0 Karma

Splunk Employee
Splunk Employee

sorry, formatting on this site messed that up. You need asterisks around dbx to see what dbx is logging.

I don't know what you mean by saying that it isn't suitable for ms-sql 2014. We test with 2012, but the driver that we use supports 2014 (https://msdn.microsoft.com/en-us/data/ff928484) and we will accept bugs for 2014.

Path Finder

Hi Jcoates, thanks for your quick response

As per your guidance in the last comment, I have set the search query as you suggested
"> http://screencast.com/t/rUqqTuTx

  1. But the data which this query has retrieved was found to be very old. You can see the date 15/09/2015. where as I am expecting that it ("_internal" index) should return the data for current date and time.

  2. My table and DB name is different from the DB and table name which is specified in the data which is being retrieved in the search.
    Required Data >> http://screencast.com/t/xzBgBpnq

  3. This is my output when I retrieved the data from the sql server 2014 in splunk db connect v2 >> DB Input >> see the screen shot: http://screencast.com/t/bexKE3Qh5

  4. Now the issue is, internal index is not running as per the current date and time. Is there is fixes (troubleshoot) regarding the index. so that we can get the current status of the data which is retrieved in the search.

Thank You!!
-Rupesh Patil

0 Karma

Path Finder

Hey Hello Jcoates. Good morning.

As you said, I change the search query and set to "index=_internal source=dbx" (asterisks is not shown in comment) and ran the query:
Output I received : http://screencast.com/t/rUqqTuTx
But this is the output of the result generated on the date 15th Sept 2015, not for todays date and time.
So that means internal index is not currently working, whenever I called it.

I am expected this result: http://screencast.com/t/W03w3vf1

And regarding splunk db connect v2, I read the documentation and they said it is not supported for higher version of sql server above 2012. and currently I am using the sql server 2014.So is that the issue.??

How I can troubleshoot the index :index = _internal to make it workable.?

Thanks for your quick response.
- Rupesh Patil

0 Karma

Path Finder

Hello All,
Kindly help me to resolve this issue.

Thank You!

0 Karma

Path Finder

Need your guidance here..

0 Karma

Path Finder

For your information, Please see the screen shot : http://screencast.com/t/haPc5FEJ

0 Karma