topic Re: How to convert a timestamp field to epoch format? in Getting Data In

How to convert a timestamp field to epoch format?

akelly4 — Sun, 02 Nov 2014 14:51:38 GMT

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"

Re: How to convert a timestamp field to epoch format?

martin_mueller — Sun, 02 Nov 2014 16:27:06 GMT

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")

Re: How to convert a timestamp field to epoch format?

vasanthmss — Tue, 04 Nov 2014 05:39:27 GMT

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

Re: How to convert a timestamp field to epoch format?

Drainy — Tue, 04 Nov 2014 07:22:42 GMT

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.