Getting Data In

How to move tabular data to splunk so I can query the data in splunk?

oshukla12
New Member

I am trying to have move the data over (that is from a db) to splunk in a tabular form so that I can query it. Currently I am sending over a JSON string with the column name and it's values. What is the best way to achieve this?

  1. Maybe I should send something different.
  2. Maybe splunk can de-parse the JSON string into a table.

Having string data is of no use to me. I need to query the data. EG: Count of people who like oranges in below table should give me 2.

Person        Favourite Fruit         
A                   Orange
B                    Apple
C                   Orange

My actual data is pretty large, with 14 columns and a lot of records that are changing (it's a log table). We have direct SQL-to-splunk connectors for most db but not for this db so I am trying to find the best way to send over this data. Currently, I am logging each record in the table in the log file and then converting it into a JSON string which shows up on Splunk.

0 Karma
1 Solution

robgora_deloitt
Path Finder

If you create a read connection from DBConnect to your DB, you won't have to convert it to JSON. You can just query directly against the database or you can have DBConnect index the data.

And if you want to see the data in a table you can just use the table command in your query.

View solution in original post

0 Karma

robgora_deloitt
Path Finder

If you create a read connection from DBConnect to your DB, you won't have to convert it to JSON. You can just query directly against the database or you can have DBConnect index the data.

And if you want to see the data in a table you can just use the table command in your query.

0 Karma

oshukla12
New Member

Thank you. So I was made to understand that this particular db cannot be queried directly against (scaling issues). Other db can be. So for now DBConnect wont work I think. Yes while researching I found a table command so I am going to play around more with that. Not sure if there's a performance hit in converting the JSON string to table.. would you know?

0 Karma

robgora_deloitt
Path Finder

It is interesting that you can't query against the DB. Since all you are doing is creating a connection string to the database and allowing Splunk to read it like a normal SQL Console.

If the data is already being indexed, you can use the table command and there won't be any hit to performance. The hit to performance will come if you are doing too large of a search (Date/Time) or if your query is malformed and taking too long to run. You can use the table command on any query to give you a tabular view of your data.

0 Karma

oshukla12
New Member

Thank you.. I will look into the DBConnect option some more as well. Some queries would be DateTime based.. actually most probably would be.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Since you already have DB Connect, use it to read the data from this db directly, without the intervening JSON step.
If DB Connect won't work, use a CSV.

---
If this reply helps you, Karma would be appreciated.
0 Karma

oshukla12
New Member

Thank you for your reply. Can you please explain more on the csv way? Although the data is huge so I dont know if that is a good way.. we have a push/pull model for adding on to it

0 Karma

robgora_deloitt
Path Finder

What type of database is the data coming from? You could try using DBConnect to either index the data or just search the data directly from a search window.

0 Karma

oshukla12
New Member

Thank you for your reply. Its a member db. I am looking for a tabular type of view in splunk. Searching directly in a search window in splunk wont work since all the fields are in a string (JSON string but still it's a string)

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...