Convert NT Epoch Time with props.conf

[xdp4]

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?

[bwindham]

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.

[xdp4]

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

[martin_mueller · Accepted solution]

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]

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

[araitz]

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