Can you combine two transactions using subquery?

[mugilbala]

I have following log statements.

2018-06-15 14:29:04,866 GMT DEBUG (inbound-8080-205|E:APP_**12345**|R:Qka4dqva8p9TQLYw|U:bacon) {X:APP_12345|I:} Account Transaction started. 
2018-06-15 14:29:04,966 GMT DEBUG (inbound-8080-205|E:APP_ROLLBACK_**12345**|R:Qka4dqva8p9TQLYw|U:bacon) {X:APP_ROLLBACK_12345|I:} Rollback Transaction Started

First event has APP_12345 and second event has APP_ROLLBACK_12345 as transaction ids.

Tx Id value is same with different suffix. Is there a way to get combine these two transaction using sub query?

[somesoni2 · Accepted solution]

Give this a try

your search fetching those records
 | rex "^[^\|]+\|E\:(?<Type>APP_ROLLBACK|APP)_(?<transactionID>[^\|]+)"
| eventstats values(Type) as types by transactionID
| where mvcount(types)=2 AND Type="APP" | field - types

OR

your base search e.g. index=foo sourcetype=bar ..  "Account Transaction started"
 | rex "^[^\|]+\|E\:APP_(?<transactionID>[^\|]+)"
| where [search your base search e.g. index=foo sourcetype=bar .. "Rollback Transaction Started" 
| rex "^[^\|]+\|E\:APP_ROLLBACK_(?<transactionID>[^\|]+)" | stats count by transactionID| table transactionID]

Both will filter the results to keep "Account Transaction started" type events which have a matching "Rollback Transaction Started" type event.

[mugilbala]

Thank you @somesoni2.

This query worked as expected.

your base search e.g. index=foo sourcetype=bar .. "Account Transaction started"
| rex "^[^|]+|E:APP_(?[^|]+)"
| where [search your base search e.g. index=foo sourcetype=bar .. "Rollback Transaction Started"
| rex "^[^|]+|E:APP_ROLLBACK_(?[^|]+)" | stats count by transactionID| table transactionID]

Appreciate your help.

[somesoni2]

Glad to hear that.

If there are no follow-up question related to this, please close the question by accepting this as an answer. This will help other Splunkers to use this as reference.

[somesoni2]

If you've setup a field extraction, then you can just update it to ignore prefix and use just the transaction id as field. Once you've done that, both events will have same field value and can be combined.

This is how you'd do the same in inline field extraction in search

your search fetching those records
| rex "^[^\|]+\|(E\:APP_ROLLBACK_|E\:APP_)(?<transactionID>[^\|]+)"

[mugilbala]

Hi @somesoni2
Thank you for the response.
Unfortunately the transaction id was not setup in field extraction. Is there a way to find the events with "Account Transaction started" if the transaction id matched with rollback transaction.