json parsing using spath

[vn86893]

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.

[kamlesh_vaghela]

@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.

[poete]

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