Sorting by TimeStamps in events with Multiple TimeStamps

[cramasta]

Hi, So I have a flat log file that i am indexing that has two timestamps in the same format. I don't care which one gets recognized as the timestamp by Splunk but what I do want to be able to do is sort by either timestamp. Currently I can sort correctly by the timestamp that splunk picks up as being the events timestamp. However when i try and sort by the other timestamp column it does not work correctly and mixes the dates up. It comes close to being correctly sorted but not 100%.

format of the logs

"9/27/2010 5:23:39 AM","7/20/2010 1:48:28 PM",MBX01-SG20-DB20

My temp work around is to do this

| eval NewTime = strptime(NonRecognizedTimeStamp, "%m/%d/%Y %H:%M:%S %p")

what this does is gives me a new column called NewTime that converts the timestamp into a numerical format like 1279636323.000000 which i can then sort, and in return, sorts the NonRecognizedTimeStamp column correctly.

Is there a way that i can get the other timestamp to be recognized correctly or at least have my temp solution return the NewTime Column in the %m/%d/%Y %H:%M:%S format. Thanks, Joe

[gkanapathy · Accepted solution]

What you're doing is really the only way for you to sort by the non-indexed timestamp, unless you can, e.g., change your log format.