Data from a subsearch is repeated in the stats column?

[MScottFoley]

TL;DR
What is wrong with the SPL at the end?

I am trying to list the IIS cs_user_Agent(s) for each test customer.
The EventID field that is found in the SystemLog matches up with the IISEventId field in IIS. That is how they are connected.

The inner search (sourcetype="SystemLog*") run alone returns 6,000 events. That is correct. With the join 160,000 events are returned.  Since the sub search is run first and every  EventID is unique I would expect 6000 events.    

There is only one CustomerName shown in stats and it is the same in each row.  The CustomerName is also different on each search.  If I add a specific customer to the sub search, such as CustomerName="Bob's Pizza", or CustomerName="Bolts R Us" the same number of results are returned.

Search
The names have been changed to protect the innocent.  Any spelling errors or missing quotes are just a failure in my typing ability.  I have switched the two searches in the join and also switched the rename order and had the same problem.  If the subsearch is run first and the join uses the renamed field from the subsearch for the outer search this seems correct to me.   

index=myIndex sourcetype=IIS
  | join IISEventID  [ search index=myIndex sourcetype="SystemLog*" IsTestCustomer="True"
  | rename EventID as IISEventID
  | fields CustomerName ]
| stats count by CustomerName cs_User_Agent

This is a sample of the output.  I know that Bob's Burgers does not use PRTG.  If I run it again the CustomerName may be "The Three Broomsticks" or any other customer.

CustomerNAme	cs_User_Agent	count
Bob's Burgers	Rebex+HTTPS	2150
Bob's Burgers	Mozilla/4.0+	934
Bob's Burgers	Mozilla/5.0	611
Bob's Burgers	Amazon-Route53-Health-Check	464
Bob's Burgers	PRTG/Go+Health+Check	124

Thanks for any help

[gcusello]

Hi @MScottFoley,

Splunk isn't a database, so use join command only when you haven't any other solution to your requirement: join is very slow and there's the limit of 50,000 results in the subsearch.

use stats command, something like this:

(index=myIndex sourcetype=IIS) OR (index=myIndex sourcetype="SystemLog*" IsTestCustomer="True")
| rename EventID as IISEventID
| stats 
   values(CustomerName) AS CustomerName 
   values(cs_User_Agent) AS cs_User_Agent
   count 
   BY IISEventID
| stats sum(count) AS count by CustomerName cs_User_Agent

Ciao.

Giuseppe

[Tom_Lundie]

You're joining on IISEventID, but that field is not present in your sub-search and (because you're getting any output at-all) must not be present in your main search either.

In the join command you need to have a common field to join the two searches together, e.g. what links the sourcetype=IIS to the sourcetype=SystemLog*?

Let's say that the link is IISEventID, you need to make sure it is present in both searches. I have updated the | fields command in your subsearch to make sure it's present there but you'll need to do the rest with the main search as I can't see your _raw data:

index=myIndex sourcetype=IIS
  | join IISEventID  [ search index=myIndex sourcetype="SystemLog*" IsTestCustomer="True"
  | rename EventID as IISEventID
  | fields CustomerName, IISEventID ]
| stats count by CustomerName cs_User_Agent

Using the join command like this without specifying a type= defaults to a type=inner join which only returns events where a matching IISEventID is present between both a main search result and sub-search result, that might be exactly what you're looking for, but I just wanted to point this out. When you perform an inner join with a field that is not present in either search it behaves weirdly and seems to join the first result of the sub-search with all of the main search (which is exactly what you're seeing in the OP).

Also, FYI, Splunk generally recommends against using join. Sometimes there are legitimate use cases and this does look like it could be but make sure you have a read of the documentation to see if there is a better way you could approach this problem...

Let me know how you get on.

[MScottFoley]

I agree that I need to stop using join.  I need more practice doing things without it (like I do using things with it☺).  We probably have dozens if not hundreds of dashboards, alerts, and reports that use join.  

The field value that is common in both sourcetypes is EventID.  It is called IISEventID in the IIS logs though.  I rename the EventID in the SystemLog to IISEventID and then join on IISSEventID 

Sourectype=IIS
IISEventID=5698564-5213698-4666998

sourcetype="SystemLog*"
EventID=5698564-5213698-4666998

I am going to try this without the join using what gcusello provided.  

[Tom_Lundie · Accepted solution]

Sounds good,

Just bear in mind that with @gcusello's solution, the | rename command will not quite do what you need it to if your EventID is named differently within each sourcetype.

Instead, you'll need to dynamically assign the IISEventID using an | eval command as follows. The rest of the solution should be fine.

(index=myIndex sourcetype=IIS) OR (index=myIndex sourcetype="SystemLog*" IsTestCustomer="True")
| eval IISEventID = coalesce(EventID, IISEventID)
| stats 
   values(CustomerName) AS CustomerName 
   values(cs_User_Agent) AS cs_User_Agent
   count 
   BY IISEventID
| stats sum(count) AS count by CustomerName cs_User_Agent

In general, using stats and eventstats for this use case instead of a join is better but you just have to make sure that you have prepared your two sub-datasets before you try and merge them. The other thing is that join does not always act intuitively (e.g. the unexpected duplication behaviour that you were seeing in your original search). Not only is it slow, it's also hard to use!

I can guarantee that for the majority of those existing searches you've got using join, there will be a way to replace the join usage with a stats or eventstats command. It might be worth challenging yourself to have a look through and see if you can find different ways to replace them.