How to extract json with filter

MirrorCraze — Wed, 13 Dec 2023 20:41:57 GMT

I have a data like this.

{
   env: prod
   host: prod01
   name: appName
   info: {
     data: [ ...
     ]
     indicators: [
       {
         details: {

A.runTime: 434
A.Count: 0
           B.runTime: 0
           B.Count: 0
....

}
         name: timeCountIndicator
         status: UP
       }
       {
         details: {

A.downCount: 2
A.nullCount: 0
           B.downCount: 0
           B.nullCount: 0
....

   }
         name: downCountIndicator
         status: UP
       }
     ]
     status: DOWN
   }
   metrics: { ...
   }
   ping: 1
}

I only want to extract fields in info.indicators{}.details ONLY when info.indicators{}.name of that field is "timeCountIndicator". I tried to use spath combined with table, mvexpand and where

... | spath path=info.indicators{} output=indicators | table indicators |mvexpand indicators| where match(indicators,"timeCountIndicator")

It returns a record as a string, however. And it's really hard to convert string back to fields which is hard to process. (Technically extract/rex can deal with it, but it takes a REALLY long time to extract every fields in details when I need only some fields)

Is there any way to deal with this in the easier way?

Re: How to extract json with filter

dtburrows3 — Wed, 13 Dec 2023 21:19:45 GMT

So there are two ways I can think of parsing this

One using MVExpand

<base_search> | spath path=info.indicators{} output=indicators | table indicators | eval time_count_indicator_json=case( isnull(indicators), null(), mvcount(indicators)==1, if(spath(indicators, "name")=="timeCountIndicator", 'indicators', null()), mvcount(indicators)>1, mvmap(indicators, if(spath(indicators, "name")=="timeCountIndicator", 'indicators', null())) ) | fields - indicators ``` Method 1 using MVExpand ``` | mvexpand time_count_indicator_json | spath input=time_count_indicator_json | fields - time_count_indicator_json

and another that is parsing an array of json_objects matching your criteria of only events "timeCountIndicator"

<base_search> | spath path=info.indicators{} output=indicators | table indicators | eval time_count_indicator_json=case( isnull(indicators), null(), mvcount(indicators)==1, if(spath(indicators, "name")=="timeCountIndicator", 'indicators', null()), mvcount(indicators)>1, mvmap(indicators, if(spath(indicators, "name")=="timeCountIndicator", 'indicators', null())) ) | fields - indicators ``` Method 2 parsing MV Field as array of json_objects ``` | eval time_count_indicator_json_array="[".mvjoin(time_count_indicator_json, ",")."]" | spath input=time_count_indicator_json_array | fields - time_count_indicator_json, time_count_indicator_json_array | rename "{}.*" as *

I personally find the mvexpand method to be a much cleaner output to work with.

Method 2 could potentially lead to mvfields being unaligned if any of the json_objects have a null value for that field.

But depend on the use case and data volume you are trying to parse because mvexpand can be memory intensive.

SPL used to replicate:

| makeresults | eval _raw="{\"env\": \"prod\", \"host\": \"prod\", \"name\": \"appName\", \"info\": {\"data\": [], \"indicators\": [{\"details\": {\"A.runTime\": 434, \"A.Count\": 0, \"B.runTime\": 0, \"B.Count\": 0}, \"name\": \"timeCountIndicator\", \"status\": \"UP\"}, {\"details\": {\"A.downCount\": 2, \"A.nullCount\": 0, \"B.downCount\": 0, \"B.nullCount\": 0}, \"name\": \"downCountIndicator\", \"status\": \"UP\"}, {\"details\": {\"A.runTime\": 333, \"A.Count\": 2, \"B.runTime\": 21, \"B.Count\": 4}, \"name\": \"timeCountIndicator\", \"status\": \"UP\"}], \"status\": \"DOWN\"}, \"metrics\": {}, \"ping\": 1}" | spath path=info.indicators{} output=indicators | table indicators | eval time_count_indicator_json=case( isnull(indicators), null(), mvcount(indicators)==1, if(spath(indicators, "name")=="timeCountIndicator", 'indicators', null()), mvcount(indicators)>1, mvmap(indicators, if(spath(indicators, "name")=="timeCountIndicator", 'indicators', null())) ) | fields - indicators ``` Method 1 using MVExpand ``` | mvexpand time_count_indicator_json | spath input=time_count_indicator_json | fields - time_count_indicator_json ``` Method 2 parsing MV Field as array of json_objects ``` ``` | eval time_count_indicator_json_array="[".mvjoin(time_count_indicator_json, ",")."]" | spath input=time_count_indicator_json_array | fields - time_count_indicator_json, time_count_indicator_json_array | rename "{}.*" as * ```

topic How to extract json with filter in Splunk Search

How to extract json with filter

Re: How to extract json with filter