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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...