How to extract JSON with key name specified as a key?

[sddunne]

Hi all, 

I have a JSON payload that contains as 'custom_fields' section that is made up of a set of title:keyname and value:value.

This is because the tool has varying formats of key/value pairs that it outputs. 

Per the screenshot, i'm trying to figure out a way to extract any values where the key is title:Mode but can't for the life of me work out how to do it. 

The Mode key will have 1 of 2 values (Monitored or Remediated) and wanting to show this in a table that includes other values from the overall json packet (e.g. _time, description etc.)

Any ideas would be greatly appreciated as spent several hours trying!

[somesoni2]

Try something like this (run anywhere sample search with similar data)

| makeresults | eval _raw="{
    \"time\": \"2022-03-18 15:35:19,997\",
    \"field1\": \"dummy\",
    \"custom_fields\":[    
    {\"title\":\"Resource ID\", \"value\":\"ram@gmail.com\", \"short\":true},    
    {\"title\":\"Mode\", \"value\":\"Remediated\"},  
    {\"title\":\"Performed By\", \"value\":\"john@gmail.com\", \"short\":true}  
]}  
}" |spath 
| rex "\"title\"\:\s*\"Mode\",\s*\"value\"\:\s*\"(?<Mode>[^\"]+)\""

[yuanliu]

Alternatively, try optional argument path if "Mode" is always the second element in the array.  Using the same simulated input somesoni2 provided,

| spath path=custom_fields{1}
| rename _raw AS temp
| rename custom_fields{1} AS _raw
| spath
| rename temp AS _raw

gives output

_raw	_time	title	value
{ "time": "2022-03-18 15:35:19,997", "field1": "dummy", "custom_fields":[ {"title":"Resource ID", "value":"ram@gmail.com", "short":true}, {"title":"Mode", "value":"Remediated"}, {"title":"Performed By", "value":"john@gmail.com", "short":true} ]} }	2022-03-19 01:07:27	Mode	Remediated

 Is this something that fits the need?

If the order is not guaranteed, you can even do foreach on them, like

| foreach 0 1 2
    [ spath path=custom_fields{<<FIELD>>}
| rename _raw AS temp
| rename custom_fields{<<FIELD>>} AS _raw
| spath
    | rename title AS title<<FIELD>>, value AS value<<FIELD>>, short AS short<<FIELD>>
| rename temp AS _raw]

A more risky alternative can also work if you are sure that title and value in custom_fields are never empty.  That is to apply mvindex to full spath as somesoni2 suggested:

| spath
| rename custom_fields{}.title as title, custom_fields{}.value as value
| foreach 0 1 2
    [eval title_<<FIELD>> = mvindex(title, <<FIELD>>), value_<<FIELD>> = mvindex(value, <<FIELD>>)]
| fields title_* value_*

The simulated input gives

title_0	title_1	title_2	value_0	value_1	value_2	_raw	_time
Resource ID	Mode	Performed By	ram@gmail.com	Remediated	john@gmail.com	{ "time": "2022-03-18 15:35:19,997", "field1": "dummy", "custom_fields":[ {"title":"Resource ID", "value":"ram@gmail.com", "short":true}, {"title":"Mode", "value":"Remediated"}, {"title":"Performed By", "value":"john@gmail.com", "short":true} ]} }	2022-03-19 01:29:49

This method will not work on the "short" field because it doesn't appear in all elements.  Again, if you are sure that "Mode" always appear in the second element, you can direct get its value via index 1, like

| spath
| foreach title value
    [rename custom_fields{}.<<FIELD>> as <<FIELD>>
    | eval <<FIELD>>_1 = mvindex(<<FIELD>>, 1)]
| fields *_1

[sddunne]

Brilliant, thanks so much everone for your responses! I went with;

| spath path=custom_fields{1}
| rename _raw AS temp
| rename custom_fields{1} AS _raw
| spath
| rename temp AS _raw | table alertrule, value

in the end which gave me exactly what i needed. 

Thanks again!!

[PickleRick]

"Full" json extraction will give you two separate multivalue fields.

custom_fields{}.title and custom_fields{}.value

That's not very helpful. If you are absolutely sure that there are no "gaps" in those values, yoh could try searching for an index in one field and search in the other one under the same index. But in general, those fields are not connected with each other after being parsed from the original event so it might work but it might not (if for some reason you wouldn't have title or value in some substructures - the fields wouldn't be matched).

If you are sure that value is always immediately after title, you can treat the event as a simple text event and search for the match using regex. Might watch for multiline issues.

You can also try to use spath to get just custom_fields as a multivalued field, then do mvexpand to split the "subjsons" into single events and parse them there using spath or kv.