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

[prathikpisplunk]

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?

[somesoni2]

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"

[somesoni2]

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.

[prathikpisplunk]

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"

[Richfez]

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.

[prathikpisplunk]

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

[prathikpisplunk]

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