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!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...