Splunk Search

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

ma7859
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

ma7859
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

ma7859
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.

0 Karma

AvianFLU
Explorer

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

0 Karma

araitz
Splunk Employee
Splunk Employee

Can you please open a support case?

0 Karma

ma7859
Explorer

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

0 Karma

ma7859
Explorer

output format is mkv.
[dbmon-tail://sa-sdsql05_HelpSpot/helpspot_errors]

0 Karma

aelliott
Motivator

have you checked the splunkd.log for any errors?

0 Karma

ma7859
Explorer

Yes its MKV,

output.format = mkv

0 Karma

aelliott
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

ma7859
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

aelliott
Motivator

Is there anything but integers in the Error_ID field?
if you run select error_ID from hs_errors where Error_ID = 'HS-82493' does it come back with results?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...