Splunk Search

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

sddunne
Explorer

sddunne_0-1647620108954.png

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!

Labels (2)
Tags (1)
0 Karma

somesoni2
Revered Legend

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>[^\"]+)\""
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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_timetitlevalue
{ "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:27ModeRemediated

 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_0title_1title_2value_0value_1value_2_raw_time
Resource IDModePerformed Byram@gmail.comRemediatedjohn@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

 

 

Tags (2)
0 Karma

sddunne
Explorer

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

0 Karma

PickleRick
Ultra Champion

"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.

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...