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.
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,
try this:
source="jms://queue/MPL1DEVWMB001:SplunkQueue" or source="upd:514" | rex "(?i)Transactionid: (?P<TransactionID>.+)" | rex "(?i) trans((?P<TransactionID>[^)]+)" | transaction TransactionID
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
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="
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
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
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
You could rename one field to match the other, then use transaction:
Transactionid=* OR trans=* | rename Transactionid as trans | transaction trans
Not sure about rename
but it is worth trying. Playing with these options should take less than ten precious minutes.
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.
You should provide some sample events and the result you expect to get, this will help a lot 😉