http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring does not describe what the different Output formats are doing.
Output formatting
These settings determine how the results are converted into a text-based format Splunk can index.
Formats
* Key-Value based
* Multiline Key-Value based
* CSV
* Template based
* Timestamp output
Initially I chose Key-Value based assuming that DB Connect would create the Key from the Column name and the Value from the data in the column.
I then noticed that varchar fields with new lines were being truncated.
To fix this I had to:
splunk clean eventdata
So my question is: What do the different values of Output Format mean and can anyone provide guidance on when to select the different values? (Ideally this should be in the Documentation - Configuring Database Monitoring
DB Connect formats the database results by generating key-value pairs in the form of <Column-Name>=<Column-Value>
. The value is quoted if it contains certain characters. Those key value pairs are concatenated (seperated by a space). If output.timestamp
is enabled, then the timestamp value will be printed at the beginning of the line.
2013-04-05T15:00:23.000 ID=4711 username=wuzi123 message="User has been disabled"
The sourcetype dbmon:kv
contains the necessary index- and search-time settings to deal with this format. It can either be used directly (by simply omitting the sourcetype for the input) or by copying it's settings.
[dbmon:kv]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]+)
Essentially the same as the regular key-value format with the exception that the key-value pairs are seperated by a newline and there is no quoting of values.
2013-04-05T15:00:23.000
ID=4711
username=wuzi123
message=User has been disabled
The sourcetype dbmon:mkv
contains the necessary index- and search-time settings.
[dbmon:mkv]
KV_MODE = none
REPORT-mkv = dbx-mkv
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]---91827349873-dbx-end-of-event---[\r\n])
LINE_BREAKER_LOOKBEHIND = 10000
The values of the database result are format as comma separated values (regular CSV with quoting):
2013-04-05T15:00:23.000,"4711","wuzi123","User has been disabled"
The field extraction for this format has to be done manually. The best option here is a transforms.conf stanza with FIELDS
and DELIMS
.
Does the same as CSV, but it will write the a header line into each file that is being generated containing a comma-separated list of the columns names from the database result.
This format is the most flexible one, as it allows you to generate any format in the output.template
option. The template should contain replacement tokens in the form of $<Column-Name>$
. Those tokens are being replaced with the corresponding column value.
Example Template:
An even occurred at $timestamp$. User $user$ ID $ID$: $message$
would produce
An even occurred at 2013-04-05T15:00:23.000. User wuzi123 ID 4711: User has been disabled
DB Connect ships with a props.conf stanza that handles line-breaking for this format. The settings should be copied the sourcetype used for the database input.
[source::...tpl_*.dbmonevt]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]---91827349873-dbx-end-of-event---[\r\n])
HEADER_MODE = firstline
DB Connect formats the database results by generating key-value pairs in the form of <Column-Name>=<Column-Value>
. The value is quoted if it contains certain characters. Those key value pairs are concatenated (seperated by a space). If output.timestamp
is enabled, then the timestamp value will be printed at the beginning of the line.
2013-04-05T15:00:23.000 ID=4711 username=wuzi123 message="User has been disabled"
The sourcetype dbmon:kv
contains the necessary index- and search-time settings to deal with this format. It can either be used directly (by simply omitting the sourcetype for the input) or by copying it's settings.
[dbmon:kv]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]+)
Essentially the same as the regular key-value format with the exception that the key-value pairs are seperated by a newline and there is no quoting of values.
2013-04-05T15:00:23.000
ID=4711
username=wuzi123
message=User has been disabled
The sourcetype dbmon:mkv
contains the necessary index- and search-time settings.
[dbmon:mkv]
KV_MODE = none
REPORT-mkv = dbx-mkv
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]---91827349873-dbx-end-of-event---[\r\n])
LINE_BREAKER_LOOKBEHIND = 10000
The values of the database result are format as comma separated values (regular CSV with quoting):
2013-04-05T15:00:23.000,"4711","wuzi123","User has been disabled"
The field extraction for this format has to be done manually. The best option here is a transforms.conf stanza with FIELDS
and DELIMS
.
Does the same as CSV, but it will write the a header line into each file that is being generated containing a comma-separated list of the columns names from the database result.
This format is the most flexible one, as it allows you to generate any format in the output.template
option. The template should contain replacement tokens in the form of $<Column-Name>$
. Those tokens are being replaced with the corresponding column value.
Example Template:
An even occurred at $timestamp$. User $user$ ID $ID$: $message$
would produce
An even occurred at 2013-04-05T15:00:23.000. User wuzi123 ID 4711: User has been disabled
DB Connect ships with a props.conf stanza that handles line-breaking for this format. The settings should be copied the sourcetype used for the database input.
[source::...tpl_*.dbmonevt]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]---91827349873-dbx-end-of-event---[\r\n])
HEADER_MODE = firstline
Clear explanations with nice examples. Thanks!
When using the CSV format, how does one link the inputs.conf and transforms.conf files? Should the respective files have identical stanza names?
Any updates to the answer when the column values contain newlines, i.e. from the original question "I then noticed that varchar fields with new lines were being truncated."
For me only Multiline Key-Value based worked
Did you ever find a solution to the issue where column values contain new lines?