topic Re: How to filter results based on property inside JSON string? in Splunk Search

How to filter results based on property inside JSON string?

ghostrider — Tue, 27 Sep 2022 14:22:59 GMT

I want to filter the search results based on tx_id that I extract in the 2nd rex. Meaning only those results that have the transaction_id same as the tx_id. I tried the where clause but it doesn't work

{search_results} | rex field= MESSAGE "(?<JSON>\{.*\})" | rex field= MESSAGE "Published Event for txn_id (?<tx_id>\w+)"

I tried this :

{search_results} | rex field= MESSAGE "(?<JSON>\{.*\})" | rex field= MESSAGE "Published Event for txn_id (?<tx_id>\w+)" | where JSON.transaction_id in (tx_id)

Re: How to filter results based on property inside JSON string?

johnhuang — Tue, 27 Sep 2022 14:51:39 GMT

Periods are considered a special characters in the fieldname. Consider renaming it or encapsulate it in single quotes.

| rename JSON.transaction_id AS transaction_id | where transaction_id IN (tx_id)

| where 'JSON.transaction_id' IN (tx_id)

Re: How to filter results based on property inside JSON string?

yuanliu — Tue, 27 Sep 2022 17:11:35 GMT

@ghostrider A reminder that "it doesn't work" conveys very little information and should be avoided.

You also forgot to say what is the content of the JSON and how the content relate to your requirement. I speculate that this extracted field would contain a conformant JSON object that happens to have a key transaction_id. If this is the case, you need to first extract the key from JSON first, with spath. In addition, why do you choose to use IN instead of ==? If you expect tx_id to have multiple values in that string, you need to use maxmatch=0 in rex. Third, you must not leave space between field= and MESSAGE.

In the following example, I'll assume that there is only one tx_id in each MESSAGE.

{search_results} | rex field=MESSAGE "(?<JSON>\{.*\})" | rex field=MESSAGE "Published Event for txn_id (?<tx_id>\w+)" | spath input=JSON | where transaction_id == tx_id

For example, if your data is

MESSAGE

Published Event for txn_id abc123 blah {"transaction_id":"abc123","foo":"bar"}

Published Event for txn_id cde456 blah {"transaction_id":"fgh789","foo":"bar"}

The above should give you

JSON	MESSAGE	foo	transaction_id	tx_id
{"transaction_id":"abc123","foo":"bar"}	Published Event for txn_id abc123 blah {"transaction_id":"abc123","foo":"bar"}	bar	abc123	abc123

In other words, the second message is excluded.