Splunk Search

Transactions with different field names

cramasta
Builder

Hi,
Hoping this is something simple that I'm not understanding.

Example Data:

Sourcetype=A Sport1=baseball

SourceType=B Sport2=baseball

How can I perform a transaction where sport1 and sport2 have the same value. The way I have gotten around this is by doing the following with append but I was thinking there has to be a better way that might be built into the transaction command

sourcetype=a | eval Sport=Sport1 | append [ search sourcetype=b | eval Sport=Sport2 ] | transaction Sport

Thanks

Tags (1)
0 Karma
1 Solution

David
Splunk Employee
Splunk Employee

I have always used rename to align different fields. It's not built into transaction, but it should be very speedy. Rename is going to be able to tear through the renaming worlds faster than transaction will be able to deal with them.

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | rename Sport1 as Sport | rename Sport2 as Sport | transaction Sport

If you did want to keep the original field names, one minor comment: if both sourcetypes are in the same index, you will get better performance out of your search by adjusting it to:

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | eval Sport=if(len(Sport1)>0,Sport1,if(len(Sport2)>0,Sport2,"")) | transaction Sport

Avoiding append, join and subsearch is a great way to improve performance, in general.

Let me know if you have any questions.

View solution in original post

cramasta
Builder

Thanks David,

Your second answer was exactly what I was looking for

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | eval Sport=if(len(Sport1)>0,Sport1,if(len(Sport2)>0,Sport2,"")) | transaction Sport

I did originally try your first suggestion before posting my question but I did not get the results that I expected. I first ran

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | rename Sport1 as Sport | rename Sport2 as Sport | transaction Sport

What I found after experimenting was the transaction only used data that belonged to the sourcetype of the last rename command in the search. So by switching the order of the rename commands I will end up with a different total number of fields in the field picker. I would also see the single sourcetype value change between A and B.

I was able to somewhat fix this by adding "| table * " before the transaction command

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | table * | rename Sport1 as Sport | rename Sport2 as Sport | transaction Sport

What this did was now list the same number of fields in the field picker no matter the order, but the results would still vary depending on the order of the rename commands the transaction.

Would be happy to show over a webex.

gkanapathy
Splunk Employee
Splunk Employee

I recommend using the coalesce() eval function.

David
Splunk Employee
Splunk Employee

I have always used rename to align different fields. It's not built into transaction, but it should be very speedy. Rename is going to be able to tear through the renaming worlds faster than transaction will be able to deal with them.

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | rename Sport1 as Sport | rename Sport2 as Sport | transaction Sport

If you did want to keep the original field names, one minor comment: if both sourcetypes are in the same index, you will get better performance out of your search by adjusting it to:

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | eval Sport=if(len(Sport1)>0,Sport1,if(len(Sport2)>0,Sport2,"")) | transaction Sport

Avoiding append, join and subsearch is a great way to improve performance, in general.

Let me know if you have any questions.

gkanapathy
Splunk Employee
Splunk Employee

I recommend the use of the coalesce() eval function.

cramasta
Builder

this worked perfect

sourcetype=a OR sourcetype=b Sport1=* OR Sport2=* | eval Sport=if(len(Sport1)>0,Sport1,if(len(Sport2)>0,Sport2,"")) | transaction Sport

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...