Splunk Search

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

New Member

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.

Tags (2)
0 Karma

New Member

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,

0 Karma

Motivator

try this:

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

New Member

Thanks. I actually ended up using this:

index=devmqesb | 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

0 Karma

New Member

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="QUEUEmsgreceived" eventid="ID:414d5120514d5f4d504c31444556574d7a183955200e211e" msgdest="SplunkQueue" msgheadertimestamp="1429892215560" msgheadercorrelationid="ID:414d5120514d5f4d504c31444556574d90883355203552bd" msgheaderdeliverymode="1" msgheaderexpiration="0" msgheaderpriority="0" msgheaderredelivered="false" msgheadertype="null" msgpropertyJMSIBMFormat=" " msgpropertyJMSIBMCharacterSet="UTF-8" msgpropertyJMSXDeliveryCount="1" msgpropertyJMSIBMEncoding="546" msgpropertyJMSXUserID="SYSTEM " msgpropertyJMSIBMMsgType="8" msgpropertyJMSIBMPutApplType="26" msgpropertyJMSIBMPutDate="20150424" msgpropertyJMSIBMPutTime="16165556" msgpropertyJMSXAppID="QMMPL1DEVWMB001 " msg_body="

0 Karma

New Member

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

0 Karma

Motivator

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 
0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Champion

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

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

Splunk Employee
Splunk Employee

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

0 Karma

New Member

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.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma