topic Re: help on subsearch which works randomly in Splunk Search

help on subsearch which works randomly

jip31 — Wed, 20 Nov 2019 10:46:53 GMT

I use the search below
what is strange is that sometimes it works fine and five minutes ago I can retrieve the fields "Geoloc" which is build after the join
I tried to modify the type (outer or left) but it continue to works randomly
what is the problem please???

[| inputlookup host.csv 
    | table host] `disks` 
| fields Type Name TotalSpaceKB FreeSpaceKB host 
| 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 by host 
| where FreeSpace <= 132 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 `wire` 
    | fields USERNAME NAME AP_NAME Building 
    | rename USERNAME as host 
    | eval host=upper(host) 
    | lookup test2.csv NAME as AP_NAME OUTPUT Building 
    | eval Building=upper(Building) 
    | stats last(Building) as "Geoloc" by host ] 
| rename host as Hostname 
| table Hostname "Free space" "Total space" Model Site Country "Geoloc" 
| sort +"Free space"

Re: help on subsearch which works randomly

gcusello — Wed, 20 Nov 2019 11:00:40 GMT

Hi @jip31,
you should check the number of results of you subsearch running it by itself, because there a limit of 50,000 results in subsearches.
So you should try to build you search in a different way, putting the search (now in subsearch) as the main search and eventually inpulookup as subsearch.
In addition remember that join isn't a performant command (Splunk isn't a DB) and it's prefereable to use other approaches as stats and join command is to use only when you haven't other solutions.

In other words, something like this (I cannot test it, but follow the approach):

`wire` 
| fields USERNAME NAME AP_NAME Building 
| rename USERNAME as host 
| eval host=upper(host) 
| lookup test2.csv NAME as AP_NAME OUTPUT Building 
| eval Building=upper(Building) 
| lookup host.csv host OUTPUT  Type Name TotalSpaceKB FreeSpaceKB
| ...

Ciao.
Giuseppe

Re: help on subsearch which works randomly

jip31 — Wed, 20 Nov 2019 11:39:07 GMT

Hi
Yes I have more than 50,000 results in my subsearches
Does it explains that I have results randomly?
And concerning your proposal it means that I have to build my host.csv file automatically
For different reasons I prefer to query the size disk directly in the index....

Re: help on subsearch which works randomly

gcusello — Wed, 20 Nov 2019 11:49:07 GMT

Hi @jip31,
it's random probably because not in all searches you have more than 50,000 results or there aren't relevant variations when subsearch exceeds 50,000 results.

About the second point, in your search you use a lookup called host.csv as first command (inputlookup) in search, use it instead in subsearch or using the lookup command, not in main search.
In other words, use in main search the search on index that has more than 50,000 results.

Ciao.
Giuseppe

Re: help on subsearch which works randomly

jip31 — Wed, 20 Nov 2019 12:06:43 GMT

for the testing I have deleted [| inputlookup host.csv
| table host] so it means that i run the search on random host
and I have used the indexhat has more than 50,000 results in the min search but it changes nothing....

Re: help on subsearch which works randomly

gcusello — Wed, 20 Nov 2019 13:07:51 GMT

Hi @jip31,
as I said, the best way is to use a different approach, but for test try invert the searches: put the search on index in main search and inputlookup in subsearch:

`wire` 
     | fields USERNAME NAME AP_NAME Building 
     | rename USERNAME as host 
     | eval host=upper(host) 
     | lookup test2.csv NAME as AP_NAME OUTPUT Building 
     | eval Building=upper(Building) 
     | stats last(Building) as "Geoloc" by host
 | join host 
     [ | inputlookup host.csv 
     | table host] `disks` 
 | fields Type Name TotalSpaceKB FreeSpaceKB host 
 | 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 by host 
 | where FreeSpace <= 132 AND TotalSpace >= 64 
 | eval FreeSpace=FreeSpace." GB", TotalSpace=TotalSpace." GB" 
 | rename FreeSpace as "Free space", TotalSpace as "Total space" 
 | search Country=France 
 ] 
 | rename host as Hostname 
 | table Hostname "Free space" "Total space" Model Site Country "Geoloc" 
 | sort +"Free space"

Ciao.
Giuseppe

Re: help on subsearch which works randomly

jip31 — Wed, 20 Nov 2019 13:21:18 GMT

i done exactly what you said to me and i have the message "Error in 'join' command: Invalid join type"

Re: help on subsearch which works randomly

gcusello — Wed, 20 Nov 2019 13:24:20 GMT

Hi @jip31,
Sorry, there was a copy error

 `wire` 
      | fields USERNAME NAME AP_NAME Building 
      | rename USERNAME as host 
      | eval host=upper(host) 
      | lookup test2.csv NAME as AP_NAME OUTPUT Building 
      | eval Building=upper(Building) 
      | stats last(Building) as "Geoloc" by host
  | join host 
      [ | inputlookup host.csv 
      | table host `disks` 
  | fields Type Name TotalSpaceKB FreeSpaceKB host 
  | 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 by host 
  | where FreeSpace <= 132 AND TotalSpace >= 64 
  | eval FreeSpace=FreeSpace." GB", TotalSpace=TotalSpace." GB" 
  | rename FreeSpace as "Free space", TotalSpace as "Total space" 
  | search Country=France 
  ] 
  | rename host as Hostname 
  | table Hostname "Free space" "Total space" Model Site Country "Geoloc" 
  | sort +"Free space"

Ciao.
Giuseppe

Re: help on subsearch which works randomly

jip31 — Wed, 20 Nov 2019 13:43:59 GMT

sorry giuseeppe but it always dosent works
for summarize
when I do the code below, it works
but I have to integrate [ | inputlookup host.csv
| table host] somewhere....

`wire` 
| fields USERNAME NAME Building AP_NAME 
| rename USERNAME as host 
| eval host=upper(host) 
| lookup test2.csv NAME as AP_NAME OUTPUT Building 
| eval Building=upper(Building) 
| stats last(Building) as "Geoloc" by host 
| join host type=outer 
    [| search `diskspace` 
    | fields Type Name TotalSpaceKB FreeSpaceKB host 
    | 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 lookup_cmdb_fo_all.csv HOSTNAME as host output DESCRIPTION_MODEL SITE COUNTRY 
    | stats latest(FreeSpace) as FreeSpace latest(TotalSpace) as TotalSpace last(DESCRIPTION_MODEL) as Model last(SITE) as Site last(COUNTRY) as Country by host ] 
| where FreeSpace <= 32 AND TotalSpace >= 64 
| eval FreeSpace=FreeSpace." GB", TotalSpace=TotalSpace." GB" 
| rename FreeSpace as "Free space", TotalSpace as "Total space" 
| search Country=* 
| rename host as Hostname 
| table Hostname "Free space" "Total space" Model Site Country "Geoloc" 
| sort +"Free space"

Re: help on subsearch which works randomly

gcusello — Thu, 21 Nov 2019 08:10:18 GMT

Hi @jip31,
if you want to filter results, add it as subsearch in the main search or in another one search:

| ...
| search Country=* [ | inputlookup host.csv | table host ] 
| ...

if instead you need some additional field in your lookup, add it at the end of the search before the table command, using the lookup command:

   | ...
   | search Country=* 
   | lookup host.csv host OUTPUT your_additional_fields
   | rename host as Hostname 
   | table Hostname "Free space" "Total space" Model Site Country "Geoloc" 
   | sort +"Free space"

Ciao.
Giuseppe