Splunk Search

Filter JSON search results based on another log message in the same search?

ghostrider
Path Finder

I have the below search results that will consist of 2 different types of log formats or strings. Log 1:  "MESSAGE "(?<JSON>\{.*\})" and Log 2 : "Published Event for txn_id (?<tx_id>\w+)". Both of these formats or logs or messages will be present in the result of the below search_results.
I want to filter only those logs with Log 1 format that has the same transactionid as the one in the other Log 2 format. I am trying to run the below query. However its giving zero results even though there are common transactionids between these 2 log formats. Is there any way to achieve this? 

 

{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

 

Labels (6)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

I think this is the same as https://community.splunk.com/t5/Splunk-Search/How-to-filter-results-based-on-property-inside-JSON-st... in which you didn't specify that the two rex in your original search would be from different events.

To equate two different events, name the transaction ID with the same name, then stats by that common field as @ITWhisperer suggested, then filter out events that do not match.  Since you are only interested in Log 1, i.e., the source with that JSON section, filter out the other one.

{search_results}
| rex field=MESSAGE "(?<JSON>\{.*\})"
| rex field=MESSAGE "Published Event for txn_id (?<transaction_id>\w+)"
| spath input=JSON
| stats values(MESSAGE) as MESSAGE by transaction_id
| where mvcount(MESSAGE) > 1
| eval MESSAGE = mvfilter(!match(MESSAGE, "Published Event for txn_id"))

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Splunk processes a pipeline of events - the where command (for example) is only operating on one event at a time. In order to find the events that share the same transaction_id you need to "gather" the events using the stats command, something like this

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

 

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...