help on join subsearch

[jip31]

Hello
The first part of the search below (before join) works fine and the second part (after join) works fine too
But when I launch the entire search it doesnt works because I can retrieve the field "Geoloc"
What is the problem please??

[| inputlookup host.csv 
    | table host] `diskspace` 
| eval time = strftime(_time, "%m/%d/%Y %H:%M") 
| eval FreeSpace = FreeSpaceKB/1024 
| eval FreeSpace = round(FreeSpace/1024,1) 
| eval TotalSpace = TotalSpaceKB/1024 
| eval TotalSpace = round(TotalSpace/1024,1) 
| lookup test.csv HOSTNAME as host output SITE DESCRIPTION_MODEL ROOM COUNTRY 
| stats latest(FreeSpace) as FreeSpace latest(TotalSpace) as TotalSpace values(DESCRIPTION_MODEL) as Model values(SITE) as Site values(COUNTRY) as Country values(ROOM) as Room by host 
| where FreeSpace <= 1132 AND TotalSpace >= 64 
| eval FreeSpace=FreeSpace." GB", TotalSpace=TotalSpace." GB" 
| rename FreeSpace as "Free space", TotalSpace as "Total space" 
| search Country=France 
| join host type=outer
    [| search `toto` 
    | rename USERNAME as host 
    | lookup test2.csv NAME as AP_NAME OUTPUT Building 
    | stats last(Building) as "Geoloc" by host ] 
| table host "Free space" "Total space" Model Site Country "Geoloc" Room 
| sort +"Free space"

[gcusello · Accepted solution]

Hi jip31,
at first try to change in lower (or upper) case host both in main search and subsearch.

In addition check how many results you have in the subsearch because there's the limit of 50,000 results in subsearches.
In this case you have to rebuild your search without join or simply change the order: search in main search and inputlookup in subsearch.

In addition, why do you have square brackets at the beginning of the main search?

Anyway, join isn't a performant command (Splunk isn't a database!), so I suggest to rebuild you search without join, something like this:

| inputlookup host.csv  OR `toto` 
| all the evals and other commands
| stats values all fields BY host

Ciao.
Giuseppe

[jip31]

hi
| eval host=upper(host) change nothing
yes I have more than 50000 events in my subsearch
and I have square brackets at the beginning because in host.csv there is the host which I want to monitore in my main search and in my subsearch...

[gcusello]

Hi jip31,
try to rebuild your search in a different way:

`toto`
| lookup host.csv host OUTPUT <lookup_fields>
| ...

Ciao.
Giuseppe