Getting Data In

Time comparsion in search

Contributor

I have 2 fields, starttime and endtime in the search result. When I change the time window in the search bar, it only takes effects on the start time because splunk thinks start_time is the event time.

Simply put, if the eventdata has 2 timestamps, how can I ask splunk search to use the second one as the event time?

Thanks!

Tags (3)
0 Karma
1 Solution

Legend

You can have multiple time fields in Splunk, but only one field can be the event time, which Splunk stores as _time. _time is the field that Splunk examines when you choose a time range from the drop down in the user interface.

Here is how to tell Splunk which field to use as the event time, assuming that your data has sourcetype=mydata and the format you defined above:

# sets the event timestamp to the first time field
[mydata]
TIME_PREFIX=.*?,\s
MAX_TIMESTAMP_LOOKAHEAD = 20

# sets the event timestamp to the second time field
[mydata]
TIME_PREFIX=.*?,\s.*?,\s
MAX_TIMESTAMP_LOOKAHEAD = 20

You must choose one or the other of these choices; you can't have both.

BUT - you can have a second time field. Let's say that you are going to use the start time as the timestamp. You can create a field named "endTime" that is the end time, and you can use it in a search, too. Here is an example:

# sets the event timestamp to the first time field and extracts the second time field
[mydata]
TIME_PREFIX=.*?,\s
MAX_TIMESTAMP_LOOKAHEAD = 20
EXTRACT-e1=.*?,\s.*?,\s(?<endTimeString>\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2})

In your search, you can do this to find the events that ended in the last 30 minutes:

yoursearchhere | 
eval endTime = strptime(endTimeString,"%x %X") |
where endTime >= relative_time(now(),-30m)

I hope this helps...

View solution in original post

Legend

You can have multiple time fields in Splunk, but only one field can be the event time, which Splunk stores as _time. _time is the field that Splunk examines when you choose a time range from the drop down in the user interface.

Here is how to tell Splunk which field to use as the event time, assuming that your data has sourcetype=mydata and the format you defined above:

# sets the event timestamp to the first time field
[mydata]
TIME_PREFIX=.*?,\s
MAX_TIMESTAMP_LOOKAHEAD = 20

# sets the event timestamp to the second time field
[mydata]
TIME_PREFIX=.*?,\s.*?,\s
MAX_TIMESTAMP_LOOKAHEAD = 20

You must choose one or the other of these choices; you can't have both.

BUT - you can have a second time field. Let's say that you are going to use the start time as the timestamp. You can create a field named "endTime" that is the end time, and you can use it in a search, too. Here is an example:

# sets the event timestamp to the first time field and extracts the second time field
[mydata]
TIME_PREFIX=.*?,\s
MAX_TIMESTAMP_LOOKAHEAD = 20
EXTRACT-e1=.*?,\s.*?,\s(?<endTimeString>\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2})

In your search, you can do this to find the events that ended in the last 30 minutes:

yoursearchhere | 
eval endTime = strptime(endTimeString,"%x %X") |
where endTime >= relative_time(now(),-30m)

I hope this helps...

View solution in original post

Contributor

I see but I would suggest to have a secondary event time in splunk's future release cause it's a real world use case.

Thanks!

0 Karma

Legend

Splunk will not substitute the start time for the end time. If Splunk cannot find a time stamp based on the rules you provide, Splunk will assign the current time to the event (that is, the time that Splunk is indexing the event).
So perhaps you should use the first option, if the second timestamp does not appear in every event.

0 Karma

Contributor

Excellent answer!!! Thanks.

One minor question -- assuming I choose the second stanza which uses the end time as the event time. Sometimes the field is empty since the job just starts, will splunk be smart enough to use the start time as the event time?

What will splunk do if the row doesn't have the time stamp defined in TIME_PREFIX?

0 Karma

Contributor

Do searches on the end time.
Thanks!

0 Karma

Splunk Employee
Splunk Employee

So do you want end time as the primary time that Splunk picks up or you just want to do some searches on the end time?

0 Karma

Contributor

I looked at the doc but it didn't help too much.
Let's say I have an event with 3 fields -- status, starttime and endtime. e.g.

Success, 05/29/2012 16:00:11, 05/29/2012 17:00:11

How can I create a search string saying end_time is within last 30 minutes?

0 Karma

Splunk Employee
Splunk Employee
0 Karma