Splunk Search

JSON field extraction from JSON event with inner JSON

phil_tt
Engager

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:

phil_tt_0-1628543006291.png


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!

Labels (1)
0 Karma
1 Solution

venkatasri
SplunkTrust
SplunkTrust

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!

  

View solution in original post

Tags (2)

venkatasri
SplunkTrust
SplunkTrust

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!

  

Tags (2)

phil_tt
Engager

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...