How to Join two queries

[shashankk]

Hello Team,

I need assistance with joining 2 SPL queries to get the desired output. Refer the below log snippet:

As per the log pattern there are distinct transaction id's with the ORA-00001 error message.

Requirement is to identify all such transactions with the error message.

Please suggest.

240614 04:35:50 Algorithm: Al10: <=== Recv'd TRN: 48493009394940303 (TQ_HOST -> TQ_HOST)
240614 04:35:52 Algorithm: TS8398 hs_handle_base_rqst_msg: Error Executing CompareRBSrules Procedure.
240614 04:35:52 Algorithm: TS8398 hs_handle_base_rqst_msg: Details of ABC error ReSubResult:-1,FinalStatus:H,ErrorCode:-1,chLogMsg:SQL CODE IS  -1  AND  SQLERRM  IS ORA-00001: unique constraint (INSTANCE.IDX_TS_UAT_ABC_ROW_ID) violated,LogDiscription:

I have below 2 queries with their respective output:

Query 1:

index=test_index source=/test/instance ("<=== Recv'd TRN:")
| rex field=_raw "\<=== Recv'd TRN:\s+(?<transaction_id>\w+)"
| table _time transaction_id

Output as:
_time | transaction_id

Query 2:

index=test_index source=/test/instance ("ORA-00001") 
| table _time _raw

Output as:
_time | _raw

I want to merge or join both the results and get the final output as below:

_time | transaction_id | _raw

In this case (example)

240614 04:35:50 | 48493009394940303 | ORA-00001: unique constraint (INSTANCE.IDX_TS_UAT_ABC_ROW_ID) violated

Please suggest what modifications to be done in the above query to get this desired result.

@ITWhisperer - Kindly help.

[yuanliu]

I don't think you are looking to join two searches because the two searches operate on the same data source and in the same time interval.  What you want is to connect a transaction request and an ORA-00001 error if it happens.  ORA-00001, if it happens, should be directly following that transaction request. (Otherwise your problem is unsolvable.)  The _raw field in your last output actually represents the error message you want to display, not so much raw events.

In other words, from the data you illustrated, you want something like

_time	transaction_id	error_log
2024-06-14 04:35:50	48493009394940303	240614 04:35:52 Algorithm: TS8398 hs_handle_base_rqst_msg: Details of ABC error ReSubResult:-1,FinalStatus:H,ErrorCode:-1,chLogMsg:SQL CODE IS -1 AND SQLERRM IS ORA-00001: unique constraint (INSTANCE.IDX_TS_UAT_ABC_ROW_ID) violated,LogDiscription:

(Important: When you say "output as", you should illustrate actual output (anonymize as needed) of a search, not just field names.)

This should get what you wanted:

index=test_index source=/test/instance ("<=== Recv'd TRN:" OR "ORA-00001")
| rex field=_raw "\<=== Recv'd TRN:\s+(?<transaction_id>\w+)"
| transaction startswith="<=== Recv'd TRN:" endswith="ORA-00001" maxevents=2
| fields _* transaction_id
| eval error_log = split(_raw, "
")
| mvexpand error_log
| where match(error_log, "ORA-00001")
| table _time transaction_id error_log

For this type of problem, transaction is appropriate.

Here is an emulation for you to play with and compare with real data:

| makeresults
| eval data = mvappend("240614 04:35:50 Algorithm: Al10: <=== Recv'd TRN: 48493009394940303 (TQ_HOST -> TQ_HOST)",
"240614 04:35:52 Algorithm: TS8398 hs_handle_base_rqst_msg: Error Executing CompareRBSrules Procedure.",
"240614 04:35:52 Algorithm: TS8398 hs_handle_base_rqst_msg: Details of ABC error ReSubResult:-1,FinalStatus:H,ErrorCode:-1,chLogMsg:SQL CODE IS  -1  AND  SQLERRM  IS ORA-00001: unique constraint (INSTANCE.IDX_TS_UAT_ABC_ROW_ID) violated,LogDiscription:")
| mvexpand data
| rename data AS _raw
| rex "^(?<_time>\S+ \S+)"
| eval _time = strptime(_time, "%y%m%d %T")
| sort - _time
``` the above emulates
index=test_index source=/test/instance ("<=== Recv'd TRN:" OR "ORA-00001")
```

[P_vandereerden]

Besides the approximate time (since the times don't match), there is nothing else to relate those two particular logs together?

Will your search be used in the general case to output more than one row's worth of data? If so, how far apart are the various distinct transactions (or can that be arbitrarily short)?

[shashankk]

Hi @P_vandereerden 
Yes, as per the log pattern there are distinct transaction id's with the ORA-00001 error message.

Requirement is to identify all such transactions with the error message.

Please suggest.

[P_vandereerden]

Joining the two searches would require some common field to join on. Since none exists in your example, you'll need to either add an identifier to all related logs at the source, or get creative with a solution based on time that could get finicky.

For example, in your sample data, you have 3 events. The transaction ID in event 1 occurs 2 seconds before the error log. If there can be more than one concurrent transaction, then there doesn't appear to be a way to be certain that the correct transaction ID will be found that corresponds to error.
e.g.

240614 04:35:50 Algorithm: Al10: <=== Recv'd TRN: AAA (TQ_HOST -> TQ_HOST)
240614 04:35:51 Algorithm: Al10: <=== Recv'd TRN: BBB (TQ_HOST -> TQ_HOST)
240614 04:35:52 Algorithm: TSXXX hs_handle_base_rqst_msg: Error Executing CompareRBSrules Procedure.
240614 04:35:52 Algorithm: TSXXX hs_handle_base_rqst_msg: Details of ABC error ReSubResult:-1,FinalStatus:H,ErrorCode:-1,chLogMsg:SQL CODE IS  -1  AND  SQLERRM  IS ORA-00001: unique constraint (INSTANCE.IDX_TS_UAT_ABC_ROW_ID) violated,LogDiscription:

In this case, does the error belong to transaction AAA or BBB?

The second issue will be how much time can elapse between the "Rec'd TRN" log, and any possible error. Without a field linking these logs, you'll have to use some fixed time range to try to bring logs together. Too short, and you'll fail to find the transaction ID, too long and you might find multiple IDs (leading to the issue mentioned above).

IF you can assume that logs are synchronous, and there is no interleaving of transactions, then something like this should work:
 

index=test_index source=/test/instance | sort _time | rex field=_raw "<=== Recv'd TRN:\s+(?<transaction_id>\w+)" | eval failure=if(like(_raw, "%ORA-00001%"), 1, 0) | filldown transaction_id | where failure=1 | table transaction_id, failure, _raw