How do I join these two searches?

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I'm using the following searches:

Search 1 - "EI Auth"

Auth - index="main" auditSource=*auth* auditType=LoginEntitlements detail.Ref=* 
| stats count by detail.Ref 
| rename detail.Ref AS REF

*Search 2 - "EI Microservice" *

MicroService - auditSource="ei-microservice" detail.StatusCode="*" 
| stats count by detail.ERef 
| rename detail.ERef AS REF

What I'm trying to do is join the two using the field REF, but despite looking at the tutorial on the Splunk documentation and receiving help here I've still been unable the join the two.

Could someone perhaps show me please how I can join the two searches, and could you also confirm for me please whether I have to run the two searches separately before running the 'Join' search?

Many thanks and kind regards

Chris

[aljohnson_splun · Accepted solution]

First, use OR to get both:

(auditSource=auth auditType=LoginEntitlements detail.Ref=*) OR (auditSource="ei-microservice" detail.StatusCode="*" )

Secondly, use eval's coalesce() to push the two into a new field:

| eval REF = coalesce(detail.ERef, detail.Ref)

Thirdly, perform the glorious stats count

| stats count by REF

Does that do what you're looking for ?

---

Edit: To answer your question, when to use join:

[woodcock]

Is this flowchart PNG a creation by @aljohnson or someone else? Is there a broader context (maybe other great flowcharts) that I can see online?

[aljohnson_splun]

@woodcock the flowchart is from the docs, here, and the information within in it provided by @sideview (Nick Mealy)

[sideview]

o/ It's true the flowchart was included in the docs based on a nearly identical flowchart that I made years ago. Needs some updating probably. Maybe even an expansion of scope beyond just row aggregation. It comes in most handy when you try to explain to relatively new splunkers why they really shouldn't use join or append. Somewhat early on join clicks with new users, particularly those with SQL experience and they think of it as a primary tool. It is not. It is an edge case tool, where stats and eval are the primary tools. Short Version: If you think you need to use join, go find this flow chart and it'll probably tell you there's a better way.

[IRHM73]

Hi @aljohnson, I've had problems with Splunk this morning, and I think that this was the cause of the problems because the query is now showing no results found.

Many thanks and kind regards

Chris

[aljohnson_splun]

Sounds frustrating. If you need help on this query once you've resolved your other issues, you can @ mention me here again 🙂

[IRHM73]

That's great, I really appreciate it.

Kind Regards

Chris

[IRHM73]

Hi @aljohnson many thanks for taking the time to come back to me with this, I've tested the solution and unfortunately this doesn't return any statistics although it does return events.

Could you also possibly explain to me please in what situation would you use a join query?

Many thanks and kind regards

Chris

[aljohnson_splun]

I know for sure that this should world - it should return statistics...
What you're asking to do is very easy - searching over two sourcetypes to count two fields.
Are you sure there isn't anything you're leaving out of your examples ?

I've updated my question to include a flowchart.