Search time parsing for nested json logs

[AKG11]

Hi,

I am looking to parse the nested JSON events. basically need to break them into multiple events.

I an trying some thing like this but its just duplicating same record in multiple lines.

| spath path=list.entry{}.fields output=items
| mvexpand items

I am looking to get all key/vale pair as single event under  "fields" 

Sample Records

{
    "total": 64,
    "list": {
        "entry": [
            {
                "recordId": 7,
                "created": 1682416024092,
                "id": "e70dbd86-53cf-4782-aa84-cf28cde16c86",
                "fields": {
                    "NumDevRes001": 11111,
                    "NumBARes001": 3,    
                    "lastUpdated": 1695960000000,
                    "engStartDate": 1538452800000,
                    "RelSupport001": 0,
                    "UnitTest001": 0,
                    "Engaged": 1,
                    "ProdGroup001": 1,
                    "QEResSGP001": 0.5,
                    "QEResTOR001": 1,
                    "QEResLoc001": 3,
                    "SITBugs001": 31,
                    "QEResIND001": 5,
                    "QEResLoc003": 3,
                    "QEResLoc002": 3,
                    "Project": "Registration Employee Directory Services",
                    "AutoTestCount001": 1657,
                    "AppKey001": "ABC",

                },
                "ownedBy": "TEST1"
            },
            {
                "recordId": 8,
                "createdBy": "TEST2",
                "created": 1682416747947,
                "id": "91e88ae6-0b64-48fc-b8ed-4fcfa399aa3e",
                "fields": {
                    "NumDevRes001": 22222,
                    "NumBARes001": 3,    
                    "lastUpdated": 1695960000000,
                    "engStartDate": 1538452800000,
                    "RelSupport001": 0,
                    "UnitTest001": 0,
                    "Engaged": 1,
                    "ProdGroup001": 1,
                    "QEResSGP001": 0.5,
                    "QEResTOR001": 1,
                    "QEResLoc001": 3,
                    "SITBugs001": 31,
                    "QEResIND001": 5,
                    "QEResLoc003": 3,
                    "QEResLoc002": 3,
                    "Project": "Registration Employee Directory Services",
                    "AutoTestCount001": 1657,
                    "AppKey001": "ABC",
                },
                "ownedBy": "TEST2"
            }
        ]
    }
}

[yuanliu · Accepted solution]

list.entry{}.fields is not itself a valid JSON path, but merely Splunk's own flat representation of one element in JSON array list.entry[].  Therefore it cannot be used in spath command.  Splunk's representation of JSON array is {}, such as list.entry{}.  The search you are looking for is

| fields - list.entry{}.* ``` these are distractions if you want to access full array ```
| spath path=list.entry{}
| mvexpand list.entry{}
| spath input=list.entry{}

After this, you will have JSON node list[].fields.* extracted as fields.*.  Your sample data would give

