Getting Data In

Extracting Fields and Values from JSON Data

ahallak2016
Explorer

I have the following Dataset:

{
    "createFormInstanceRequest": {
        "formId": "xxxxxxxxxxxxxxxxxxxxxxx",
        "requestOptions": {
            "requestAction": "SUBMIT"
        },
        "responseOptions": {
            "returnFormDefinition": false,
            "returnFormInfo": false,
            "returnFormData": true
        },
        "formData": {
            "groups": [{
                "groupId": "studentDetails",
                "iterations": [
                    [{
                        "fieldId": "studentName",
                        "value": ["timothy jones"]
                    }, {
                        "fieldId": "studentid",
                        "value": ["sxxxxxx"]
                    }, {
                        "fieldId": "address",
                        "value": ["12/12 some street"]
                    }, {
                        "fieldId": "attendingevent",
                        "value": ["No"]
                    }]
                ]
            }, {
                "groupId": "grades",
                "iterations": [
                    [{
                        "fieldId": "math",
                        "value": ["C"]
                    }, {
                        "fieldId": "PE",
                        "value": ["D"]
                    }, {
                        "fieldId": "english",
                        "value": ["A"]
                    }, {
                        "fieldId": "science",
                        "value": ["B"]
                    }]
                ]
            }]
        }
    }
}

I am struggling to parse the field names and values as everything is placed in either: createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId and createFormInstanceRequest.formData.groups{}.iterations{}{}.value{}.

They both become multivalue fields.

I couldn't get SPATH to work with this data and changing the log format is not an option either.

How can I create fields based on the fieldIDS and assign them the corresponding values in search time?

Tags (2)
0 Karma

niketn
Legend

Following is a run anywhere search to get single value mapping between fieldId and value instead of multivalue. I have used spath. However, you can use pipe commands from mvzip() onward.

| makeresults
| eval jsonData="
 {
     \"createFormInstanceRequest\": {
         \"formId\": \"xxxxxxxxxxxxxxxxxxxxxxx\",
         \"requestOptions\": {
             \"requestAction\": \"SUBMIT\"
         },
         \"responseOptions\": {
             \"returnFormDefinition\": false,
             \"returnFormInfo\": false,
             \"returnFormData\": true
         },
         \"formData\": {
             \"groups\": [{
                 \"groupId\": \"studentDetails\",
                 \"iterations\": [
                     [{
                         \"fieldId\": \"studentName\",
                         \"value\": [\"timothy jones\"]
                     }, {
                         \"fieldId\": \"studentid\",
                         \"value\": [\"sxxxxxx\"]
                     }, {
                         \"fieldId\": \"address\",
                         \"value\": [\"12/12 some street\"]
                     }, {
                         \"fieldId\": \"attendingevent\",
                         \"value\": [\"No\"]
                     }]
                 ]
             }, {
                 \"groupId\": \"grades\",
                 \"iterations\": [
                     [{
                         \"fieldId\": \"math\",
                         \"value\": [\"C\"]
                     }, {
                         \"fieldId\": \"PE\",
                         \"value\": [\"D\"]
                     }, {
                         \"fieldId\": \"english\",
                         \"value\": [\"A\"]
                     }, {
                         \"fieldId\": \"science\",
                         \"value\": [\"B\"]
                     }]
                 ]
             }]
         }
     }
 }
"
| spath input=jsonData path=createFormInstanceRequest.formId output=formId
| spath input=jsonData path=createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId output=fieldId
| spath input=jsonData path=createFormInstanceRequest.formData.groups{}.iterations{}{}.value{} output=value
| eval fieldValue=mvzip(fieldId,value)
| mvexpand fieldValue
| eval fieldValue=split(fieldValue,",")
| eval fieldId=mvindex(fieldValue,0)
| eval value=mvindex(fieldValue,1)
| table formId fieldId value

PS: I have retained formId if you want to perform further correlation with SPL.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ahallak2016
Explorer

Your solution worked once I removed the input parameter as I am searching in my index. It does create the mappings correctly but it does not create fields from the fieldIds.

I guess I was trying to replicate what the sourcetype should have done.

0 Karma

niketn
Legend

@ahallak2016, yes first part of my query until spath commands were to create mock data. You do not require them.

I had messed up couple of field names as per your question. Possibly because of the same your query is not working as expected. Can you try with the following search?

 index=myIndex sourcetype=json
| rename "createFormInstanceRequest.formData.groups{}.iterations{}{}.value{}" as values,"createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId" as fields, "createFormInstanceRequest.formId" as formId 
| eval fieldValue=mvzip(fields,values)
| mvexpand fieldValue
| eval fieldValue=split(fieldValue,",")
| eval fields=mvindex(fieldValue,0)
| eval values=mvindex(fieldValue,1)
| table formId fields values
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ahallak2016
Explorer

Ok, I have come up with a work around, its functional.. but it looks horrible:

index=myIndex sourcetype=json | rename "createFormInstanceRequest.formData.groups{}.iterations{}{}.value{}" as values,"createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId" as fields 
    | eval var0 = mvindex(fields,0) 
    | eval var1 = mvindex(fields,1) 
    | eval var2 = mvindex(fields,2) 
    | eval var3 = mvindex(fields,3) 
    | eval var4 = mvindex(fields,4) 
    | eval var5 = mvindex(fields,5) 
    | eval var6 = mvindex(fields,6) 
    | eval var7 = mvindex(fields,7)

    | eval {var0} = mvindex(values,0)
    | eval {var1} = mvindex(values,1)
    | eval {var2} = mvindex(values,2)
    | eval {var3} = mvindex(values,3)
    | eval {var4} = mvindex(values,4)
    | eval {var5} = mvindex(values,5)
    | eval {var6} = mvindex(values,6)
    | eval {var7} = mvindex(values,7)

With this, the values of fieldID will be the field names of the actual values relating to student.

0 Karma

niketn
Legend

@ahallak2016, will you always have 7 values?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ahallak2016
Explorer

At this point yes. I don't know if this will change in the future.

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