Splunk records join on transaction-id

Question

Two type of log records exist in our Splunk:

1st record format:

Transfer money { "accountNo" : "123" , "transaction-id":"1234567890" }

2nd record format:

[1234567890] Transfer failed, mainframe is offline

the first record is just the details of the transaction where 1234567890 is the transaction-id. The second record is just a log of the details of the exception that happened in the transaction. The value inside [] is the transaction-id of the transaction that had the exception.

In my splunk query, I want to list all transfer money transactions initiated by account = 123 that resulted in failure with details "Transfer failed, mainframe is offline"

Is this even possible to do? I did some bit of research that found joins can be used for this but how can you extract the transaction-id and use it as common field value for the join?

Answer 1

Your developers did you no service by creating log messages with such different formats. It's still possible to do it, however.

Yes, a join can be used, but should be avoided.

I'll explain using example code. Start by reading the events of interest: "Transfer money" and "Transfer failed, mainframe is offline".

index=foo ("Transfer money" OR "Transfer failed, mainframe is offline")
``` Extract the account number field ```
| rex "accountNo\\\" : \\\"(?<accountNo>[^\"]+)"
``` Extract the transaction ID from "Transfer money" events ```
| rex "transaction-id\\\":\\\"(?<transID>[^\"]+)"
``` Extract the transaction ID from "Transfer failed..." events ```
| rex "\[(?<transID>[^\]]+)"
``` Extract the error message from "Transfer failed..." events ```
| rex "] (?<msg>.*)"
``` Group the results by transaction ID ```
| stats values(*) as * by transID
``` Keep only the events with the requested error message ```
| where msg="Transfer failed, mainframe is offline"

Answer 2

Try this run anywhere example:

| makeresults count=2
    ```generation of example data```
| streamstats count
| eval
    event=if(count==1,"Transfer money { \"accountNo\" : \"123\" , \"transaction-id\":\"1234567890\" }", "[1234567890] Transfer failed, mainframe is offline")
    ```start of query, extract information from sourcetypes```
| rex field=event "^Transfer money\s(?<tm_json>\{.+)" 
| rex field=event "\[(?<transaction_id>.+)\]\s(?<fail_details>.+)" 
| spath input=tm_json 
| rename "transaction-id" AS "tid" 
| eval tid=coalesce(transaction_id, tid) 
    ```filter for desired events```
| where accountNo="123" OR fail_details="Transfer failed, mainframe is offline"
    ```merge events by transaction id``` 
| stats values(*) AS * BY tid