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?
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)
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)
Looks like it is partially working, how can I remove the blank SSN and TPID fields?
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