Getting Data In
Highlighted

How to get records in epoch time format to show as DateTime format?

New Member

I have pulled in a bunch of records from my database. By looking at many posts and going over the docs I figured out how to get the timestamp (date create) of each record to be recognized. Now that I have 50,000 records in, there are several other date columns (they are DateTime's in MSSQL) but they show up in Epoch time format. How can I get them to show up in real DateTime format so I (and others) can query on them? Here is an example of one record:
SRID=237218 CreatedBy=first.last@xyz.com UpdatedDate=1404771267.153 UpdatedBy=first.last@xyz.com DateOpened=1404771267.123 DateClosed=1404771267.153 IntakeSource=Phone

The SRID is the rising field, I got the CreatedDate properly set so Splunk recognizes it, but now I need UpdatedDate, DateClosed and several other dates converted... My question is - do I have to blow it all away and try to do this as the data is originally pulled in or can I write this in a transform.conf or a props.conf file to convert it for me? It will not always be at an exact offset...

Thanks,
George

0 Karma
Highlighted

Re: How to get records in epoch time format to show as DateTime format?

Motivator

I suggest you show your Splunk query and some sample data if possible. This always makes understanding the problem clearer, and an answer easier to frame.

0 Karma
Highlighted

Re: How to get records in epoch time format to show as DateTime format?

New Member

I did include some sample data up above... After I pulled the data into Splunk I just did a query of Index=xyz and this is how the query shows the data that is in Splunk. Am I missing something?

0 Karma
Highlighted

Re: How to get records in epoch time format to show as DateTime format?

SplunkTrust
SplunkTrust

You can utilize splunk's calculated fields for this requirement, where you can create new/edit existing fields by formatting there value, like you do in an eval command. Checkout this like for more information.

http://docs.splunk.com/Documentation/Splunk/6.0/Knowledge/definecalcfields

0 Karma
Highlighted

Re: How to get records in epoch time format to show as DateTime format?

New Member

Still trying. I have modified the PROPS.CONF file to add

[Infotrak1_index]
EVAL-UpdatedDate = strftime(UpdatedDate,"%+")

I saw the "strftime" mentioned on another posting. Should this be in the PROPS.CONF or in the TRANSFORMS.CONF? Also, I am guessing a bit at the STANZA name... I created a sourcetype name in the Database Input set up. I tried using that (Infotrak1source) but it didn't work. So I have also tried "Infotrak1index" and just "Infotrak1". I will keep going on the trial and error...

0 Karma
Highlighted

Re: How to get records in epoch time format to show as DateTime format?

SplunkTrust
SplunkTrust

This should be updated in props.conf file only and under the stanza with actual sourcetype name (value using which you can select data in search, e.g. index=_internal sourcetype=splunkd). Also, you would need to restart/Refresh Splunk instance, Or add the calculated field using Splunk Web UI (Manager » Fields » Calculated fields)

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.