topic coalesce values of Outsearch and Subsearch in Splunk Search

coalesce values of Outsearch and Subsearch

prasant — Sat, 07 Aug 2021 03:35:29 GMT

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?

Re: coalesce values of Outsearch and Subsearch

ITWhisperer — Sat, 07 Aug 2021 07:03:38 GMT

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

Re: coalesce values of Outsearch and Subsearch

prasant — Sun, 08 Aug 2021 03:17:51 GMT

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

Re: coalesce values of Outsearch and Subsearch

ITWhisperer — Sun, 08 Aug 2021 07:56:26 GMT

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.

Re: coalesce values of Outsearch and Subsearch

prasant — Mon, 09 Aug 2021 00:01:05 GMT

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