topic Re: help on join command please in Splunk Search

help on join command please

jip31 — Fri, 22 Feb 2019 12:04:31 GMT

I use the search below

index =* sourcetype=*
| dedup host 
| stats count

This search returns 87 events
I try to combine this results with another search in order to match the events of the first search with the events of the second search
So I have to have also 87 events but it doesnt works
could you help me please?

index=* sourcetype=* 
| dedup host 
| stats count 
| join type="outer" 
    [ search eventtype=OSBuild 
    | eval OS=if(........)
        Build=if(...........) 
    | stats latest(OS) as OS latest(Build) as Build by host] 
| stats values(OS) as OS values(Build) as Build by host 
| stats count as Total by OS Build

Re: help on join command please

lakshman239 — Fri, 22 Feb 2019 12:55:21 GMT

did you try using host in the main search?

Re: help on join command please

somesoni2 — Fri, 22 Feb 2019 14:50:36 GMT

Your stats is reducing the result of first row to just have single row with count and it has nothing to join with. In fact, for your requirement, you probably don't need join. Give this version a try

(index=* sourcetype=* ) OR (eventtype=OSBuild)
|eval OS=if(........),        Build=if(...........) 
 | stats latest(OS) as OS latest(Build) as Build by host
 | stats count as Total by OS Build

OR this (would perform better)

| tstats count WHERE index=* sourcetype=* by host 
| append [venttype=OSBuild 
     | eval OS=if(........)
         Build=if(...........) 
     | stats latest(OS) as OS latest(Build) as Build by host]
| stats values(OS) as OS values(Build) as Build by host 
| stats count as Total by OS Build

Re: help on join command please

jip31 — Fri, 22 Feb 2019 15:58:11 GMT

hi
I dont undertsand why you use "OR"
in the first eventtype I collect some host following a specific criteria
From this host list, I want to cross these host with the second eventype
thats the reason why I need to use a join command...
I have done this
could you confirm me its good please??

eventtype=Flag (NOT host=E* AND NOT
    host=I*) 
| join type="outer" 
    [ search eventtype=OSBuild (NOT host=E* AND NOT
        host=I*) 
    | eval OS=if(x), 
        Build=if(x) 
    | stats latest(OS) as OS latest(Build) as Build by host 
        ] 
| stats dc(host) as Total by OS Build

Re: help on join command please

camillak — Fri, 22 Feb 2019 16:17:19 GMT

(eventtype=Flag OR eventtype=OSBuild) NOT (host=E* host=I*)
| dedup host eventtype
|  eval OS=if(eventtype=OSBuild AND (x), 'y', OS),
        Build=if(eventtype=OSBuild AND (x), 'y', Build)
| stats dc(host) as Total by OS, Build

It looks like you're trying to populate OSBuild events with a field that already exists in Flag. If so, this should work.
Edit: if the stuff you're evaluating in OSBuild can be null, move the dedup after the eval and use

| search OS=* Build=*
|dedup host eventtype

Should still be more performant than join I think

Re: help on join command please

jip31 — Fri, 22 Feb 2019 17:34:26 GMT

The only common field is host
What méans 'y' please?