Getting Data In

DB Connect: Why datetime field in mssql is imported as epoch time and how to convert it to a time-searchable value?

krwinters11
Path Finder

I have been unable to find a working solution to my problem. My datetime field in my mssql database looks like it is imported as epoch time. This results in me only being able to search via indexed time, when I would like to search by my datetime field. I can convert it to ctime, which makes it look correct to the user, but this is a string, and not a time-searchable value.

The closest answer I have found looks like my datetime field should be of type timestamp. I am unable to change my data types, so a solution/work-around would be great!

Thank you in advance 🙂

0 Karma
1 Solution

musskopf
Builder

Hello, I used to have this problem and my workaround was to convert the Time to varchar in the SELECT statement and, at same time, use the option output.timestamp.parse.format in the DB Connect - This option is not available on the GUI, only via config file.

Here how the $SPLUNK_HOME/etc/apps/dbx/local/inputs.conf looks like:

[dbmon-tail://XXX_ACS/ACS Account Lockout]
index = ws_xxx_acs
interval = 300
output.format = kv
output.timestamp = 1
output.timestamp.column = CreationTimeUTC
output.timestamp.parse.format = yyyy-MM-dd' 'HH:mm:ss' 'Z
output.timestamp.format = yyyy-MM-dd' 'HH:mm:ss' 'Z
query = SELECT convert(varchar,dvA.CreationTime,120) + ' +0000' AS CreationTimeUTC, dvA.EventId, dvA.EventMachine AS Server, dvA.CollectionTime FROM AdtServer.dvAll5 AS dvA WHERE dvA.EventId IN (4740,644) {{ AND dvA.$rising_column$ > ? }}
sourcetype = xxx_acs_lockout
table = dvAll5
tail.rising.column = CollectionTime
disabled = 0

I normally configure everything via GUI, disable it via GUI and after all edit the inputs.conf to add the output.timestamp.parse.format. After edited the file I use the command $SPLUNK_HOME/bin/splunk reload monitor, get back to the GUI to double check and enable to input.

Note: If you need to use the Timestamp as the rising column, you'll need to point it to the original column and not to the converted one, as per my example.

Ps.: One more tip, if you add the DB Input, it'll start enabled by default. To prevent start collecting data before you change the inputs.conf, just schedule it to run using cron style to something like 1 1 1 1 1, so you'll have enough time do disable it.

View solution in original post

0 Karma

musskopf
Builder

Hello, I used to have this problem and my workaround was to convert the Time to varchar in the SELECT statement and, at same time, use the option output.timestamp.parse.format in the DB Connect - This option is not available on the GUI, only via config file.

Here how the $SPLUNK_HOME/etc/apps/dbx/local/inputs.conf looks like:

[dbmon-tail://XXX_ACS/ACS Account Lockout]
index = ws_xxx_acs
interval = 300
output.format = kv
output.timestamp = 1
output.timestamp.column = CreationTimeUTC
output.timestamp.parse.format = yyyy-MM-dd' 'HH:mm:ss' 'Z
output.timestamp.format = yyyy-MM-dd' 'HH:mm:ss' 'Z
query = SELECT convert(varchar,dvA.CreationTime,120) + ' +0000' AS CreationTimeUTC, dvA.EventId, dvA.EventMachine AS Server, dvA.CollectionTime FROM AdtServer.dvAll5 AS dvA WHERE dvA.EventId IN (4740,644) {{ AND dvA.$rising_column$ > ? }}
sourcetype = xxx_acs_lockout
table = dvAll5
tail.rising.column = CollectionTime
disabled = 0

I normally configure everything via GUI, disable it via GUI and after all edit the inputs.conf to add the output.timestamp.parse.format. After edited the file I use the command $SPLUNK_HOME/bin/splunk reload monitor, get back to the GUI to double check and enable to input.

Note: If you need to use the Timestamp as the rising column, you'll need to point it to the original column and not to the converted one, as per my example.

Ps.: One more tip, if you add the DB Input, it'll start enabled by default. To prevent start collecting data before you change the inputs.conf, just schedule it to run using cron style to something like 1 1 1 1 1, so you'll have enough time do disable it.

0 Karma

krwinters11
Path Finder

Thanks! I'll give this a try.

0 Karma

krwinters11
Path Finder

It doesn't seem to change my format.

0 Karma

musskopf
Builder

Have you updated the query as well to output the timestamp as varchar?

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