Splunk Search

LEFT JOIN not working

davidcraven02
Communicator

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.

Tags (2)
0 Karma

davidcraven02
Communicator

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

0 Karma

micahkemp
Champion

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.

0 Karma

micahkemp
Champion

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?

0 Karma

davidcraven02
Communicator

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

micahkemp
Champion

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?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...