Getting Data In

How to convert a timestamp field to epoch format?

akelly4
Path Finder

I'm loading a file via Data Inputs into Splunk on a daily basis. When I load the file the _time field is the current time when the file is loaded and the 'Date Added' is the time a device was added.

My goal is to be able to search based on time for both of these specific fields. For example, the files will be loaded on a daily basis so I want to be able to look at the logs from only the last file loaded at any given time. Then once I find the logs for that file I want to be able to search those logs for any that have a 'Date Added' date that is -7d@d.

My problem is I can't seem to figure out how to convert the 'Date Added' field into a time based field so I can search off of it? Is this possible?

Below is what the log looks like

_time                                          Date Added
10/29/14 3:42:00.000 PM "Test-Machine"," "," ","2014-10-29 15:42"
Tags (3)

Drainy
Champion

Splunk already has a field for when data is added (or indexed to be more accurate) _indextime. You could flip this around and set _time at index time to be the value of Date Added, have a look at props.conf to set this up;
http://docs.splunk.com/Documentation/Splunk/6.2.0/Admin/Propsconf
Its pretty straightforward and your date format should be understood without issue.
This way you can then search by _indextime and _time, although if you want the performance boost of filtering on _time with the expectation that this is the date added, then this option isn't for you.,Just to throw another option out there, Splunk already has a time field for when data is added - _indextime. So why not just override _time to be Date Added at index time?
Have a look through http://docs.splunk.com/Documentation/Splunk/6.2.0/Admin/Propsconf for all the options but its pretty simple to configure and you may find it a better solution in the long run.

If you want the performance boost of filtering on _time being the time added then this won't be for you but if you're going to search for both values, it should help.

vasanthmss
Motivator

Another way to make epoch time without making new field,

same query instead of

| eval dateadded_epoch = strptime('Date Added', "%Y-%m-%d %H:%S")

use

| convert timeformat="%Y-%m-%d %H:%M:%S" mktime("Date Added")

Query

base search identifying the correct file | convert timeformat="%Y-%m-%d %H:%M:%S" mktime("Date Added") | where "Date Added" >= relative_time(now(), "-7d@d")

At end you will get same result 😉

Thanks,
Vasu

martin_mueller
SplunkTrust
SplunkTrust

You could do something like this:

base search identifying the correct file | eval dateadded_epoch = strptime('Date Added', "%Y-%m-%d %H:%S") | where dateadded_epoch >= relative_time(now(), "-7d@d")
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!