How do I join 2 or more search results together on fields with different names?

[chrismeyer75]

For example, on one result I have a field 'Transactionid' equal to '12345' and on another result I have a field 'trans' = '12345'. I want to join these values together in one result and do some additional formatting.

[chrismeyer75]

Here is where I'm at with this. I'm able to run the following searches and get the results I need with the exception that I don't get the results from the join.

index=syslog | rex "(?i) trans\\((?P[^\\)]+)" | join TransactionID [search index=dev_mq_esb | rex "(?i)Transactionid: (?P.+)" | rex "(?i)X-Archived-Client-Ip: (?P.+)"] | transaction TransactionID | xmlkv | table host source sourcetype TransactionID ClientIP LatencyCalc

index=dev_mq_esb | rex "(?i)Transactionid: (?P.+)" | rex "(?i)X-Archived-Client-Ip: (?P.+)" | rex "(?i)Latency: (?P.+)" | join type=inner TransactionID [search index=syslog | rex "(?i) trans\\((?P[^\\)]+)"] | transaction TransactionID | xmlkv | table host source sourcetype TransactionID ClientIP LatencyCalc

I'm looking for how I can get the results of the join to be accessible for the results table I'm trying to build or if there is a different way I can build this and still get the same results.

Thanks,

[fdi01]

try this:

source="jms://queue/MPL1DEVWMB001:SplunkQueue" or source="upd:514" | rex "(?i)Transactionid: (?P<TransactionID>.+)" | rex "(?i) trans((?P<TransactionID>[^)]+)" | transaction TransactionID

[chrismeyer75]

Thanks. I actually ended up using this:

index=dev_mq_esb | rex "(?i)Transactionid: (?P.+)"| join TransactionID [search index=syslog | rex "(?i) trans\((?P[^\)]+)"] | transaction TransactionID

However, I don't see the results of the data I've joined to (i.e. results from index=syslog). Other than that, I am getting what I was expecting to see. Any thought on how I would get the results from the join transactions to show as well?

Thanks

[chrismeyer75]

Hi everyone, I'm listing transactions as an example of the events I'm looking to join. I need to be able to join these based on the 'Transactionid' and 'trans' fields. Another challenge is extracting these values as that currently isn't happening dynamically when I query based on the transaction. Currently, I just queried on the actual Transaction ID value but ultimately won't have that available. I want to eventually be able to report on this by pulling trip times from the latency transaction that is listed first but I also need information on the other events so we can report more details on what the transaction was for. I hope this helps provide more info. I've bolded the Transactionid and trans fields below. Thanks!

Apr 24 11:16:56 192.168.82.199 Apr 24 11:16:55 datapower-dev-dev [0x80e00073][latency][info] mpgw(dispatcher-mpg): trans(4607697)[192.168.82.113]: Latency: 0 29 0 28 29 20 0 22988 22988 22988 22988 22988 0 22988 28 29 [https://192.168.82.199:443/star/services/VehicleServiceHistory/Get]
host = 192.168.82.199 source = udp:514 sourcetype = syslog

Fri Apr 24 11:16:55 CDT 2015 name="QUEUE_msg_received" event_id="ID:414d5120514d5f4d504c31444556574d7a183955200e211e" msg_dest="SplunkQueue" msg_header_timestamp="1429892215560" msg_header_correlation_id="ID:414d5120514d5f4d504c31444556574d90883355203552bd" msg_header_delivery_mode="1" msg_header_expiration="0" msg_header_priority="0" msg_header_redelivered="false" msg_header_type="null" msg_property_JMS_IBM_Format=" " msg_property_JMS_IBM_Character_Set="UTF-8" msg_property_JMSXDeliveryCount="1" msg_property_JMS_IBM_Encoding="546" msg_property_JMSXUserID="SYSTEM " msg_property_JMS_IBM_MsgType="8" msg_property_JMS_IBM_PutApplType="26" msg_property_JMS_IBM_PutDate="20150424" msg_property_JMS_IBM_PutTime="16165556" msg_property_JMSXAppID="QM_MPL1DEVWMB001 " msg_body="

[chrismeyer75]

This is the search query I'm currently trying to use to get this information:

source="jms://queue/MPL1DEVWMB001:SplunkQueue" or source="upd:514" | rex "(?i)Transactionid: (?P.+)" | rex "(?i) trans\((?P[^\)]+)" | transaction TransactionID

[chimell]

Hi chrismeyer75

You can use these search code

|set  union[search sourcetype=.......    Transactionid= '12345' ] [search sourcetype=......     trans= '12345']|table_raw

|multisearch [search sourcetype=.......    Transactionid= '12345' ] [search sourcetype=......     trans= '12345']|table_raw

Run these example

|set union[search  sourcetype=access_* method=GET ][search sourcetype=access_* method=POST]|table_raw


|multisearch [search search sourcetype=access_* method=GET][search sourcetype=access_*  method=POST]|table _raw 

[Gilberto_Castil]

You are looking for coalesce. This command will allow you to concatanate different fields definitions onto a single named field. For instance:

  (sourcetype=a OR sourcetype=b) [ ... ] | eval Transactionid=coalesce(Transactionid,trans)

http://blogs.splunk.com/2014/03/21/search-command-coalesce/

---

As an alternative, you can just rename the field in sourcetype B as Transactionid with a fieldalias. You then use a search like so:

  (sourcetype=a OR sourcetype=b) [ ... ] | transaction Transactionid

or

  (sourcetype=a OR sourcetype=b) [ ... ] | stats count by Transactionid

--gc

[the_wolverine]

You could rename one field to match the other, then use transaction:

Transactionid=* OR trans=* | rename Transactionid as trans | transaction trans

[Gilberto_Castil]

Not sure about rename but it is worth trying. Playing with these options should take less than ten precious minutes.

[chrismeyer75]

I am trying to join the 2 search results but not the fields. It looks like another thing I need to do is extract the fields I am needing to work with.

[MuS]

You should provide some sample events and the result you expect to get, this will help a lot 😉