transforming timestamps

[amanteja]

We have JSON data coming into Splunk. When it appears in Splunk the events shows a timestamp like
10/2/13
7:07:26.000 PM

Whereas the actual time stamp of when the even was generated is in the JSON as
{
...

timestamp : "03-Oct-2013,02:07:23:27,UTC",
...
}

I would like splunk to use the timestamp in the json as the default time stamp so that we can sort by that.

Otherwise users have to search using an expression as follows
... | spath "timestamp" |eval t=strptime(timestamp,"%d-%b-%Y,%H:%M:%S:%2N,%Z")|sort t

Which is a pain.

UPDATE:

In etc/system/local/transforms.conf I have defined

[date-transform]
REGEX = \{*.timestamp\":\"(\w{2})-(\w{3})-(\w{4}),(\w{2}):(\w{2}):(\w{2}):(\w{2}),(\w{3})*.
FORMAT=$1-$2-$3,$4:$5:$6

In etc/system/local/props.conf I have defined

[log4j]
TRANSFORMS-dttransform=date-transform
TIME_FORMAT = %d-%b-%Y,%H:%M:%S

The JSON is

{"message":{"request_method": "GET","request_headers": {"host": "localhost:8080"},"params": {"type": "[dbdbdbdb]"},"request_body": "",        "request_url": "http:\/\/localhost:8080\/a"},"sourceClass": "class1",    "tenantId": 0,"timestamp": "03-Oct-2013,16:34:53:63,UTC","sourceId": -1}

But it doesnt seem to work. The time stamp on the left continues to be different from the JSON timestamp. In transform.conf what should be the dest_key ??

Suggestion?

[amanteja · Accepted solution]

Thanks. Finally the fix was that
1. I modified the JSON log to always have the timestamp in the beginning (so no need to define the MAX_TIMESTAMP_LOOKAHEAD)
2. Then in etc/system/local/props.conf I defined the TIME_FORMAT = %d-%b-%Y,%H:%M:%S

Dont need any transform.

[Ayn]

Your TIME_FORMAT looks OK, but your timestamp is pretty far into the event so it's past the default limit of how long into an event Splunk looks for timestamps. Default value is 150 characters - in your sample event the timestamp starts at the 244th character so you'll want to increase the limit by specifying MAX_TIMESTAMP_LOOKAHEAD for this sourcetype in props.conf.

http://docs.splunk.com/Documentation/Splunk/latest/admin/Propsconf

[sdaniels]

Take a look at this:

http://docs.splunk.com/Documentation/Splunk/5.0.5/Data/ConfigurePositionalTimestampExtraction

You'll need to change your MAX_TIMESTAMP_LOOKAHEAD and TIME_PREFIX in your props.conf for that sourcetype (or source/host if you want it that granular). So, how far to look into the event for the timestamp and then a regex to tell splunk to look for that before extracting a timestamp.

[kml_uvce]

you need to put regx in transforms.conf and props.conf ...