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)
@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.
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)