topic Re: Convert NT Epoch Time with props.conf in Getting Data In

Convert NT Epoch Time with props.conf

xdp4 — Tue, 13 May 2014 18:58:50 GMT

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?

Re: Convert NT Epoch Time with props.conf

araitz — Tue, 13 May 2014 20:12:43 GMT

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

Re: Convert NT Epoch Time with props.conf

martin_mueller — Tue, 13 May 2014 20:43:24 GMT

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

Re: Convert NT Epoch Time with props.conf

jcoates_splunk — Wed, 14 May 2014 01:11:46 GMT

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

Re: Convert NT Epoch Time with props.conf

xdp4 — Wed, 14 May 2014 11:54:27 GMT

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

Re: Convert NT Epoch Time with props.conf

bwindham — Sun, 25 Jan 2015 04:21:56 GMT

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.