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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...