created	createdBy	fields.AppKey001	fields.AutoTestCount001	fields.Engaged	fields.NumBARes001	fields.NumDevRes001	fields.ProdGroup001	fields.Project	fields.QEResIND001	fields.QEResLoc001	fields.QEResLoc002	fields.QEResLoc003	fields.QEResSGP001	fieldsQEResTOR001	fields.RelSupport001	fields.SITBugs001	fields.UnitTest001	fields.engStartDate	fields.lastUpdated	id	list.entry{}	ownedBy	recordId	total
1682416024092		ABC	1657	1	3	11111	1	Registration Employee Directory Services	5	3	3	3	0.5	1	0	31	0	1538452800000	1695960000000	e70dbd86-53cf-4782-aa84-cf28cde16c86	{ "recordId": 7, "created": 1682416024092, "id": "e70dbd86-53cf-4782-aa84-cf28cde16c86", "fields": { "NumDevRes001": 11111, "NumBARes001": 3, "lastUpdated": 1695960000000, "engStartDate": 1538452800000, "RelSupport001": 0, "UnitTest001": 0, "Engaged": 1, "ProdGroup001": 1, "QEResSGP001": 0.5, "QEResTOR001": 1, "QEResLoc001": 3, "SITBugs001": 31, "QEResIND001": 5, "QEResLoc003": 3, "QEResLoc002": 3, "Project": "Registration Employee Directory Services", "AutoTestCount001": 1657, "AppKey001": "ABC" }, "ownedBy": "TEST1" }	TEST1	7	64
1682416747947	TEST2	ABC	1657	1	3	22222	1	Registration Employee Directory Services	5	3	3	3	0.5	1	0	31	0	1538452800000	1695960000000	91e88ae6-0b64-48fc-b8ed-4fcfa399aa3e	{ "recordId": 8, "createdBy": "TEST2", "created": 1682416747947, "id": "91e88ae6-0b64-48fc-b8ed-4fcfa399aa3e", "fields": { "NumDevRes001": 22222, "NumBARes001": 3, "lastUpdated": 1695960000000, "engStartDate": 1538452800000, "RelSupport001": 0, "UnitTest001": 0, "Engaged": 1, "ProdGroup001": 1, "QEResSGP001": 0.5, "QEResTOR001": 1, "QEResLoc001": 3, "SITBugs001": 31, "QEResIND001": 5, "QEResLoc003": 3, "QEResLoc002": 3, "Project": "Registration Employee Directory Services", "AutoTestCount001": 1657, "AppKey001": "ABC" }, "ownedBy": "TEST2" }	TEST2	8	64

Here is an emulation (correcting for one minor JSON syntax error) you can play with and compare with real data

| makeresults
| eval _raw = "{
    \"total\": 64,
    \"list\": {
        \"entry\": [
            {
                \"recordId\": 7,
                \"created\": 1682416024092,
                \"id\": \"e70dbd86-53cf-4782-aa84-cf28cde16c86\",
                \"fields\": {
                    \"NumDevRes001\": 11111,
                    \"NumBARes001\": 3,    
                    \"lastUpdated\": 1695960000000,
                    \"engStartDate\": 1538452800000,
                    \"RelSupport001\": 0,
                    \"UnitTest001\": 0,
                    \"Engaged\": 1,
                    \"ProdGroup001\": 1,
                    \"QEResSGP001\": 0.5,
                    \"QEResTOR001\": 1,
                    \"QEResLoc001\": 3,
                    \"SITBugs001\": 31,
                    \"QEResIND001\": 5,
                    \"QEResLoc003\": 3,
                    \"QEResLoc002\": 3,
                    \"Project\": \"Registration Employee Directory Services\",
                    \"AutoTestCount001\": 1657,
                    \"AppKey001\": \"ABC\"

                },
                \"ownedBy\": \"TEST1\"
            },
            {
                \"recordId\": 8,
                \"createdBy\": \"TEST2\",
                \"created\": 1682416747947,
                \"id\": \"91e88ae6-0b64-48fc-b8ed-4fcfa399aa3e\",
                \"fields\": {
                    \"NumDevRes001\": 22222,
                    \"NumBARes001\": 3,    
                    \"lastUpdated\": 1695960000000,
                    \"engStartDate\": 1538452800000,
                    \"RelSupport001\": 0,
                    \"UnitTest001\": 0,
                    \"Engaged\": 1,
                    \"ProdGroup001\": 1,
                    \"QEResSGP001\": 0.5,
                    \"QEResTOR001\": 1,
                    \"QEResLoc001\": 3,
                    \"SITBugs001\": 31,
                    \"QEResIND001\": 5,
                    \"QEResLoc003\": 3,
                    \"QEResLoc002\": 3,
                    \"Project\": \"Registration Employee Directory Services\",
                    \"AutoTestCount001\": 1657,
                    \"AppKey001\": \"ABC\"
                },
                \"ownedBy\": \"TEST2\"
            }
        ]
    }
}"
| spath
``` data emulation above ```

[ITWhisperer]

Do you mean something like this?

| spath list.entry{}.fields output=items
| mvexpand items
| spath input=items
| fields - _raw items