How to extract fields from Splunk DB Connect 2 data?

[katalinali]

I use Splunk DB Connect 2 to generate a db input as:

[mi_input://db:abc]
connection = db
index = db
input_timestamp_column_name = eventtime
interval = 300
max_rows = 10000
mode = batch
output_timestamp_format = yyyy-MM-dd HH:mm:ss
query = SELECT * FROM abc
source = db:abc
sourcetype = Sybase
ui_query_mode = advanced

The output format is key-value statement:

"2016-06-10 22:25:09" event="92", eventmod="1", spid="600", eventtime="2016-06-10 22:25:09.38", sequence="1", suid="38", dbid="6", objid="NULL", xactid="NULL", loginname="abc", dbname="abc", objname="NULL", objowner="NULL"

I input a line in props.conf:

[Sybase]
SHOULD_LINEMERGE = False
LINE_BREAKER = ([\r\n]+)

But the fields are not extracted as a key-value pattern. Fields are not extracted at all. I would like to know how to extract each field with regex.

[gacerioni]

Hi there!

I had the same problem in my office yesterday.
DBConnect KV data should be automatically discovered/parser.

My problem was my search was set to a "Fast Mode" instead of Smart or Verbose.

Hope this was helpful!

Regards,
Gabriel

[gacerioni]

Hi there!

I had the same problem in my office yesterday.
DBConnect KV data should be automatically discovered/parser.

My problem was my search was set to a "Fast Mode" instead of Smart or Verbose.

Hope this was helpful!

Regards,
Gabriel

[jkat54]

Have you tried adding KV_MODE=auto to your props?

This will be a search time extraction so you need to do it in the props.conf in the app context where you will be searching.

Another option is using the extract command in your search

... | extract pairdelim="," kvdelim="="

The problem your having is being created by the commas in between each key value pair. This requires transforms.conf according to props.conf & transforms.conf:

# Transforms.conf is commonly used for:
# * Configuring regex-based host and source type overrides.
# * Anonymizing certain types of sensitive incoming data, such as credit
#   card or social security numbers.
# * Routing specific events to a particular index, when you have multiple
#   indexes.
# * Creating new index-time field extractions. NOTE: We do not recommend
#   adding to the set of fields that are extracted at index time unless it
#   is absolutely necessary because there are negative performance
#   implications.
# * Creating advanced search-time field extractions that involve one or more
#   of the following:
#   * Reuse of the same field-extracting regular expression across multiple
#     sources, source types, or hosts.
#   * Application of more than one regex to the same source, source type, or
#     host.
#   * Using a regex to extract one or more values from the values of another
#     field.
#   * Delimiter-based field extractions (they involve field-value pairs that
#     are separated by commas, colons, semicolons, bars, or something
#     similar).
#   * Extraction of multiple values for the same field (multivalued field
#     extraction).
#   * Extraction of fields with names that begin with numbers or
#     underscores.
#   * NOTE: Less complex search-time field extractions can be set up
#           entirely in props.conf.