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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...