Archive

DB Connect : SQL query column output ALIAS names are not indexing as fields

Explorer

Hi,

Background:

I am trying to index SQL source where i have to give alias to table column names.

My query:

WITH T1 AS( SELECT xErrors AS [Error_ID],MAX(DATEADD(s, dtErrorDate, '1970-01-01 00:00:00')) AS [Timestamp],sType AS [Type],sFile AS [File],sLine AS [Line],sDesc AS [Description] from HS_Errors group by xErrors, sType, sFile, sLine, sDesc)SELECT * FROM T1 order by [Error_ID]

Where xErrors is an identity column, so i put it as rising column.

I tried putting it in inputs.conf file as below:

[dbmon-tail://sa-sdsql05_HelpSpot/helpspot_errors]
index = default
interval = auto
output.format = mkv
output.timestamp = 1
output.timestamp.column = TimeStamp
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSSXXX
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss.SSSXXX
query = WITH T1 AS( SELECT xErrors AS [Error_ID],MAX(DATEADD(s, dtErrorDate, '1970-01-01 00:00:00')) AS [Timestamp],sType AS [Type],sFile AS [File],sLine AS [Line],sDesc AS [Description] from HS_Errors group by xErrors, sType, sFile, sLine, sDesc ) SELECT * FROM T1 {{WHERE $rising_column$ > ?}} order by $rising_column$
sourcetype = helpspot_errors
tail.rising.column = Error_ID
disabled = 0

If i run query in SQl management studio it gives me an output as following columns :

Error_ID   Timestamp     Type    File    Line    Description

which is fine, but the problem is when data gets indexed in SPLUNK, it doesn't make column headers as fields but it shows me in raw data as below:

6:29:06.716 AM  
2014-01-13 06:29:06.716Z Error_ID=1171961 Timestamp=1389393351.000 Type=Database File="C:\\Program Files (x86)\\helpspot\\helpspot\\helpspot\\lib\\class.person.status.php" Line=32 Description="SQLState: 22018
Error Code: 245
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting the varchar value 'HS-82493' to data type int."

I want to have Error_ID,Timestamp,Type,File,Line,Description as automatically created interesting fields. Please help me.

0 Karma
1 Solution

Explorer

My bad, the problem was some of my legacy applications which were configured with the same source-type name whose props.conf and transform.conf were overwriting this new source-type with the same name.
I changed the source-type name and it solved this weird problem.

View solution in original post

0 Karma

Explorer

My bad, the problem was some of my legacy applications which were configured with the same source-type name whose props.conf and transform.conf were overwriting this new source-type with the same name.
I changed the source-type name and it solved this weird problem.

View solution in original post

0 Karma

Explorer

Could you be more specific in how you solved this issue?

0 Karma

Splunk Employee
Splunk Employee

Can you please open a support case?

0 Karma

Explorer

ya i checked splunkd.log, but didnt find any errors.

0 Karma

Explorer

output format is mkv.
[dbmon-tail://sa-sdsql05HelpSpot/helpspoterrors]

0 Karma

Motivator

have you checked the splunkd.log for any errors?

0 Karma

Explorer

Yes its MKV,

output.format = mkv

0 Karma

Motivator

This would have to do with the sourcetype = helpspot_errors , that tells splunk of how you want it indexed. Is the sourcetype format dbmon:mkv ? If not you could just set the sourcetype=dbmon:mkv

0 Karma

Explorer

Error_ID is the alias i have given to xErrors column which is an identity column with datatype as integer.
I tried executing below query as u suggested but got exception msg :

SELECT xErrors from hs_errors where xErrors = 'HS-82493'

Exception msg:

Conversion failed when converting the varchar value 'HS-82493' to data type int.

My problem is those aliases are coming as column name in SQL management studio, i want them as splunk fields in splunk, which is not coming in splunk.

0 Karma

Motivator

Is there anything but integers in the ErrorID field?
if you run select error
ID from hserrors where ErrorID = 'HS-82493' does it come back with results?

0 Karma