How to get a sourcetype of JSON mixed with text that uses the timestamp within the JSON object and correctly extracts kv and JSON kv pairs?

[mathiask]

Hi I'm trying to get JSON data from a message queue into splunk. This works very well but the imported events also contain some message header information

Tue Dec 02 15:45:56 CET 2014 name="amqp_msg_received" event_id="null" msg_queue="dns-events2splunk-splunk" msg_exchange="dns-events2splunk" msg_body="{"timestamp":"2014-12-02T14:45:56.021Z","src":"192.168.16.56","src_port":"53","dest":"192.168.0.2","dest_port":7521,"dns_meta":{"status":"NOERROR","id":42347,"flags":["qr","rd","ra"],"queries":"1","answers":"1"},"dns_query":{"domain":"lopq.ch.","class":"IN","type":"A"},"dns_response":{"domain":"lopq.ch.","ttl":8477,"class":"IN","type":"A","IP":"10.14.183.106"}}"

How can i now get a sourcetype that

1. using the timestamp within the json object
2. correctly extracts the kv pairs as well as the json kv pairs

Alternatively only taking the msg_body and correctly extract the json object

[cpetterborg · Accepted solution]

I you don't mind loosing the data that doesn't fall within the msg_body, then you can use SEDCMD to eliminate all the the JSON string, something like:

SEDCMD-stripnonjson1 = s/^.*msg_body="//
SEDCMD-stripnonjson2 = s/}"$/}/

This will leave only the JSON string for it index, which can then be used to get the timestamp and be parsed into the kv pairs:

KV_MODE = json
TIME_PREFIX = "timestamp":"

These would go in the props.conf file for the data coming in to the indexers. You may also want to define the linebreak to do it on each JSON string at the beginning of a line if they cross lines:

BREAK_ONLY_BEFORE = ^{
NO_BINARY_CHECK = 1
SHOULD_LINEMERGE = true

I have tried this on your data, and it appears to have worked.

[nmanolak]

I downvoted this post because dosnt answer the question

[sbsbb]

I also use Websphere, and the jms connector...

There are a lot of possible actions, but nothing that covers your needs at 100%...
I don't use json, but xml, but problems and solutions are the same...

• I'm happy about the header, because we put a lot of good informations in it, we can search at, because there are automatically converted to fields by splunk
• To handle the body, I've made a field-extraction with a regex like
  "body=(?P.*). After that I can user spath : spath input=xml ...

There two other options :
- make a custom message handler, for the jms connector, there you could put all your json data into key-value, to have splunk extract it automatically
- or with a custom message handler or changes in the jms connector, but everything in json, and use kvmode=json (I'm not sure it works, for xml you can make it... you have to check).

.

[mathiask]

btw im using Splunk Enterprise and not Splunk Cloud, and there i cannot find the sourcetype json_predefined_timestamp