Getting Data In

json parsing using spath

vn86893
Explorer

I have a json log as shown below
{
action: Get

applicationName: abc
controller: Main

ip: 123.123.123.123

logLevel: INFO

loggerType: abcdef
machineName: windows

message: {"Value":{"Data":{"Items":[{"FieldType":"abc","Value":"**"},{"FieldType":"abcd","Value":""},{"FieldType":"123","Value":""}],"EncryptedDocKey":"123456","Domain":"Order","Partner":"India","Carrier":"Idea"},"RequestTrackerId":"7894561230","Message":"OK"},"Formatters":[],"ContentTypes":[],"DeclaredType":null,"StatusCode":null}

principalId: 22222222-2222-2222-2222-222222222222

requestMethod: POST

requestUrl: https://abc.com/api/v1/get
responseData: {"Value":{"Data":{"Items":[{"FieldType":"abc","Value":"
"},{"FieldType":"123","Value":""},{"FieldType":"xyz","Value":"**"}],"EncryptedDocKey":"123456789","Domain":"Order","Partner":"india","Carrier":"idea"},"RequestTrackerId":"7894561230","Message":"OK"},"Formatters":[],"ContentTypes":[],"DeclaredType":null,"StatusCode":null}

time: 2019-07-10 18:35:23.3893

traceId: 12345678963525
userName: abc/12345

}

All the fields are indexed correctly. I am looking to extract json data in message element. I would like to extract FieldType,EncryptedDocKey,Domain,Partner,Carrier,RequestTrackerId in to its own fields using spath .

any other alternative options are also welcome. Thanks you for your help.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@vn86893,

Can you please try this?

YOUR_SEARCH | rename message.Value.Data.Items{}.FieldType as FieldType, message.Value.Data.Items{}.Value as Value,  responseData.Value.Data.EncryptedDocKey as EncryptedDocKey, responseData.Value.Data.Domain as Domain, responseData.Value.Data.Partner as Partner, responseData.Value.Data.Carrier as Carrier, responseData.Value.RequestTrackerId as RequestTrackerId  | table FieldType ,EncryptedDocKey,Domain,Partner,Carrier,RequestTrackerId

Here I have rename fields names with required names.

| makeresults
 | eval _raw = "{\"action\": \"Get\", \"applicationName\": \"abc\" ,\"controller\": \"Main\", \"ip\": \"123.123.123.123\", \"logLevel\": \"INFO\", \"loggerType\": \"abcdef\", \"machineName\": \"windows\", \"message\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"4\"},{\"FieldType\":\"abcd\",\"Value\":\"5\"},{\"FieldType\":\"123\",\"Value\":\"6\"}],\"EncryptedDocKey\":\"123456\",\"Domain\":\"Order\",\"Partner\":\"India\",\"Carrier\":\"Idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"},\"principalId\":\" 22222222-2222-2222-2222-222222222222\", \"requestMethod\": \"POST\", \"requestUrl\": \"https://abc.com/api/v1/get\", \"responseData\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"\"},{\"FieldType\":\"123\",\"Value\":\"\"},{\"FieldType\":\"xyz\",\"Value\":\"\"}],\"EncryptedDocKey\":\"123456789\",\"Domain\":\"Order\",\"Partner\":\"india\",\"Carrier\":\"idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"}, \"time\": \"2019-07-10 18:35:23.3893\", \"traceId\": \"12345678963525\", \"userName\": \"abc/12345\" }"  
 | append [ | makeresults
 | eval _raw = "{\"action\": \"Get\", \"applicationName\": \"def\" ,\"controller\": \"Main\", \"ip\": \"123.123.123.123\", \"logLevel\": \"INFO\", \"loggerType\": \"abcdef\", \"machineName\": \"windows\", \"message\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"def\",\"Value\":\"1\"},{\"FieldType\":\"defg\",\"Value\":\"2\"},{\"FieldType\":\"123\",\"Value\":\"3\"}],\"EncryptedDocKey\":\"123456\",\"Domain\":\"Order\",\"Partner\":\"India\",\"Carrier\":\"Idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"},\"principalId\":\" 1111-2222-2222-2222-222222222222\", \"requestMethod\": \"POST\", \"requestUrl\": \"https://abc.com/api/v1/get\", \"responseData\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"4\"},{\"FieldType\":\"123\",\"Value\":\"5\"},{\"FieldType\":\"xyz\",\"Value\":\"6\"}],\"EncryptedDocKey\":\"123123123\",\"Domain\":\"Order\",\"Partner\":\"UK\",\"Carrier\":\"idea123\"},\"RequestTrackerId\":\"1212345\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"}, \"time\": \"2019-07-10 18:35:23.3893\", \"traceId\": \"11111\", \"userName\": \"abc/1111\" }"  
 ]
 | extract | rename message.Value.Data.Items{}.FieldType as FieldType, message.Value.Data.Items{}.Value as Value,   responseData.Value.Data.EncryptedDocKey as EncryptedDocKey, responseData.Value.Data.Domain as Domain, responseData.Value.Data.Partner as Partner, responseData.Value.Data.Carrier as Carrier, responseData.Value.RequestTrackerId as RequestTrackerId  | table FieldType ,EncryptedDocKey,Domain,Partner,Carrier,RequestTrackerId

Note: As I have used makeresults for creating fake events, I have also used extract command for extracting fields. These are Splunk extracting automatically.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/makeresults

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Extract

Here you will get FieldType as multivalued field. If you want FieldType as single value related with other fields then just append below search,

 | mvexpand FieldType

Here I noticed there is Value field with FieldType field. If you want to get value from Value fields an want to display very next to FieldType then append this search.

| eval tmp=mvzip(FieldType,Value) | mvexpand tmp | eval FieldType=mvindex(split(tmp,","),0), Value=mvindex(split(tmp,","),1) | fields - tmp

Below is my finaly search with Value field.

| makeresults
 | eval _raw = "{\"action\": \"Get\", \"applicationName\": \"abc\" ,\"controller\": \"Main\", \"ip\": \"123.123.123.123\", \"logLevel\": \"INFO\", \"loggerType\": \"abcdef\", \"machineName\": \"windows\", \"message\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"4\"},{\"FieldType\":\"abcd\",\"Value\":\"5\"},{\"FieldType\":\"123\",\"Value\":\"6\"}],\"EncryptedDocKey\":\"123456\",\"Domain\":\"Order\",\"Partner\":\"India\",\"Carrier\":\"Idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"},\"principalId\":\" 22222222-2222-2222-2222-222222222222\", \"requestMethod\": \"POST\", \"requestUrl\": \"https://abc.com/api/v1/get\", \"responseData\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"\"},{\"FieldType\":\"123\",\"Value\":\"\"},{\"FieldType\":\"xyz\",\"Value\":\"\"}],\"EncryptedDocKey\":\"123456789\",\"Domain\":\"Order\",\"Partner\":\"india\",\"Carrier\":\"idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"}, \"time\": \"2019-07-10 18:35:23.3893\", \"traceId\": \"12345678963525\", \"userName\": \"abc/12345\" }"  
 | append [ | makeresults
 | eval _raw = "{\"action\": \"Get\", \"applicationName\": \"def\" ,\"controller\": \"Main\", \"ip\": \"123.123.123.123\", \"logLevel\": \"INFO\", \"loggerType\": \"abcdef\", \"machineName\": \"windows\", \"message\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"def\",\"Value\":\"1\"},{\"FieldType\":\"defg\",\"Value\":\"2\"},{\"FieldType\":\"123\",\"Value\":\"3\"}],\"EncryptedDocKey\":\"123456\",\"Domain\":\"Order\",\"Partner\":\"India\",\"Carrier\":\"Idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"},\"principalId\":\" 1111-2222-2222-2222-222222222222\", \"requestMethod\": \"POST\", \"requestUrl\": \"https://abc.com/api/v1/get\", \"responseData\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"4\"},{\"FieldType\":\"123\",\"Value\":\"5\"},{\"FieldType\":\"xyz\",\"Value\":\"6\"}],\"EncryptedDocKey\":\"123123123\",\"Domain\":\"Order\",\"Partner\":\"UK\",\"Carrier\":\"idea123\"},\"RequestTrackerId\":\"1212345\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"}, \"time\": \"2019-07-10 18:35:23.3893\", \"traceId\": \"11111\", \"userName\": \"abc/1111\" }"  
 ]
 | extract | rename message.Value.Data.Items{}.FieldType as FieldType, message.Value.Data.Items{}.Value as Value,   responseData.Value.Data.EncryptedDocKey as EncryptedDocKey, responseData.Value.Data.Domain as Domain, responseData.Value.Data.Partner as Partner, responseData.Value.Data.Carrier as Carrier, responseData.Value.RequestTrackerId as RequestTrackerId  | table traceId,userName,FieldType, Value,EncryptedDocKey,Domain,Partner,Carrier,RequestTrackerId | eval tmp=mvzip(FieldType,Value) | mvexpand tmp | eval FieldType=mvindex(split(tmp,","),0), Value=mvindex(split(tmp,","),1) | fields - tmp

