JSON field extraction from JSON event with inner JSON

[phil_tt]

This seems to be an odd issue or at least I've been searching for the wrong thing.  My event sourcetype is json and they log and display just fine.  However, one of the fields of the event contains more JSON that is just being displayed like it is a string.  How can I extract the fields from this string of JSON?

Raw event:

{"Level":"Trace","MessageTemplate":"{\"Id\":\"000000000000000000000000\",\"HttpTracker\":{\"Method\":\"GET\",\"UserAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:90.0) Gecko/20100101 Firefox/90.0\",\"TimeOfCall\":\"2021-08-09T20:08:29.6311024Z\",\"StatusCode\":200,\"Url\":\"http://localhost:45705/Job/JobSelectionTableData?page=0&size=25&sort=col[4]=1&filter=filter&jobType=0\",\"Action\":\"JobSelectionTableData\",\"Controller\":\"Job\",\"Parameters\":{\"page\":\"0\",\"size\":\"25\",\"sort\":\"col[4]=1\",\"filter\":\"filter\",\"jobType\":\"UserCreated\"}},\"Notes\":\"\",\"UserId\":\"5b759c5cbb67fd479489f1ab\",\"Properties\":{\"ServerName\":\"LCS-AL-HNXX8Y2\",\"JobId\":\"000000000000000000000000\",\"TimeTaken\":\"1.998\"},\"HasBeenRead\":false,\"CallType\":1}","RenderedMessage":"{\"Id\":\"000000000000000000000000\",\"HttpTracker\":{\"Method\":\"GET\",\"UserAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:90.0) Gecko/20100101 Firefox/90.0\",\"TimeOfCall\":\"2021-08-09T20:08:29.6311024Z\",\"StatusCode\":200,\"Url\":\"http://localhost:45705/Job/JobSelectionTableData?page=0&size=25&sort=col[4]=1&filter=filter&jobType=0\",\"Action\":\"JobSelectionTableData\",\"Controller\":\"Job\",\"Parameters\":{\"page\":\"0\",\"size\":\"25\",\"sort\":\"col[4]=1\",\"filter\":\"filter\",\"jobType\":\"UserCreated\"}},\"Notes\":\"\",\"UserId\":\"5b759c5cbb67fd479489f1ab\",\"Properties\":{\"ServerName\":\"LCS-AL-HNXX8Y2\",\"JobId\":\"000000000000000000000000\",\"TimeTaken\":\"1.998\"},\"HasBeenRead\":false,\"CallType\":1}","Properties":{"host":"LCS-AL-HNXX8Y2","threadid":"6","logger":"TOPSS.UserLogger.ActionTrackerContext"}}

Splunk recognizes this as JSON and displays as:

Notice the MessageTemplate field contains more JSON.  That is what I'm trying to extract fields from and coming up empty thus far.

A few things I've tried that don't work:

MYSEARCH 
| spath output=Id path=MessageTemplate.Id

MYSEARCH
| spath MessageTemplate

Any help would be much appreciated.  This type of extraction is very new to me!

[venkatasri · Accepted solution]

Hi @phil_tt 

MessageTemplate should have been already pre-extracted however following should does it again. Run the following SPL in smart/verbose mode you will find Interresting fields.

<your_search>
| spath path="MessageTemplate" output=mt 
| spath input=mt

--

An upvote would be appreciated if this reply helps!

[phil_tt]

Yes, this seems to work! Thanks for the help. 😊