Splunk Search

Search time parsing for nested json logs

AKG11
Path Finder

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"
            }
        ]
    }
}

 

 

 

 


 

Labels (2)
Tags (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

createdcreatedByfields.AppKey001fields.AutoTestCount001fields.Engagedfields.NumBARes001fields.NumDevRes001fields.ProdGroup001fields.Projectfields.QEResIND001fields.QEResLoc001fields.QEResLoc002fields.QEResLoc003fields.QEResSGP001fieldsQEResTOR001fields.RelSupport001fields.SITBugs001fields.UnitTest001fields.engStartDatefields.lastUpdatedidlist.entry{}ownedByrecordIdtotal
1682416024092 ABC165713111111Registration Employee Directory Services53330.51031015384528000001695960000000e70dbd86-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" }TEST1764
1682416747947TEST2ABC165713222221Registration Employee Directory Services53330.5103101538452800000169596000000091e88ae6-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" }TEST2864

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

 

  

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

createdcreatedByfields.AppKey001fields.AutoTestCount001fields.Engagedfields.NumBARes001fields.NumDevRes001fields.ProdGroup001fields.Projectfields.QEResIND001fields.QEResLoc001fields.QEResLoc002fields.QEResLoc003fields.QEResSGP001fieldsQEResTOR001fields.RelSupport001fields.SITBugs001fields.UnitTest001fields.engStartDatefields.lastUpdatedidlist.entry{}ownedByrecordIdtotal
1682416024092 ABC165713111111Registration Employee Directory Services53330.51031015384528000001695960000000e70dbd86-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" }TEST1764
1682416747947TEST2ABC165713222221Registration Employee Directory Services53330.5103101538452800000169596000000091e88ae6-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" }TEST2864

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

 

  

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Do you mean something like this?

| spath list.entry{}.fields output=items
| mvexpand items
| spath input=items
| fields - _raw items
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 ...