Join two searches together and create a table

[dpanych]

I have two searches that I want to combine into one:

index=calfile CALFileRequest.TPID=* CALFileRequest.SSN=* CALFileRequest.TransactionIdentifier=*
| rename CALFileRequest.TPID AS TPID, CALFileRequest.SSN AS SSN, CALFileRequest.TransactionIdentifier AS TransID 
| where TPID!=SSN
| table SSN TPID TransID

index=calfile \<CALFileResponse\> CALFileResponse.TransactionIdentifier=* _raw="*\<Street\>*"
| rename CALFileResponse.TransactionIdentifier AS TransID, "CALFileResponse.CALFileData.Street" AS Street
| table TransID Street

The first one returns:
SSN TPID TransID
---------------------------------------------------
123121234 123121234 1

The second one returns:
TransID Street
------------------------------
1 10 Box Ct.

How can I make a search that returns SSN, TPID, TransID, and Street?

[somesoni2 · Accepted solution]

This probably be more efficient then join.

index=calfile (CALFileRequest.TPID=* CALFileRequest.SSN=* CALFileRequest.TransactionIdentifier=*) OR ( \<CALFileResponse\> CALFileResponse.TransactionIdentifier=* _raw="*\<Street\>*")
 | where isnull('CALFileRequest.TPID') OR 'CALFileRequest.TPID'!='CALFileRequest.SSN'
 | eval TransID=coalesce('CALFileRequest.TransactionIdentifier','CALFileResponse.TransactionIdentifier')
 | rename CALFileRequest.TPID AS TPID, CALFileRequest.SSN AS SSN  "CALFileResponse.CALFileData.Street" AS Street
 | stats values(Street) as Street, values(SSN) as SSN values(TPID) as TPID by TransID

Update
There could be some extra TransID in 2nd search, try something like this

index=calfile (CALFileRequest.TPID=* CALFileRequest.SSN=* CALFileRequest.TransactionIdentifier=*) OR ( \<CALFileResponse\> CALFileResponse.TransactionIdentifier=* _raw="*\<Street\>*")
 | where isnull('CALFileRequest.TPID') OR 'CALFileRequest.TPID'!='CALFileRequest.SSN'
 | eval TransID=coalesce('CALFileRequest.TransactionIdentifier','CALFileResponse.TransactionIdentifier')
 | rename CALFileRequest.TPID AS TPID, CALFileRequest.SSN AS SSN  "CALFileResponse.CALFileData.Street" AS Street
 | stats values(Street) as Street, values(SSN) as SSN values(TPID) as TPID by TransID | where isnotnull(SSN) OR isnotnull(TPID)

[dpanych]

Looks like it is partially working, how can I remove the blank SSN and TPID fields?

[stephanefotso]

Try this with appedncols http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Appendcols

   index=calfile CALFileRequest.TPID=* CALFileRequest.SSN=* CALFileRequest.TransactionIdentifier=*
     | rename CALFileRequest.TPID AS TPID, CALFileRequest.SSN AS SSN, CALFileRequest.TransactionIdentifier AS TransID 
     | where TPID!=SSN
     | table SSN TPID TransID  | appendcols [search index=calfile \<CALFileResponse\> CALFileResponse.TransactionIdentifier=* _raw="*\<Street\>*"
 | rename CALFileResponse.TransactionIdentifier AS TransID, "CALFileResponse.CALFileData.Street" AS Street
 | table Street]

thanks