Security

SQL Server ERRORLOG

stepmmx
Path Finder

I have a similar issue like the one described here:
http://www.splunk.com/support/forum:SplunkAdministration/2686

After some monitoring time I get wrong data from the Log:

\x002\x000\x000\x009\x00-\x000\x004\x00-\x001\x000\x00 \x001\x004\x00:\x003\x000\x00:\x004\x007\x00.\x003\x009\x00 \x00L\x00o\x00g\x00o\x00n\x00 \x00 \x00 \x00 \x00 \x00 \x00 \x00L\x00o\x00g\x00i\x00n\x00 \x00s\x00u\x00c\x00c\x00e\x00e\x00d\x00e\x00d\x00 \x00f\x00o\x00r\x00 \x00u\x00s\x00e\x00r\x00 \x00'\x00h\x00p\x00_\x00o\x00v\x00o\x00'\x00.\x00 \x00C\x00o\x00n\x00n\x00e\x00c\x00t\x00i\x00o\x00n\x00:\x00 \x00n\x00o\x00n\x00-\x00t\x00r\x00u\x00s\x00t\x00e\x00d\x00.\x00 \x00[\x00C\x00L\x00I\x00E\x00N\x00T\x00:\x00 \x001\x009\x002\x00.\x001\x006\x008\x00.\x001\x00.\x004\x002\x00]\x00
\x00

I thought the problem was solved in previous versions.

I'm running Splunk 4.2.1, SQL server 2008 Enterprise Edition x64, Windows 2003 SP2 x64

Here my props.conf settings:

[source::\comp-name\D$\SQLServer\LOG\ERRORLOG]
CHARSET=UTF-16LE
NO_BINARY_CHECK = true

Thanks
Stefan

Tags (2)
1 Solution

hexx
Splunk Employee
Splunk Employee

Your CHARSET and NO_BINARY_CHECK settings seem appropriate. I would worry that the source-based props.conf spec might not always match, though.

I would advise to assign a sourcetype to this type of logs at the input level if you haven't done so yet ("sourcetype=sqlerror" in the corresponding inputs.conf stanza) and to set up a sourcetype-based stanza in props.conf to replace the one you currently have :

[sqlerror]
CHARSET = UTF-16LE
NO_BINARY_CHECK = true

This will be more reliable than a source-based spec.

View solution in original post

vcarbona
Path Finder

I only monitor for ERRORLOG and still got the same issue. I tested the props.conf ONLY on the universal forwarder it works fine. So no need to update the indexer's props.conf.

0 Karma

bguenter
Explorer

I had this same issue, however since I pointed my source to just all files in the SQL Server log directory, it picked up SQLAGENT.OUT as well which caused the output to show up with the \x00 before each letter. I had to whitelist the directory to only use the ERRORLOG files. Once I did that, then splunk was able to pick up the proper character set.

0 Karma

hexx
Splunk Employee
Splunk Employee

Your CHARSET and NO_BINARY_CHECK settings seem appropriate. I would worry that the source-based props.conf spec might not always match, though.

I would advise to assign a sourcetype to this type of logs at the input level if you haven't done so yet ("sourcetype=sqlerror" in the corresponding inputs.conf stanza) and to set up a sourcetype-based stanza in props.conf to replace the one you currently have :

[sqlerror]
CHARSET = UTF-16LE
NO_BINARY_CHECK = true

This will be more reliable than a source-based spec.

View solution in original post

stepmmx
Path Finder

@hexx: it works! THX!

0 Karma

aalbaker
New Member

i'm new to splunk
i'm having the same issue above , i have tried

[sqlserver_errorlog]
CHARSET = UTF-16LE
NO_BINARY_CHECK = true

on my UF and indexer with no luck. however, i think i might updating wrong props.conf

what is the right path for props.conf on both UF and indexer please.

Thank you in advance

0 Karma

hexx
Splunk Employee
Splunk Employee

@stepmmx: What if you move the following props.conf stanza to the UF?

[sqlserver_errorlog]
CHARSET = UTF-16LE
NO_BINARY_CHECK = true

0 Karma

stepmmx
Path Finder

It works when I access the file over a windows share.
But when I use the universal forwarder, the I have the same problem again
(important: it works for the events, which are already in the ERRORLOG-File, but not for new generated events)

props.conf on indexer

[sqlserver_errorlog]
CHARSET=UTF-16LE
NO_BINARY_CHECK = true

inputs.conf on universal forwarder
[monitor://path\LOG\ERRORLOG]
disabled = false
followTail = 0
host = hostname
index = sqlserver
sourcetype = sqlserver_errorlog

output:
2\x000\x001\x002\x00-\x000\x005\x00-\x000\x004\x00 \x000\x003\x00:\x002\x005\x00:\x004\x007\x00.\x001\x002\x00 \x00s\x00p\x00i\x00d\x006\x004\x00 \x00 \x00 \x00 \x00 \x00 \x00D\x00B\x00C\x00C\x00 \x00C\x00H\x00E\x00C\x00K\x00D\x00B\x00 \x00(\x00P\x00R\x00D\x00_\x00S\x00t\x00a\x00g\x00i\x00n\x00g\x00)\x00 \x00W\x00I\x00T\x00H\x00 \x00a\x00l\x00l\x00_\x00e\x00r\x00r\x00o\x00r\x00m\x00s\x00g\x00s\x00,\x00 \x00n\x00o\x00_\x00i\x00n\x00f\x00o\x00m\x00s\x00g
...

Any ideas?

0 Karma

yungro
Explorer

This works for me too.

0 Karma

stepmmx
Path Finder

I've been spending some time testing your suggestion and it works 🙂
Thank you!

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.