Splunk Search

help on join command please

jip31
Motivator

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

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

jip31
Motivator

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
0 Karma

camillak
Path Finder
(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

0 Karma

jip31
Motivator

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

0 Karma

lakshman239
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...