topic Re: DB Connect : SQL query column output ALIAS names are not indexing as fields in Splunk Search

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

ma7859 — Mon, 13 Jan 2014 07:17:39 GMT

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.

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

aelliott — Mon, 28 Sep 2020 15:38:25 GMT

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?

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

ma7859 — Mon, 13 Jan 2014 15:51:22 GMT

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.

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

aelliott — Mon, 13 Jan 2014 15:57:13 GMT

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

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

ma7859 — Mon, 13 Jan 2014 16:12:57 GMT

Yes its MKV,

output.format = mkv

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

aelliott — Mon, 13 Jan 2014 16:24:23 GMT

have you checked the splunkd.log for any errors?

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

ma7859 — Mon, 28 Sep 2020 15:38:28 GMT

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

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

ma7859 — Tue, 14 Jan 2014 07:48:58 GMT

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

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

araitz — Tue, 14 Jan 2014 19:07:30 GMT

Can you please open a support case?

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

ma7859 — Thu, 16 Jan 2014 06:26:41 GMT

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.

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

AvianFLU — Thu, 24 Jul 2014 00:10:48 GMT

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