topic Re: Pulling Data from Nested JSON Fields based on Field Value in Splunk Search

How to pull data from Nested JSON Fields based on Field Value?

cfloquet — Thu, 15 Sep 2022 14:41:42 GMT

Hello,

I'm working on creating automated alerts from an email security vendor and would like for them to only include the names of files/attachments which have the "attached" disposition within a nested JSON structure. The example below shows what I'm talking about in a limited/trimmed capacity:
messageParts: [
{
contentType: image/png
disposition: attached
filename: example.png
md5: xxyy
sha256: xxyy
}
{
contentType: text/html
disposition: inline
filename: text.html
md5: xxyy
sha256: xxyy
}
{
contentType: text/plain
disposition: inline
filename: text.txt
md5: xxyy
sha256: xxyy
}
]

Essentially I'd like to pull and store the respective "filename" and hash values for when the "disposition" field is "attached" but not "inline". I know this can likely be done using something like spath or mvfind, but I'm not entirely sure how to accomplish it and it's giving me fits.

Anyone who can lend a helping hand would be handsomely rewarded with karma and many well wishes, thanks for taking the time to consider my question!

Re: Pulling Data from Nested JSON Fields based on Field Value

bowesmana — Thu, 15 Sep 2022 00:11:27 GMT

Here's an example using your base data. I added a 4th file, also attached to show that it works with possible multiple 'attached' files.

| makeresults | eval _raw="{ \"messageParts\": [ { \"contentType\":\"image/png\", \"disposition\":\"attached\", \"filename\":\"example.png\", \"md5\":\"xxyy\", \"sha256\":\"xxyy\" }, { \"contentType\":\"text/html\", \"disposition\":\"inline\", \"filename\":\"text.html\", \"md5\":\"xxyy\", \"sha256\":\"xxyy\" }, { \"contentType\":\"text/plain\", \"disposition\":\"inline\", \"filename\":\"text.txt\", \"md5\":\"xxyy\", \"sha256\":\"xxyy\" }, { \"contentType\":\"text/plain\", \"disposition\":\"attached\", \"filename\":\"bla.txt\", \"md5\":\"nnnn\", \"sha256\":\"zzzz\" } ] } " | spath | rename messageParts{}.* as * | foreach 0 1 2 3 4 5 6 7 8 9 10 [ eval isAttach=if(mvindex(disposition, <<FIELD>>)="attached", 1, 0), file=if(isAttach=1, mvappend(file, mvindex(filename, <<FIELD>>)), file), hash_md5=if(isAttach=1, mvappend(hash_md5, mvindex(md5, <<FIELD>>)), hash_md5), hash_sha256=if(isAttach=1, mvappend(hash_sha256, mvindex(sha256, <<FIELD>>)), hash_sha256) ]

Up to the spath is setting up the data example. The foreach 0 1 2... is simply creating a loop for 0-10, so supporting 11 possible filenames - add as many as you want. The returned file, hash_md5 and hash_sha256 will contain the data you need

Re: Pulling Data from Nested JSON Fields based on Field Value

cfloquet — Tue, 20 Sep 2022 18:06:14 GMT

I think this works great, havent tested fully yet but it looks right to me.

Thanks!