topic Re: LEFT JOIN not working in Splunk Search

LEFT JOIN not working

davidcraven02 — Tue, 29 Sep 2020 17:36:54 GMT

The below left join identified by ** is what i am trying to join onto the search but it is not listing all product_names per machine.

`GEN_ProductionWorkstations` 
| table machine 
| join type=left machine 
    [ search index=sccm sourcetype=otl_dbin_machineinfo host=opspk source=dbmon-dump:/otl_dbin_machineinfo ] 
| join type=left machine 
    [ search index=ad source=otl_addnsscan 
    | eval machine=lower(machine) 
    | rename data as IP, name as machine 
    | table machine, IP, User_Name0, Model0, lastRebootDaysAgo] 
**| join type=left machine 
    [ search index=sccm computername=* product_name=* 
    | search category!="Device Drivers, Configuration, and Utilities" 
    | eval machine=lower(computername) 
    | fields machine, product_name 
    | dedup machine, product_name 
    | table machine, product_name ]**

The final table should list machine, IP, User_Name0, Model0, lastRebootDaysAgo, product_name. I think the issue is that there can be many product_names per machine but only one User_Name0 and IP per machine.

Re: LEFT JOIN not working

micahkemp — Thu, 11 Jan 2018 02:17:12 GMT

This is almost certainly an instance where there's a better way to accomplish what you're looking for without using join. And looking at your search, the first join search only returns the machine value, which is what is being joined on, so I'm not convinced this accomplishes anything.

Can you include sample events for each of the searches and indicate what you want the final result to look for?

Re: LEFT JOIN not working

davidcraven02 — Thu, 11 Jan 2018 14:28:37 GMT

I want the final output to look like this;

machine | product_name
nas01b |Adobe AIR, Adobe Flash Player, Bloomberg Office Tools ....
nas02b |Adobe 3.2, Adobe Flash Player ....

When I run the below query It displays the product_name in one row and several machines grouped under ProductName but it only include 69 results when there should me much more results.

`GEN_ProductionWorkstations` 
| table machine 
|join type=left machine[search index=sccm sourcetype=otl_dbin_machineinfo host=opspkhf03p source=dbmon-dump://otl_db_opsccmsql_sccm/otl_dbin_machineinfo
| eval machine=lower(Name0)
| rename Client0 as SCCMClient, Active0 as SCCMClientActive, Caption0 as OperatingSystem] 
  | join type=left machine 
      [ search index=ad source=otl_addnsscan 
      | eval machine=lower(machine) 
      | rename data as IP
  ] 
  | join type=left machine 
      [ search index=sccm 
      | search category!="Device Drivers, Configuration, and Utilities" 
      | eval machine=lower(computername) 
     ] 
         | stats values(machine) AS ProductName by product_name

Re: LEFT JOIN not working

micahkemp — Thu, 11 Jan 2018 18:59:51 GMT

Is there a reason you wouldn't want to search for all the events in one search, then perform eval as necessary to normalize field names, then use stats to get the values you need grouped by product_name?

Re: LEFT JOIN not working

davidcraven02 — Thu, 11 Jan 2018 19:03:23 GMT

That sound like a good idea. How would I do this? Are you able to tweak my query?

Re: LEFT JOIN not working

micahkemp — Thu, 11 Jan 2018 19:04:36 GMT

It's tough to do without knowing what your initial macro expands to. Can you expand that, and also show sample events from each index/sourcetype/whatever and note how they are related to each other.