help on issue with join subsearch

[jip31]

hi

I use the search below in order to display a pie chart
When I execute the first part of the search (before join), I have 20 events
But when I execute the entire search, I have 2 events instead 20 events
Its strange because I use an identical search for another need and I havent this issue
where is the problem please??

| inputlookup tablet_host.csv 
| lookup Pana.csv "Hostname00" as host OUTPUT HealthState00 
| eval HealthState00=tonumber(replace(HealthState00,",",".")) 
| where HealthState00 < 85 
| lookup test.csv HOSTNAME as host output SITE 
| stats dc(host) as NbHostHealthInf85 by SITE
| join SITE
    [| inputlookup host.csv 
    | lookup test.csv HOSTNAME as host output SITE 
    | stats dc(host) as NbIndHost by SITE] 
| eval NbHostHealthSup85 = (NbIndHost - NbHostHealthInf85) 
| eval NbHostHealthSup85=NbHostHealthSup85, NbHostHealthInf85=NbHostHealthInf85 
| table NbHostHealthSup85 NbHostHealthInf85 
| rename NbHostHealthSup85 as "> 85%", NbHostHealthInf85 as "< 85%" 
| transpose

[jip31]

sorry, but it changes nothing
I had tested this before my email....

[arjunpkishore5]

could you post some sample data? Are you also getting the exact number of results in the outer join?

[jip31]

Sorry to dont be clear
I am going to summarize for better comprehension:
When I am doing :
| inputlookup tablet_host.csv
| lookup Pana.csv "Hostname00" as host OUTPUT HealthState00
| eval HealthState00=tonumber(replace(HealthState00,",","."))
| where HealthState00 < 85
| lookup test.csv HOSTNAME as host output SITE
| stats dc(host) as NbHostHealthInf85 by SITE

I have 16 events

When I am doing :
| inputlookup tablet_host.csv

| lookup test.csv HOSTNAME as host output SITE
| stats dc(host) as NbIndHost by SITE

I have 438 events
But when I execute the entire search, I have well 16 events for NbHostHealthInf85 field but I have only 282 events for NbIndHost field
And nothing change with type=outer or type=left!!
I have done an attachment where you can see that for > 85 %, I have not the goog count

[arjunpkishore5]

join by default uses Inner Join. This means only those records which match on both sides are retained. If you want to retain the records of the first search irrespective of if there is a match in the second, use left join |join type=left . If you need to retain both sides irrespective of a match, use outer join | join type=outer

Here is how your searches would look with these

Left Join

| inputlookup tablet_host.csv 
 | lookup Pana.csv "Hostname00" as host OUTPUT HealthState00 
 | eval HealthState00=tonumber(replace(HealthState00,",",".")) 
 | where HealthState00 < 85 
 | lookup test.csv HOSTNAME as host output SITE 
 | stats dc(host) as NbHostHealthInf85 by SITE
 | join SITE type=left
     [| inputlookup host.csv 
     | lookup test.csv HOSTNAME as host output SITE 
     | stats dc(host) as NbIndHost by SITE] 
 | eval NbHostHealthSup85 = (NbIndHost - NbHostHealthInf85) 
 | eval NbHostHealthSup85=NbHostHealthSup85, NbHostHealthInf85=NbHostHealthInf85 
 | table NbHostHealthSup85 NbHostHealthInf85 
 | rename NbHostHealthSup85 as "> 85%", NbHostHealthInf85 as "< 85%" 
 | transpose

Outer join

| inputlookup tablet_host.csv 
 | lookup Pana.csv "Hostname00" as host OUTPUT HealthState00 
 | eval HealthState00=tonumber(replace(HealthState00,",",".")) 
 | where HealthState00 < 85 
 | lookup test.csv HOSTNAME as host output SITE 
 | stats dc(host) as NbHostHealthInf85 by SITE
 | join SITE type=outer
     [| inputlookup host.csv 
     | lookup test.csv HOSTNAME as host output SITE 
     | stats dc(host) as NbIndHost by SITE] 
 | eval NbHostHealthSup85 = (NbIndHost - NbHostHealthInf85) 
 | eval NbHostHealthSup85=NbHostHealthSup85, NbHostHealthInf85=NbHostHealthInf85 
 | table NbHostHealthSup85 NbHostHealthInf85 
 | rename NbHostHealthSup85 as "> 85%", NbHostHealthInf85 as "< 85%" 
 | transpose

Here is the documentation for join keyword - https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/Join

Hope this helps. Please upvote and mark as answer if this is what you were looking for.

Cheers