topic Re: Extract nested json in Getting Data In

Extract nested json

ch1221 — Mon, 11 May 2020 20:52:08 GMT

Looking for some assistance extracting all of the nested json values like the "results", "tags" and "iocs" in the screenshot. I've been trying to get spath and mvexpand to work for days but apparently I am not doing something right. Any help is appreciated.

Re: Extract nested json

to4kawa — Mon, 11 May 2020 22:03:02 GMT

your JSON can't be extracted using spath and mvexpand

This Only can be extracted from _raw, not Show syntax highlighted

Re: Extract nested json

Sfry1981 — Tue, 12 May 2020 08:16:11 GMT

if you run the search and then table it does it show as being parsed into its own columns as that might give you what you need.

Otherwise you may need to regex the data first if spath or mvexpand could not be used.

Also can you provide the output what is happening after you try with your search so we can try and work out a solution?

Re: Extract nested json

to4kawa — Tue, 12 May 2020 08:42:54 GMT

click Show as raw text in events

Re: Extract nested json

ch1221 — Wed, 30 Sep 2020 05:24:00 GMT

{ "start": 0, "terms": [ "feed_id:14" ], "highlights": [], "total_results": 103, "filtered": {}, "facets": {}, "results": [ { "ipv4_count": 0, "description": "description of event", "tags": [ "threathunting", "hunting", "t1033", "discovery", "recon", "windows" ], "feed_id": 14, "timestamp": 1552664393, "feed_category": "xxx", "sha256_count": 0, "create_time": 1552664393, "link": "hxxps://xxx.xxx", "id": "565616", "query_count": 1, "is_deleted": false, "title": "test", "has_query": true, "iocs": { "query": [ { "index_type": "events", "search_query": "test query" } ] }, "is_ignored": false, "feed_name": "test feed", "md5_count": 0, "score": 65, "ipv6_count": 0, "domain_count": 0 }, ], "elapsed": 0.013309955596923828}

Re: Extract nested json

to4kawa — Tue, 12 May 2020 12:03:20 GMT

your search
| rex "results\":\s(?<results>\[.*\])"
| spath
| fields - _* results{}.* 
| stats values(*) as * by results
| spath input=results {} output=results
| stats values(*) as * by results
| spath input=results
| fields - results
| eval tags=mvjoin('tags{}',",")
| fields - tags{} source sourcetype splunk_server punct date_* host

Thanks for your sample. how about this?

Re: Extract nested json

ch1221 — Tue, 12 May 2020 12:08:53 GMT

That doesn't work. It returns 6 events (based on the tags it appears) with all of the other values identical.

Re: Extract nested json

ch1221 — Tue, 12 May 2020 12:09:34 GMT

The raw results that I provided was a sample of 103+ results which is too much to post.

Re: Extract nested json

to4kawa — Tue, 12 May 2020 12:13:44 GMT

Put it up on another site temporarily and let me know the link.

Re: Extract nested json

ch1221 — Tue, 12 May 2020 12:43:02 GMT

https://www.dropbox.com/s/hjs4ez36y8g622q/cbfeeds.txt?dl=0

Re: Extract nested json

to4kawa — Tue, 12 May 2020 13:06:45 GMT

I see, my answer is updated.

Re: Extract nested json

ch1221 — Tue, 12 May 2020 13:10:17 GMT

Fantastic! Thank you so much!!!!!