Getting Data In

Convert NT Epoch Time with props.conf

xdp4
Explorer

I'm using db connect to access our SQL SCCM database which stores timestamps as NT EPOCH. I want to use props.conf to have the data indexed with the time field converted for human readability. From the search line I can easily leverage the strftime command to get the date I need. However, due to how NT Epoch works, that same command doesn't work in props.conf.

Here is the stanza for my props.conf:

[host::UBERSCCMSERVER]
TIME_PREFIX = (?i)^(?:[^ ]* ){8}\w+=(?P<FIELDNAME>.+)
MAX_TIMESTAMP_LOOKAHEAD = 0
TIME_FORMAT =/10000000-11644473600,"%m-%d-%Y %H:%M:%S"

Anyone know how to modify my TIME_FORMAT line to work properly with NT Epoch?

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Based on the two magic numbers he's referring to tenths of microseconds since January 1st, 1601... used in some MS applications.

You're not going to succeed with props.conf, its power ends with formatting and regular expressions - those cannot do maths.
I've faced the same thing a while back: http://answers.splunk.com/answers/64504/how-to-apply-an-arbitrary-offset-to-the-timestamp-at-index-t...
Back then I worked on the people producing my data, eventually getting them to output intelligible timestamps.
In your case you might be able to tell the SQL query to do some conversion before the data even gets to Splunk.

View solution in original post

bwindham
Path Finder

I am doing the same thing with the SCCM database using dbx. I have my timestamp working but I'm trying to figure out how to convert a few of the other SCCM date fields that are EPOCH. Not sure where I should do those conversions. props.conf? Any help would be appreciated.

0 Karma

xdp4
Explorer

See http://www.epochconverter.com/epoch/ldap-timestamp.php for some background on MS Windows NT time format.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Based on the two magic numbers he's referring to tenths of microseconds since January 1st, 1601... used in some MS applications.

You're not going to succeed with props.conf, its power ends with formatting and regular expressions - those cannot do maths.
I've faced the same thing a while back: http://answers.splunk.com/answers/64504/how-to-apply-an-arbitrary-offset-to-the-timestamp-at-index-t...
Back then I worked on the people producing my data, eventually getting them to output intelligible timestamps.
In your case you might be able to tell the SQL query to do some conversion before the data even gets to Splunk.

jcoates_splunk
Splunk Employee
Splunk Employee

Yeah, I'd try CAST or CONVERT to DATETIME.

araitz
Splunk Employee
Splunk Employee

Can you provide a referent to NT Epoch? Not sure which epoch time you are referring to.

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