regex help

[dbautist]

I have the following log snippet with a JSON payload and I want to run a regex such that it extracts the JSON fields so I can display them in a table.

input:

id=1234, payload={"shippingAddress": [{"name": "bob","address": "123 a street, san francisco, ca"},{"name": "joe","address": "14 b ave, new york, ny"}]}

output:

name address
bob 123 a street, san francisco, ca
joe 14 b ave, new york, ny

[Rob]

Although I agree with @martin_mueller, you could use rex to extract the fields, but it will be cumbersome, here is an example based on your snippet (please remove the ## marks that have been inserted due to the Splunk answers formatting):

|rex field=payload max_match=0 "\"name\":\s\"(?<##name>[^"]+)\",\"address\":\s\"(?<##address>[^"]+)\"" | table name address

Bear in mind that this rex statement will take multiple values in the payload string and create multi value fields which you might need to use with eval statements such as mvexpand depending on the table you are looking to generate.

[martin_mueller · Accepted solution]

You're better off using

| spath input=payload

than regex'ing your way through JSON.

[martin_mueller]

On event level you can do this:

... | spath input=payload | rename shippingAddress{}.name AS name shippingAddress{}.address AS address | where name=joe

That'll only give you events where one value of name is joe.
Within one even you can filter the two multi-values fields like this:

...  | spath input=payload | rename shippingAddress{}.name AS name shippingAddress{}.address AS address | eval mv_index = mvfind(name, "joe") | eval name = mvindex(name, mv_index) | eval address = mvindex(address, mv_index)

That'll remove value from the multi-valued field not belonging to joe.

[dbautist]

Thanks! This works like a charm. As a follow up, how can I pull specific fields from the result. For example, if I only want it to return name='joe'. I tried eval, but it still returns all of it.