Getting Data In

Get dynamic json property names

tmortiboy
New Member

I have some structured json logs that indicate some validation errors, and depending on the error, a different property is set with the value that caused the error. eg:

{ "validationError": { "property" : { "title" : "" }, "errorType" : "Empty" } }
{ "validationError": { "property" : { "category" : [] }, "errorType" : "Empty" } }
{ "validationError": { "property" : { "category" : [ { "categoryId" : 12345 } ] }, "errorType" : "Invalid" } }
{ "validationError": { "property" : { "thing" : { "thingId" : 9999 } }, "errorType" : "Disallowed" } }

I would like to be able to retrieve the property name and its value: eg
property | value | type
---------+-------+-----------
title | | Empty
category | | Empty
category | 12345 | Invalid
thing | 9999 | Disallowed

0 Karma
1 Solution

javiergn
Super Champion

So assuming your field is called yourField, I came up with the following (probably overcomplicated) query:

| rex max_match=0 "(?m)(?<yourField>[^\n]+)"
| fields - _raw
| mvexpand yourField
| spath input=yourField path=validationError.property output=propertyRaw
| spath input=yourField path=validationError.errorType output=type
| fields - yourField
| spath input=propertyRaw
| rex field=propertyRaw max_match=1 "(?msi)\"(?<property>[^\"]+)\"\s?:"
| fields - propertyRaw
| eval value = ""
| eval temp = 'category{}.categoryId'
| foreach * [ eval temp = if (match("<<FIELD>>", property), toString('<<FIELD>>'), "") | eval value = value . temp]
| fields property, value, type
| foreach * [ eval <<FIELD>> = if(match(<<FIELD>>, "Null"), null(), <<FIELD>>) ]

Example:

| stats count | fields - count
| eval _raw = "
{ \"validationError\": { \"property\" : { \"title\" : \"\" }, \"errorType\" : \"Empty\" } }
{ \"validationError\": { \"property\" : { \"category\" : [] }, \"errorType\" : \"Empty\" } }
{ \"validationError\": { \"property\" : { \"category\" : [ { \"categoryId\" : 12345 } ] }, \"errorType\" : \"Invalid\" } }
{ \"validationError\": { \"property\" : { \"thing\" : { \"thingId\" : 9999 } }, \"errorType\" : \"Disallowed\" } }
"
| rex max_match=0 "(?m)(?<yourField>[^\n]+)"
| fields - _raw
| mvexpand yourField
| spath input=yourField path=validationError.property output=propertyRaw
| spath input=yourField path=validationError.errorType output=type
| fields - yourField
| spath input=propertyRaw
| rex field=propertyRaw max_match=1 "(?msi)\"(?<property>[^\"]+)\"\s?:"
| fields - propertyRaw
| eval value = ""
| eval temp = 'category{}.categoryId'
| foreach * [ eval temp = if (match("<<FIELD>>", property), toString('<<FIELD>>'), "") | eval value = value . temp]
| fields property, value, type
| foreach * [ eval <<FIELD>> = if(match(<<FIELD>>, "Null"), null(), <<FIELD>>) ]

Output, see picture below:

alt text

View solution in original post

0 Karma

javiergn
Super Champion

So assuming your field is called yourField, I came up with the following (probably overcomplicated) query:

| rex max_match=0 "(?m)(?<yourField>[^\n]+)"
| fields - _raw
| mvexpand yourField
| spath input=yourField path=validationError.property output=propertyRaw
| spath input=yourField path=validationError.errorType output=type
| fields - yourField
| spath input=propertyRaw
| rex field=propertyRaw max_match=1 "(?msi)\"(?<property>[^\"]+)\"\s?:"
| fields - propertyRaw
| eval value = ""
| eval temp = 'category{}.categoryId'
| foreach * [ eval temp = if (match("<<FIELD>>", property), toString('<<FIELD>>'), "") | eval value = value . temp]
| fields property, value, type
| foreach * [ eval <<FIELD>> = if(match(<<FIELD>>, "Null"), null(), <<FIELD>>) ]

Example:

| stats count | fields - count
| eval _raw = "
{ \"validationError\": { \"property\" : { \"title\" : \"\" }, \"errorType\" : \"Empty\" } }
{ \"validationError\": { \"property\" : { \"category\" : [] }, \"errorType\" : \"Empty\" } }
{ \"validationError\": { \"property\" : { \"category\" : [ { \"categoryId\" : 12345 } ] }, \"errorType\" : \"Invalid\" } }
{ \"validationError\": { \"property\" : { \"thing\" : { \"thingId\" : 9999 } }, \"errorType\" : \"Disallowed\" } }
"
| rex max_match=0 "(?m)(?<yourField>[^\n]+)"
| fields - _raw
| mvexpand yourField
| spath input=yourField path=validationError.property output=propertyRaw
| spath input=yourField path=validationError.errorType output=type
| fields - yourField
| spath input=propertyRaw
| rex field=propertyRaw max_match=1 "(?msi)\"(?<property>[^\"]+)\"\s?:"
| fields - propertyRaw
| eval value = ""
| eval temp = 'category{}.categoryId'
| foreach * [ eval temp = if (match("<<FIELD>>", property), toString('<<FIELD>>'), "") | eval value = value . temp]
| fields property, value, type
| foreach * [ eval <<FIELD>> = if(match(<<FIELD>>, "Null"), null(), <<FIELD>>) ]

Output, see picture below:

alt text

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...