Splunk Search

coalesce values of Outsearch and Subsearch

prasant
Explorer

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

 

correlationIdcontactUUIDaccountUUIDQ_errorMessageQ_errorCodeD_errorCodeD_errorMessage
ab861125-6cd7-493b-999f-ef9b2edd8315023758601 C0DABCC1-EFC8-11eb-A67A-005056B89B42nullnull201 nullnull { "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? 


Labels (1)
0 Karma
1 Solution

prasant
Explorer

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 

 

 

View solution in original post

Tags (1)
0 Karma

prasant
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

prasant
Explorer

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 

 

 

View solution in original post

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!