Splunk Search

How to filter results based on property inside JSON string?

ghostrider
Path Finder

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_idI 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)

 

 

 

Labels (5)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

@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

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

In other words, the second message is excluded.

Tags (1)
0 Karma

johnhuang
Motivator

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)

 

 

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...