coalesce values of Outsearch and Subsearch

[prasant]

Hi Splunk experts,

I have below usecase and using below query

index=Index1 app_name IN ("customer","contact")

| rex field=msg.message.details "\"accountUuid\":\"(?<SFaccountUUID>[^\n\r\"]+)"

| rex field=msg.message.details "\"contactId\":\"(?<SFcontactUUID>[^\n\r\"]+)"

|rex field=msg.details "\'customerCode\'\=\'(?<cac>[^\n\r\']{10})\'"

| rename msg.correlationId AS correlationId

| stats latest(SFcontactUUID) as contactUUID,latest(SFaccountUUID) as accountUUID,values(msg.tag.Status) as QStatus,values(msg.tag.errorMessage) as Q_errorMessage,values(msg.tag.errorCode) as Q_errorCode by correlationId

| join type=left correlationId [search index=index2 app_name="contact1"

|rename  msg.message.header.correlationId AS correlationId

|stats values(msg.message.header.Status) AS DStatus,values(msg.message.header.eventName) AS eventName,values(msg.message.header.errorMessage) as D_errorMessage,values(msg.message.header.errorCode) as D_errorCode by correlationId]

The common identifier between the 2 searches is the correlationId. Below is sample result

correlationId	contactUUID	accountUUID	Q_errorMessage	Q_errorCode	D_errorCode	D_errorMessage
ab861125-6cd7-493b-999f-ef9b2edd8315023758601		C0DABCC1-EFC8-11eb-A67A-005056B89B42	null	null	201 null	null { "ContactUUID": "b020c98a-43f5-d6b3-e983-45ffddf52a73"}

Is it possible to coalesce the value of highlighted in red from subsearch into the ContactUUID field in the outersearch?I am expecting this value either in outer or subsearch and so how can I solve it? 

[prasant]

@ITWhisperer  Thanks it worked, I was thinking of using rex and combine the indexes in one search.

[ITWhisperer]

You could try something like this

index=Index1 app_name IN ("customer","contact")
| rex field=msg.message.details "\"accountUuid\":\"(?<SFaccountUUID>[^\n\r\"]+)"
| rex field=msg.message.details "\"contactId\":\"(?<SFcontactUUID>[^\n\r\"]+)"
|rex field=msg.details "\'customerCode\'\=\'(?<cac>[^\n\r\']{10})\'"
| rename msg.correlationId AS correlationId
| stats latest(SFcontactUUID) as contactUUID,latest(SFaccountUUID) as accountUUID,values(msg.tag.Status) as QStatus,values(msg.tag.errorMessage) as Q_errorMessage,values(msg.tag.errorCode) as Q_errorCode by correlationId
| append [search index=index2 app_name="contact1"
| rex field=msg.message.header.errorCode "\"ContactUUID\":\"(?<SFcontactUUID>[^\n\r\"]+)"
| rename  msg.message.header.correlationId AS correlationId
|stats values(msg.message.header.Status) AS DStatus,values(msg.message.header.eventName) AS eventName,values(msg.message.header.errorMessage) as D_errorMessage,values(msg.message.header.errorCode) as D_errorCode values(SFContactUUID) and SFContactUUID by correlationId]
| stats values(*) as * by correlationId

[prasant · Accepted solution]

Hi, @ITWhisperer 

thanks for u r response, but your solution doesnt seem to work, I am using join( real time) so I can get the values of the subsearch as column, against the join condition. i.e common identifier is correlation ID.

Outer Search A,  Contact Column x

Subsearch B, Contact Column y 

Join condition correlationId

final stats/table should have combined result of column x and y along with all the other columns from Search A and Search B. The reason I want to combine the values is that, sometime Column x or Column Y will have the value.

thanks 

[ITWhisperer]

Sorry typo (field names are case sensitive)

values(SFcontactUUID) and SFcontactUUID

 The final stats does "join" by correlation id; where field names are the same, the values from both searches are joined into multi-value fields, so SFcontactUUID will have values from both searches, so in your case, if it is only present in one search or the other, they are effectively coalesced into a single field.