Please try and let me know if any difficulty.

0 Karma

poete
Builder

Hello @vn86893,

please find the requst below :

| makeresults
| eval _raw = "{\"action\": \"Get\", \"applicationName\": \"abc\" ,\"controller\": \"Main\", \"ip\": \"123.123.123.123\", \"logLevel\": \"INFO\", \"loggerType\": \"abcdef\", \"machineName\": \"windows\", \"message\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"\"},{\"FieldType\":\"abcd\",\"Value\":\"\"},{\"FieldType\":\"123\",\"Value\":\"\"}],\"EncryptedDocKey\":\"123456\",\"Domain\":\"Order\",\"Partner\":\"India\",\"Carrier\":\"Idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"},\"principalId\":\" 22222222-2222-2222-2222-222222222222\", \"requestMethod\": \"POST\", \"requestUrl\": \"https://abc.com/api/v1/get\", \"responseData\": {\"Value\":{\"Data\":{\"Items\":[{\"FieldType\":\"abc\",\"Value\":\"\"},{\"FieldType\":\"123\",\"Value\":\"\"},{\"FieldType\":\"xyz\",\"Value\":\"\"}],\"EncryptedDocKey\":\"123456789\",\"Domain\":\"Order\",\"Partner\":\"india\",\"Carrier\":\"idea\"},\"RequestTrackerId\":\"7894561230\",\"Message\":\"OK\"},\"Formatters\":[],\"ContentTypes\":[],\"DeclaredType\":\"null\",\"StatusCode\":\"null\"}, \"time\": \"2019-07-10 18:35:23.3893\", \"traceId\": \"12345678963525\", \"userName\": \"abc/12345\" }"
| spath path=message.Value.Data.Items{}.FieldType
| spath path=message.Value.Data.EncryptedDocKey
| spath path=message.Value.Data.Domain
| spath path=message.Value.Data.Partner
| spath path=message.Value.Data.Carrier
| spath path=message.Value.RequestTrackerId
| rename message.Value.Data.Items{}.FieldType as FieldType message.Value.Data.EncryptedDocKey as EncryptedDocKey message.Value.Data.Domain as Domain message.Value.Data.Partner as Partner message.Value.Data.Carrier as Carrier message.Value.RequestTrackerId as RequestTrackerId
| table FieldType,EncryptedDocKey,Domain,Partner,Carrier,RequestTrackerId
0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...