Getting Data In

Extract nested json

ch1221
Path Finder

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.
alt text

0 Karma
1 Solution

to4kawa
Ultra Champion
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?

View solution in original post

0 Karma

to4kawa
Ultra Champion
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?

0 Karma

ch1221
Path Finder

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

0 Karma

ch1221
Path Finder

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

ch1221
Path Finder
0 Karma

to4kawa
Ultra Champion

I see, my answer is updated.

0 Karma

ch1221
Path Finder

Fantastic! Thank you so much!!!!!

0 Karma

Sfry1981
Communicator

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?

0 Karma

to4kawa
Ultra Champion

click Show as raw text in events

0 Karma

ch1221
Path Finder

{ "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}

0 Karma

to4kawa
Ultra Champion

your JSON can't be extracted using spath and mvexpand

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...