Multiple json in an event- How do I extract status in one event?

splunkuser320 — Thu, 13 Jul 2023 20:35:27 GMT

I have multiple json coming in a single event and want to extract the status of one event.

For example, I want the status of the event extract

{"event": "load", "id ":132", "status": "passed"}

{"event": "write", "id ":132", "status": "passed"}

{"event": "extract", "id ":132", "status": "passed"}

Re: Multple json in an event

ITWhisperer — Thu, 13 Jul 2023 18:09:06 GMT

| rex "event\"\s*:\s*\"extract\".+?status\"\s*:\s*\"(?<status>[^\"]+)"

Re: Multple json in an event

yuanliu — Thu, 13 Jul 2023 19:11:53 GMT

Seriously, beg/pester your developer to write events in proper JSON. (BTW, your illustration also contained extraneous quote after reach number.) A lazy approach could be JSON array, like

{"event": [ {"type": "load", "id ":132, "status": "passed"}, {"type": "write", "id ":132, "status": "passed"}, {"type": "extract", "id ":132, "status": "passed"} ] }

This will give you something like

data	event{}.id	event{}.status	event{}.type
{"event": [ {"type": "load", "id ":132, "status": "passed"}, {"type": "write", "id ":132, "status": "passed"}, {"type": "extract", "id ":132, "status": "passed"} ] }	132 132 132	passed passed passed	load write extract

The multivalue fields are harder to process in Splunk, so you want to use spath and mvexpand to handle raw event

| spath path=event{} | mvexpand event{} | spath input=event{}

so you get single-value rows like

event{}	id	status	type
{"type": "load", "id ":132, "status": "passed"}	132	passed	load
{"type": "write", "id ":132, "status": "passed"}	132	passed	write
{"type": "extract", "id ":132, "status": "passed"}	132	passed	extract

But really, because the types do not overlap, they should be in nested key-value form

{"event": { "load": {"id":132, "status": "passed"}, "write": {"id ":132, "status": "passed"}, "extract": {"id ":132, "status": "passed"} } }

This should give you

data	event.extract.id	event.extract.status	event.load.id	event.load.status	event.write.id	event.write.status
{"event": { "load": {"id":132, "status": "passed"}, "write": {"id ":132, "status": "passed"}, "extract": {"id ":132, "status": "passed"} } }	132	passed	132	passed	132	passed

Lastly, JSON does not dictate order of nodes, or how texts are spaced, linewrapped, etc. It is best not to avoid treating structured data as text. So, before your developer yields to your persuasion, you can convert the bad event into compliant JSON array. Not an ideal form but usable.

| rex mode=sed "s/^/[/ s/}/},/g s/,$/]/" | spath path={} | mvexpand {} | spath input={}

This way, you get

event	id	status	{}
load	132	passed	{"event": "load", "id ":132, "status": "passed"}
write	132	passed	{"event": "write", "id ":132, "status": "passed"}
extract	132	passed	{"event": "extract", "id ":132, "status": "passed"}

topic Multiple json in an event- How do I extract status in one event? in Splunk Search

Multiple json in an event- How do I extract status in one event?

Re: Multple json in an event

Re: Multple json in an event