help on join command please

[jip31]

hi

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

[somesoni2 · Accepted solution]

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

[jip31]

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

[camillak]

(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

[jip31]

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

[lakshman239]

did you try using host in the main search?

index=* sourcetype=*

| stats count by host
| 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