All Apps and Add-ons

How to output a CSV file with certain columns from a dbxquery in Splunk DB Connect 2.2.0?

splunkrocks2014
Communicator

Hi.

I am using Splunk DB Connect version 2.2.0 to download the data from a database and output the data into a CSV file as a lookup file. The following is my sample query:

| dbxquery connection=xyz query="SELECT col1, col2, col3, col4, col5 FROM mytable" | outputcsv mylookup.csv

However, the csv file, "mylookup.csv", has lots of garbage contents such as column name, data type, etc. from each row!!! How can I get rid of column name and data type from the outputcsv?

0 Karma
1 Solution

woodcock
Esteemed Legend

First of all, I think you will prefer the output when you use the shortnames directive. Try this:

| dbxquery shortnames=t connection=xyz query="SELECT col1, col2, col3, col4, col5 FROM mytable" | table col* | outputcsv mylookup.csv

View solution in original post

vj8210
Explorer

hey , try using option: shortnames=t

Example:
| dbxquery query="SELECT actor_id, first_name as fname, last_name as lname, last_update FROM actor" connection="mySQL" shortnames=t

http://docs.splunk.com/Documentation/DBX/2.2.0/DeployDBX/Commands

woodcock
Esteemed Legend

First of all, I think you will prefer the output when you use the shortnames directive. Try this:

| dbxquery shortnames=t connection=xyz query="SELECT col1, col2, col3, col4, col5 FROM mytable" | table col* | outputcsv mylookup.csv

splunkrocks2014
Communicator

It doesn't work. This is a biggest problem in DB Connect 2 and it doesn't work as expected as DB Connect 1. Here is the sample output:

(001) NULL.col1 VARHAR2    (002) NULL.col2 VARCHAR2 .... _raw .... _time
0 Karma

woodcock
Esteemed Legend

Did you notice my comment about shortnames? The key is to do dbxquery shortnames=t; did you do that part?

0 Karma

splunkrocks2014
Communicator

I missed the option, "shortnames=t". It works as expected. Thank you for your help.

0 Karma
Get Updates on the Splunk Community!

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...

From Alert to Resolution: How Splunk Observability Helps SREs Navigate Critical ...

It's 3:17 AM, and your phone buzzes with an urgent alert. Wire transfer processing times have spiked, and ...