Search time parsing for nested json logs

AKG11 — Thu, 05 Oct 2023 09:31:04 GMT

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

Re: Search time parsing for nested json logs

ITWhisperer — Thu, 05 Oct 2023 11:29:09 GMT

Do you mean something like this?

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

Re: Search time parsing for nested json logs

yuanliu — Thu, 05 Oct 2023 17:39:33 GMT

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

topic Re: Search time parsing for nested json logs in Splunk Search

Search time parsing for nested json logs

Re: Search time parsing for nested json logs

Re: Search time parsing for nested json logs