Can you help me extract field and value pairs from a JSON log?

[gcusello]

Hi at all,

I searched through past answers, but I couldn't reach to adapt some of them to my data:

I have JSON data and I need to extract field/value pairs from a JSON log.

The problem is that I have many field/value pairs and I'm not able to extract all pairs with a single command. The only solution I found is to have a spath extraction for each field, but I think that there must be an easier solution.

this is an example of my data:

{
   "Request":{
      "ChannelTracker":false,
      "AutoCreateUserFlag":true,
      "AutoCreateUserFlagTracker":true,
      "ClientReturnDataTracker":false,
      "CollectionRequestTracker":false,
      "CredentialDataListTracker":false,
      "DeviceManagementRequest":{
         "ActionTypeList":{
            "DeviceActionTypes":[
               {
                  "DeviceActionType":"UPDATE_DEVICE"
               }
            ],
            "DeviceActionTypesTracker":true
         },
         "ActionTypeListTracker":true,
         "DeviceData":{
            "BindingType":{
               "BindingType":"HARD_BIND"
            },
            "BindingTypeTracker":true,
            "DeviceTokenCookieTracker":false,
            "DeviceTokenFSOTracker":false,
            "LookupLabelTracker":false,
            "NewLabelTracker":false
         },
         "DeviceDataTracker":true
      },
      "DeviceManagementRequestTracker":true,
      "EventDataList":{
         "EventData":[
            {
               "AuthenticationLevelTracker":false,
               "ClientDefinedAttributeList":{
                  "Fact":[
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"customer",
                        "NameTracker":true,
                        "Value":"12345",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"LIST",
                        "NameTracker":true,
                        "Value":"12345",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"userAgent",
                        "NameTracker":true,
                        "Value":"mozilla/5.0 (windows nt 10.0; win64; x64) applewebkit/537.36 (khtml, like gecko) chrome/69.0.3497.100 safari/537.36",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"userAgent",
                        "NameTracker":true,
                        "Value":"mozilla/5.0 (windows nt 10.0; win64; x64) applewebkit/537.36 (khtml, like gecko) chrome/69.0.3497.100 safari/537.36",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"deviceHash",
                        "NameTracker":true,
                        "Value":"xxxxx33",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"deviceScreen",
                        "NameTracker":true,
                        "Value":"24|1536|864|824",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"proxyDetectionTime",
                        "NameTracker":true,
                        "Value":"[null, null]",
                        "ValueTracker":true
                     },
                     {
                        "DataType":{
                           "DataType":"STRING"
                        },
                        "DataTypeTracker":true,
                        "Name":"deviceUserAgent",
                        "NameTracker":true,
                        "Value":"mozilla/5.0 (windows nt 10.0; win64; x64) applewebkit/537.36 (khtml, like gecko) chrome/69.0.3497.100 safari/537.36|5.0 (windows nt 10.0; win64; x64) applewebkit/537.36 (khtml, like gecko) chrome/69",
                        "ValueTracker":true
                     }
                  ],
                  "FactTracker":true
               },
               "ClientDefinedAttributeListTracker":true,
               "ClientDefinedEventType":"ENTRATA_IN_RELAZIONE",
               "ClientDefinedEventTypeTracker":true,
               "EventDescription":"LOGIN SUCCESS",
               "EventDescriptionTracker":true,
               "EventIdTracker":false,
               "EventReferenceIdTracker":false,
               "EventType":{
                  "EventType":"ENROLL"
               },
               "EventTypeTracker":true,
               "NewUserDataTracker":false,
               "StockTradeDataTracker":false,
               "TimeOfOccurrenceTracker":false,
               "TransactionDataTracker":false
            }
         ],
         "EventDataTracker":true
      },
      "RunRiskType":{
         "RunRiskType":"ALL"
      },
      "UserDataTracker":false,
      "ChannelIndicator":{
         "ChannelIndicatorType":"WEB"
      },
      "ChannelIndicatorTracker":true,
      "ClientDefinedChannelIndicator":"mychan",
      "ClientDefinedChannelIndicatorTracker":true,
      "ActionTypeList":{
         "GenericActionTypes":[
            {
               "GenericActionType":"SET_USER_STATUS"
            }
         ],
         "GenericActionTypesTracker":true
      },
      "ActionTypeListTracker":true,
      "ConfigurationHeaderTracker":false,
      "DeviceRequest":{
         "BeaconIdTracker":false,
         "DevicePrint":"version%3D3%2E0%2E0%2E0%5F5%26pm%5Ffpua%3Dmozilla%2F5%2E0%20%28windows%20nt%2010%2E0%3B%20win64%3B%20x64%29%20applewebkit%2F537%2E36%20%28khtml%2C%20like%20gecko%29%20chrome%2F69%2E0%2E3497%2E100%20safari%2F537%2E36%7C5%2E0%20%28Windows%20NT%2010%2E0%3B%20Win64%3B%20x64%29%20AppleWebKit%2F537%2E36%20%28KHTML%2C%20like%20Gecko%29%20Chrome%2F69%2E0%2E3497%2E100%20Safari%2F537%2E36%7CWin32%26pm%5Ffpsc%3D24%7C1536%7C864%7C824%26pm%5Ffpsw%3D%26pm%5Ffptz%3D2%26pm%5Ffpln%3Dlang%3Dit%7Csyslang%3D%7Cuserlang%3D%26pm%5Ffpjv%3D0%26pm%5Ffpco%3D1%26pm%5Ffpasw%3Dinternal%2Dpdf%2Dviewer%7Cmhjfbmdgcfjbbpaeojofohoefgiehjai%7Cinternal%2Dnacl%2Dplugin%26pm%5Ffpan%3DNetscape%26pm%5Ffpacn%3DMozilla%26pm%5Ffpol%3Dtrue%26pm%5Ffposp%3D%26pm%5Ffpup%3D%26pm%5Ffpsaw%3D1536%26pm%5Ffpspd%3D24%26pm%5Ffpsbd%3D%26pm%5Ffpsdx%3D%26pm%5Ffpsdy%3D%26pm%5Ffpslx%3D%26pm%5Ffpsly%3D%26pm%5Ffpsfse%3D%26pm%5Ffpsui%3D%26pm%5Fos%3DWindows%26pm%5Fbrmjv%3D69%26pm%5Fbr%3DChrome%26pm%5Finpt%3D%26pm%5Fexpt%3D",
         "DevicePrintTracker":true,
         "DeviceTokenCookieTracker":false,
         "DeviceTokenFSOTracker":false,
         "HttpAccept":"application/json, text/plain, */*",
         "HttpAcceptTracker":true,
         "HttpAcceptChars":"*",
         "HttpAcceptCharsTracker":true,
         "HttpAcceptEncoding":"*",
         "HttpAcceptEncodingTracker":true,
         "HttpAcceptLanguage":"it, en",
         "HttpAcceptLanguageTracker":true,
         "HttpReferrer":"https://my_app/",
         "HttpReferrerTracker":true,
         "IpAddress":"111.111.111.111",
         "IpAddressTracker":true,
         "UserAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36",
         "UserAgentTracker":true,
         "GeoLocationTracker":false,
         "DomElementsTracker":false,
         "JsEventsTracker":false,
         "PageIdTracker":false,
         "DeviceIdentifierTracker":false
      },
      "DeviceRequestTracker":true,
      "IdentificationData":{
         "ClientSessionIdTracker":false,
         "ClientTransactionId":"11111111-1111-1111-1111-111111111111",
         "ClientTransactionIdTracker":true,
         "Delegated":false,
         "DelegatedTracker":false,
         "GroupNameTracker":false,
         "NewUserNameTracker":false,
         "OrgName":"my_org",
         "OrgNameTracker":true,
         "SessionIdTracker":false,
         "TransactionIdTracker":false,
         "UserCountryTracker":false,
         "UserLanguageTracker":false,
         "UserLoginName":"12345",
         "UserLoginNameTracker":true,
         "UserName":"12345",
         "UserNameTracker":true,
         "UserStatus":{
            "UserStatus":"VERIFIED"
         },
         "UserStatusTracker":true,
         "UserType":{
            "WSUserType":"PERSISTENT"
         },
         "UserTypeTracker":true
      },
      "IdentificationDataTracker":true,
      "MessageHeader":{
         "ApiType":{
            "APIType":"DIRECT_SOAP_API"
         },
         "ApiTypeTracker":true,
         "RequestIdTracker":false,
         "RequestType":{
            "RequestType":"ANALYZE"
         },
         "RequestTypeTracker":true,
         "TimeStamp":"",
         "TimeStampTracker":true,
         "Version":{
            "MessageVersion":"7.0"
         },
         "VersionTracker":true
      },
      "SecurityHeader":{
         "CallerId":"callerId",
         "Method":{
            "AuthorizationMethod":"PASSWORD"
         }
      }
   }
}

I need to extarct the pair Name/Value (highlighted) in "Request.EventDataList.EventData.ClientDefinedAttributeList.Fact".

Thank you for your attention.

Bye.
Giuseppe

[gcusello · Accepted solution]

Hi at all,
I found by myself (with a friend) the solution to my question:

index=my_index  
| eval req_cap_attr = spath(_raw, "Request.EventDataList.EventData{}.ClientDefinedAttributeList.Fact{}")
| mvexpand req_cap_attr
| rex field=req_cap_attr "\"Name\":\"(?<key>[^\"]*)\",\"NameTracker\":\w+,\"Value\":\"(?<value>[^\"]*)\""
| eval {key}=value
| table *

Bye.
Giuseppe