Splunk Search

help on issue with join subsearch

jip31
Motivator

alt texthi

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
Tags (1)
0 Karma

jip31
Motivator

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

0 Karma

arjunpkishore5
Motivator

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

0 Karma

jip31
Motivator

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

0 Karma

arjunpkishore5
Motivator

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...