Splunk Search

Is a JOIN clause in SQL equal to an OR operator in Splunk?

prathikpisplunk
Explorer

SQL JOIN clause gets intersection of two tables.

In Splunk search, if I use OR on two different sources, I am not getting intersectioning...instead looks like I am getting combined records from two sources.

Please clarify. In case I want intersection, what should I be using?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index="79390-np" sourcetype=np-cache-v2  (physicalType="MODULE" currentEoxMilestone="SALE_DATE" source="hw*eox.") OR (source="group_member" groupId="185524" ) OR (source="device_details" productFamily!=null) | eval source=if(match(source,"hw.*eox\."),"hweox",source)
| table deviceId source productId deviceName deviceIp swVersion configTime inventoryTime
| stats count values(*) as * by deviceId | where mvcount(source)=3 
 | stats sum(count) AS Total by deviceName, productId, deviceIp, swVersion, configTime, inventoryTime 
| eval configTime=strftime(strptime(configTime,"%Y-%m-%dT%H:%M:%S"),"%Y-%m-%d") 
| eval inventoryTime=strftime(strptime(inventoryTime,"%Y-%m-%dT%H:%M:%S"),"%Y-%m-%d") 
| rename deviceName as Device, Total as Count, productId as "Product ID", deviceIp as "IP Address", swVersion as Version, configTime as "Config Collected", inventoryTime as "SNMP Collected" 
| table Device, "Product ID", Count, "IP Address", Version, "Config Collected", "SNMP Collected"
0 Karma

somesoni2
SplunkTrust
SplunkTrust

An OR operator is for union, not intersection.
There are alternative to join command which involves using OR operator but with conjunction with stats. If you could provide the final calculation/output that you expect we can help build the join replacement query.

0 Karma

prathikpisplunk
Explorer

Thanks for reply. Below is my query , where I want to replace join . Same index but sources are different

index="79390-np" sourcetype=np-cache-v2 physicalType="MODULE" currentEoxMilestone="*SALE_DATE*"  source="*hw*eox.*"   
| join deviceId         
    [search index="79390-np" sourcetype=np-cache-v2 source="*group_member*" groupId="185524"   
    | dedup deviceId  
    | fields + deviceId]   
| join deviceId      [search index="79390-np" sourcetype=np-cache-v2  source="*device_details*" productFamily!=null ]  
| stats count(productId) AS Total by deviceName, productId, deviceIp, swVersion, configTime, inventoryTime 
| eval configTime=strftime(strptime(configTime,"%Y-%m-%dT%H:%M:%S"),"%Y-%m-%d") 
| eval inventoryTime=strftime(strptime(inventoryTime,"%Y-%m-%dT%H:%M:%S"),"%Y-%m-%d") 
| rename deviceName as Device, Total as Count, productId as "Product ID", deviceIp as "IP Address", swVersion as Version, configTime as "Config Collected", inventoryTime as "SNMP Collected" 
| table Device, "Product ID", Count, "IP Address", Version, "Config Collected", "SNMP Collected"
0 Karma

Richfez
SplunkTrust
SplunkTrust

This seems right. billy OR sally as a search string will return any events where EITHER billy occurs or where sally occurs. The intersection would be AND, wouldn't it? And "AND" is implicit between search terms so you don't need it.

To recap:

billy OR sally

Would find all events with either billy or sally somewhere in them.

billy sally

Would find all events with bothbilly and sally somewhere in them.

(billy OR sally) suzy

Would find all events with either billy or sally somewhere in them, but only if they also had suzy. So events with suzy and billy would be returned along with those with suzy and sally, but if suzy wasn't in the event it wouldn't be.

If you'd like to provide the actual search you are running and perhaps a few lines of sample data, we can be more specific with the help, but hopefully this is enough to get you started!

Keep in mind Splunk is not SQL. There are definitely some similarities, but there are as many differences. This document may help make the transition.

0 Karma

prathikpisplunk
Explorer

Hi ,
Query using joins

index="79390-np" sourcetype=np-cache-v2 physicalType="MODULE" currentEoxMilestone="*SALE_DATE*" source="*hw*eox.*" | join deviceId  [search index="79390-np" sourcetype=np-cache-v2  source="*device_details*" productFamily!=null ]

fetches 7037 events.

index="79390-np" sourcetype=np-cache-v2 (source="*hw*eox.*" AND currentEoxMilestone="*SALE_DATE*" AND physicalType="MODULE") OR (source="*device_details*" AND productFamily!=null ) 

fetches me 103,228 events.

I wanted to avoid joins and use OR , but this is really confusing me now....

0 Karma

prathikpisplunk
Explorer

Really need the solutions as early as possible...please guide me

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 ...