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 (3)
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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...