topic Re: Transactions with different field names in Splunk Search

Transactions with different field names

cramasta — Wed, 31 Aug 2011 20:49:15 GMT

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

Re: Transactions with different field names

David — Wed, 31 Aug 2011 20:59:54 GMT

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.

Re: Transactions with different field names

cramasta — Thu, 01 Sep 2011 04:44:56 GMT

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.

Re: Transactions with different field names

cramasta — Thu, 01 Sep 2011 04:51:02 GMT

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

Re: Transactions with different field names

gkanapathy — Thu, 01 Sep 2011 06:28:33 GMT

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

Re: Transactions with different field names

gkanapathy — Thu, 01 Sep 2011 06:28:54 GMT

I recommend using the coalesce() eval function.