How to do a lookup between two searches and combine the data in a single line in a table

[dmcgeearke]

Can someone give me the basics to do something like

find THIS in search number 1, match it to THAT in search number 2, then Create a table with THESE fields from search number 1 and THOSE fields from Search number 2 (in line on the matched fields)

Basically the Splunk equivalent of a VLookup, but I don't have a inputlookup file, its a live search.

I've done combined searches and created alerts to basically say if "this is greater than that" then alert, or "if this is NOT present in that or whatever.. and that seems to work.. but I've only ever created the table of the results after the count, not just returning them in one line.

In the attached example, I am trying to return info from a search that contains error messages and plain language explanations along with root cause and proposed fixes. I will have current searches comparing the error message to the "SitecoreError_short` field in our solutions database.

I narrowed the 'message' and the SitecoreError_Short Fields down to one specific error, but what i would want is it to search for message in SitecoreError_Short and then put the other fields like Plain_Error_Description, Root_Cause, Proposed_NextSteps in line with the error.

Ideas on how to accomplish this, or if i'm going about it completely wrong?

index="main" sourcetype="sitecore:*" message="*Object reference not set to an instance of an object.*"
| stats count by log_level,message,exception 
| append 
    [search index=main source="C:\\SplunkLogs\\Splunk_ArkeSitecore_ErrorLogRCA.csv" SitecoreError_Short="*Object reference not set to an instance of an object.*"
    | dedup SitecoreError_Short 
    | fields SitecoreError_Short, Plain_Error_Description, Root_Cause, Proposed_NextSteps]

Also, If a solution can be made to allow both (in this example) of the Plain_Error_Description fields to be visible, that would be awesome. (i'm thinking a multivalue of some sort?)

[somesoni2]

Give this a try (assuming values in field message and SitecoreError_Short have exact match)

index="main" (sourcetype="sitecore:*" OR source="C:\\SplunkLogs\\Splunk_ArkeSitecore_ErrorLogRCA.csv")
| eval message=coalesce(message,SitecoreError_Short )
 | stats count values(Plain_Error_Description) as Error_Description values(Root_Cause) as Root_Cause values(Proposed_NextSteps) as Proposed_NextSteps values(log_level) as log_level values(exception) as exception by message

Update
Per your screenshot values, tt seems field message and SitecoreError_Short do not have exact match but SitecoreError is combination of values from message and exception, so give this version a try as well

index="main" (sourcetype="sitecore:*" OR source="C:\\SplunkLogs\\Splunk_ArkeSitecore_ErrorLogRCA.csv")
| eval message=coalesce("Exception: ".exception." Message: ".message,SitecoreError_Short )
 | stats count values(Plain_Error_Description) as Error_Description values(Root_Cause) as Root_Cause values(Proposed_NextSteps) as Proposed_NextSteps values(log_level) as log_level  by message

[dmcgeearke]

This worked, except the fields that you coalesced are not "exact match"

the "Sitecore_ErrorShort" field often contains slightly different information than whats been parsed into message, so i would need to match on a wildcard of some sort to allow it to "contain" the message. or have some semblance of a matching ratio to find a match

Does coalesce work like that, or is that a different command?

Example would be:

message = Object reference not set to an instance of an object.

Sitecore_ErrorShort = Exception: System.NullReferenceException Message: Object reference not set to an instance of an object.

Another example would be

message = Could not submit session for contact c771cbea-b75f-4e26-9404-ba95be407bc1

Sitecore_ErrorShort = Could not submit session for contact [GUID]

Or even something like this:
message = MediaRequestProtection: An invalid/missing hash value was encountered. The expected hash value: 1903367452EBDB39828FC1A6E015EB87D33673DE. Media URL: /-/media/././././site.ashx, Referring URL: (empty)

Sitecore_ErrorShort = ERROR MediaRequestProtection: An invalid/missing hash value was encountered. The expected hash value: [Hash Value]. Media URL: [Media Url], Referring URL: [Referring Url]

[somesoni2]

That make the things very difficult. The coalesce command is just creating a common field in each event so that the stats would work (a way of combining values, the field message will either have value from field message from sourcetype=sitecore* OR SiteCore_ErrorShort, based on from where the event is coming from). There may have been an option to use the wildcard for matching (using subsearch as filter), but your actual data doesn't have wildcards in them, do they? It would be impossible for Splunk to assume the portion of string to match (in other words, where to put the wildcard). In my example search, if you have a way to make both the field same (by adding wildcard or truncating them), then you would be able to match. Or at least have one of them wildcarded.