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?
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 Thanks it worked, I was thinking of using rex and combine the indexes in one search.
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
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
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.