Timestamp Parsing in JSON

[RichaSingh]

Hi,

Can anyone help me with best configurations for timestamp parsing (where "DateTime" is the actual time) for following JSON format :

{ [-] 
    AccessKey:  ----------------------
    Account:  some-value
    AccountId:  somevalue
    Action:  GetInstances 
    Class:  ----------------------------------------------------------
    **DateTime:  2007-10-27T00:51:57.91Z** 
    ElapsedTime:  00:00:00 
    File:  null 
    Level:  Trace 
    Line:  null 
    LogId:  ID-347t58734534-6565ghmhmm
    Message:  Vapor URL - https://www.randomURL.com,ImageId)
Header - {
  "Signature": "$^%$#@)(#4t4r65gvjhloojk",
  "AccessKey": "123854y3957349534-0fdjvbkjds90234u02394-23940=dsffsvbkdjadsvfnk0923480",
  "RequestType": 0,
  "SkipOwnerLookup": false,
  "UserAgent": "456934",
  "UserAgentVersion": null,
  "Timestamp": "2007-10-27T00:51:57Z",
  "User": "3254832564873`12",
  "TestMode": false,
  "ScheduledAction": false
} 

What I have already tried in props.conf is:

[API]
TIMESTAMP_FIELDS=DateTime 
INDEXED_EXTRACTIONS=json
NO_BINARY_CHECK=true
KV_MODE=none 

And also:

[API]
TIME_PREFIX = "\"DateTime\":\"
INDEXED_EXTRACTIONS=json

Also, I have been through the link:

https://answers.splunk.com/answers/104500/transforming-timestamps.html

None of the two seem to be working. Any comments/ suggestions will be highly appreciated.

[SierraX · Accepted solution]

Hi,
got a similar problem at an customer and worked yesterday on a solution based on your Question and the first answer, on a testsetup.

The Splunk Server has a different system time than the sender (see in the lower half)

The JSON is send by following python line:

response = urllib2.urlopen(req, json.dumps({"sourcetype":"indoor_tph", "source":"", "host":"", "event": {"temperature": str(sense.get_temperature()-7.0), "timestamp": str(datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S.%f')), "pressure": str(sense.get_pressure()), "humidity": str(sense.get_humidity())}}))

The props.conf entry for the upper half example looks:

[indoor_tph]
INDEXED_EXTRACTIONS = JSON
TIME_PREFIX = "timestamp": "
TIME_FORMAT = %Y-%m-%dT%H:%M:%S.%6N
TZ = UTC

Hope this will help other splunkers with the same or similar problems.

Kind Regards
SierraX

[acharlieh]

If that's an accurate copy/paste of the data you're attempting to send in, that is not actually JSON formatted text. It's close, but not quite. This could definitely throw a wrench into using INDEXED_EXTRACTIONS. If it was actually JSON text there would be a lot more double quotes at least.

If you're using INDEXED_EXTRACTIONS=json with your sourcetype, the props.conf stanza specifying INDEXED_EXTRACTIONS and all parsing options should live on the originating Splunk instance instead of the usual parsing Splunk instance. (In most environments, this means this configuration is on your universal forwarder instead of your indexer). I would try TIMESTAMP_FIELDS on the UF (assuming you can actually use INDEXED_EXTRACTIONS.)

If you cannot use INDEXED_EXTRACTIONS, don't surround your setting of TIME_PREFIX in quotes. You should also consider also setting TIME_FORMAT and MAX_TIMESTAMP_LOOKAHEAD if the timestamp is consistent.