Archive
Highlighted

How can I create custom indexed fields for Splunk DB Connect database inputs?

Splunk Employee
Splunk Employee

Currently using a pair of custom indexed fields for qualifying some of our data. For normal inputs this works great using the steps I've included at the end. It does not, however, seem to work with dbmon-tail inputs from the DBConnect app. I really need those input to also have values for the indexed fields, but just specifying the field names like this doesn't work:

[dbmon-tail://MYDB/mydbqueryname]
host = myhost
index = application
interval = 5m
output.format = mkv
output.timestamp = 1
output.timestamp.column = login
time
query = select * \r\nfrom sometable\r\nwhere logintime >= sysdate-1\r\n{{AND $risingcolumn$ > ?}}
sourcetype = mysourcetype
table = sometable
tail.rising.column = LOGINTIME
meta
customfield1 = "myapp"
meta_customfield2= "Finance Division"

A working process for custom indexed fields that are not dbmon-tail inputs:
To correctly handle the indexed fields metacustomfield1 and metacustomfield2 on a heavy forwarder or indexer:

Step 1: (inputs.conf)
[monitor://tmp/myappsecurity.log]
meta
customfield1 = “myapplicationname"
metacustomfield2 = “securityinfo"
sourcetype=mysourcetype

[monitor://tmp/myapperror.log]
meta
customfield1 = “myapplicationname"
meta_customfield2 = “errors"
sourcetype= mysourcetype

Description: This is the same as an inputs.conf file on a universal forwarder, nothing different here.
(you will get warnings about the meta_* names when you restart splunk, this is normal and you can safely ignore them)

Step 2: (props.conf)

[host::*]
TRANSFORMS-generatemeta = generatefield1, generatefield2

Description: This stanza says "for any host, call the two transforms generatefield1 and generatefield2.”

Step 3: (transforms.conf)
[generatefield1]
SOURCE
KEY = metacustomfield1
WRITE
META = true
REPEATMATCH = true
FORMAT = meta
customfield1::"$1"
REGEX= ([^:\"]+)

[generatefield2]
SOURCE
KEY = metacustomfield2
WRITE
META = true
REPEATMATCH = true
FORMAT = meta
customfield2::"$1"
REGEX= ([^:\"]+)

[acceptedkeys]
miketest = meta
customfield1, meta_customfield2

Description: the acceptedkeys stanza just lists the new keys we created (otherwise you will get errors). The other two stanzas look at the sourcekey and keep applying the regex to it creating a new indexed field for each match. So a metacustomfield1 of “finance:banking” would result in a first key of metacustomfield1 = finance and a second indexed field of metacustomfield1 = banking. The WRITEMETA tells the transforms to actually make this an indexed field.

Step 4 (This is done on the search head, so in your case this is unnecessary, but it’s a change they made to fields.conf)
[meta_customfield1]
INDEXED = true

[meta_customfield2]
INDEXED=true

Description: This just tells the search head about the two new indexed fields.

0 Karma
Highlighted

Re: How can I create custom indexed fields for Splunk DB Connect database inputs?

Splunk Employee
Splunk Employee

After some trial and error I got this working (but it's a bit of a hack, not sure yet why I can't get the normal approach to work with dbmon-tail inputs) as a workaround:

inputs.conf:
[dbmon-tail://MY_DB/mydbqueryname]
# stash the two meta fields in the host name
host = "myhost|myapp:finance division"
index = application
interval = 5m
output.format = mkv
output.timestamp = 1
output.timestamp.column = login_time
query = select * from sometabler where login_time >= sysdate-1 {{AND $rising_column$ > ?}}
sourcetype = mysourcetype
table = sometable
tail.rising.column = LOGIN_TIME

props.conf:
[mysourcetype]
TRANSFORMS-dbmon_create_meta_fields = dbmon_create_field1, dbmon_create_field2, dbmon_clean_host

#  These two transforms could be combined, I just am being lazy
[dbmon_create_field1]
SOURCE_KEY=MetaData:Host
REGEX=\w+\|([^\:\"]+)
FORMAT = meta_customfield1::"$1"
WRITE_META = true

[dbmon_create_field2]
SOURCE_KEY=MetaData:Host
REGEX=\w+\|[^\:\"]+\:([^\"]+)
FORMAT = meta_customfield2::"$1"
WRITE_META = true

#This cleans the host field back up to just have the host name
[dbmon_clean_host]
SOURCE_KEY=MetaData:Host
REGEX=\"{0,1}([^\|\"]+)\|
FORMAT = host::$1
DEST_KEY = MetaData:Host

[accepted_keys]
key_ meta_customfield1 = meta_customfield1
key_ meta_customfield2 = meta_customfield1
0 Karma