Relating 2 different fields within a transaction

[ndoshi]

I would like to relate 2 different sourcetypes with a common value for a field. The fields are named differently in each sourcetype. For my example I have:

sourcetype=A OR sourcetype=B|transaction field_A,field_B maxspan=1d connected=f|eval num=mvcount(sourcetype)|where num=2

This means relate all events of sourcetype A to sourcetype B using the extracted field_A and field_B from each respective sourcetype and only show grouped events that have both sourcetypes. So, if field_A=2 and field_B=2 for the allotted time period, there should be a grouping. However, if field_A=3 and field_B=2, there should be no match. If I don't use connected=f, I get no matches at all for 2 different sourcetypes, even when they exist. If I use connected=f, it groups events even if field_A and field_B have different values. The end goal is to group events where field_A==field_B for the same values. What am I doing incorrectly. Here's another more concrete example:

sourcetype="trade_pricing" OR sourcetype="stockquote_entry" |transaction stock_name,stock_symbol connected=f maxspan=1d |eval num=mvcount(sourcetype)|where num=2

The results I get back from a search for a transaction grouping are:

StockQuotes Stock Symbol GE 11/1/2010 4:02pm Change-0.07
Mon Nov  1 17:20:04 EDT 2010 Looking up price for BAC
Mon Nov  1 17:20:04 EDT 2010 Got price for BAC as 0
Mon Nov  1 17:21:08 EDT 2010 Looking up price for BAC
Mon Nov  1 17:21:08 EDT 2010 Got price for BAC as 0.5



    * host=mac-mbp15.local 
    * sourcetype=stockquote_entry  
    * sourcetype=trade_pricing 
    * source=/Applications/splunk/etc/apps/Trade/logs/pricing.log
    * source=stockquote_xml
    * stock_name=BAC 
    * stock_symbol=GE 

Notice how stock_name and stock_symbol have different values, but the events are still grouped together. Any explanations?

[chris · Accepted solution]

If sourcetype A only contains field_A and sourcetype B only contains field_B, then this might work

`sourcetype=A OR sourcetype=B|eval mySingleField=coalesce(field_A,field_B)|transaction mySingleField maxspan=1d |eval num=mvcount(sourcetype)|where num=2`

You just create a new field for all the events that has the same name in both sourcetypes

I hope this helps

Chris

[maverick · Accepted solution]

If you are trying to group on field_A AND field_B that have the same value between them (even between sourcetypes), then you could try creating a field alias called "field_Z" to normalize the field name, first, THEN pipe to transaction using field_Z.

BTW, to setup a field alias, you can go into Splunk >> Manager >> Fields >> Field alias >> New

and add both field_A and field_B to have an alias of field_Z, save.

[chris · Accepted solution]

If sourcetype A only contains field_A and sourcetype B only contains field_B, then this might work

`sourcetype=A OR sourcetype=B|eval mySingleField=coalesce(field_A,field_B)|transaction mySingleField maxspan=1d |eval num=mvcount(sourcetype)|where num=2`

You just create a new field for all the events that has the same name in both sourcetypes

I hope this helps

Chris