topic Re: Indexing a CSV with mixed sourcetypes. in Getting Data In

Indexing a CSV with mixed sourcetypes.

BobM — Thu, 22 Nov 2012 20:25:48 GMT

In SPLUNK, can we index and search data with varying formats?

We have a csv file containg events with different formats. Approximately there are 100 formats available. There is also a config file which can be used as a lookup to determine the column names of the corresponding record.

If we need to put such data into Splunk, where and how we can specify the format for each event to be handled?

The data format is something like this:

Dept01,ABC,20120904,001500,Gn,3,ggss Test-City_gtpp,1,103031    Dept01,DEF,20120904,1500,1,13.17,86.83,277,1876288,34078720,2,3.34,96.66,235,1681068   Dept01,PQR,20120904,001500,24,3,0
Dept01,XYZ12,20120904,001500,Gi,2,abc-xyz.net.P2,67651,0,0,63419,R,0,G,10.208.0.0,abc-xyz.net.GP

The second column (ABC, PQR) are different source types, and each has varying column names and numbers. Since there are no key-value pairs, we cannot use EXTRACT.
In this case, how can we populate the fields?

Re: Indexing a CSV with mixed sourcetypes.

BobM — Thu, 22 Nov 2012 20:27:54 GMT

I found this works. Set the sourcetype with a transform and regex then set the field names at search time.

props.conf

[mycsvsourcetype]
TRANSFORMS-mysourcetyper = csvsourcetypes

transforms.conf

[csvsourcetypes]
DEST_KEY = MetaData:Sourcetype
REGEX = ^\w*,(\w+),
FORMAT = sourcetype::$1

then back in the props.conf file for each source type

[ABC]
REPORT-abc = ABCfields

[DEF]
REPORT-def = DEFfields

and back to transforms.conf to define the filed names

[ABCfields]
DELIMS = ","
FIELDS = "field1", "field2", "field3"

[DEFfields]
DELIMS = ","
FIELDS = "fieldA", "fieldB", "fieldC"

Re: Indexing a CSV with mixed sourcetypes.

BobM — Thu, 22 Nov 2012 22:20:46 GMT

After testing this, I found I have to use REPORT and not TRANSFORMS as it is a search time function. Editing original answer to reflect this