Getting Data In

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

gjohnson
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

somesoni2
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

somesoni2
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

gjohnson
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 (Infotrak1_source) but it didn't work. So I have also tried "Infotrak1_index" and just "Infotrak1". I will keep going on the trial and error...

0 Karma

gjohnson
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

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