Splunk Search

Join two searches together and create a table

dpanych
Communicator

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?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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)

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Communicator

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

0 Karma

stephanefotso
Motivator

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

SGF
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...