Splunk Search

Join alternatives

LynneEss
Engager

I've read in other posts that using join in Splunk isn't great so I'm looking for a better way to do my search.

I want a table of users connected to the company VPN, who are not using a corporate device and who are not contractors. The first join is to find non-corporate devices and the second join is to find users who are not contractors.

Currently the search looks something like this:

 

index=firewall vpn_connection=success
| dedup device_name
| table device_name, user, src_ip
| join type=outer left=vpn right=AD where vpn.device_name=AD.name [| inputlookup AD_Computer_LDAP_list | table name]
| where isnull('AD.name')
| table vpn.device_name, vpn.user, vpn.src_IP
| rename vpn.user as user, vpn.device as device
| join type=left left=connected right=contractor where connected.user=contractor.user [| inputlookup AD_User_LDAP_list | where like(memberOf, "%contractor%") | eval user=lower(sAMAccountName) | table user] 
| where isnull('contractor.user')
| table connected.device, connected.user, connected.src_IP

 

Any way to avoid using joins and to simplify this would much appreciated!

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @LynneEss,

yes! as @ITWhisperer said, I forgot the NOT condition:

index=firewall vpn_connection=success NOT ( [ | inputlookup AD_Computer_LDAP_list | 
 rename name AS device_name | fields device_name ] [ | inputlookup AD_User_LDAP_list | where like(memberOf, "%contractor%") | eval user=lower(sAMAccountName) | fields user ] )
| dedup device_name
| table device_name user src_IP

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @LynneEss,

in Splunk join is used to correlate two (or more ) searches using one or more common keys and take fields from both the searches.

Splunk isn't a DB (remember!) and you can have the above requirement using stats command.

But in your question, you need to filter a search using results from other two searches and it's a different thing: you cona use subsearches with the only attention to have the same field names in main and sub searches.

In your case:

index=firewall vpn_connection=success [ | inputlookup AD_Computer_LDAP_list | 
 rename name AS device_name | fields device_name ] [ | inputlookup AD_User_LDAP_list | where like(memberOf, "%contractor%") | eval user=lower(sAMAccountName) | fields user ] 
| dedup device_name
| table device_name user src_IP

This solution has only one limit: you can have only until 50,000 results in subsearches, but with a lookup it shouldn't be a problem.

In addition, the lookup command is the same thing of a join with a lookup instead inputlookup.

Ciao.

Giuseppe

gcusello
SplunkTrust
SplunkTrust

Hi @LynneEss,

yes! as @ITWhisperer said, I forgot the NOT condition:

index=firewall vpn_connection=success NOT ( [ | inputlookup AD_Computer_LDAP_list | 
 rename name AS device_name | fields device_name ] [ | inputlookup AD_User_LDAP_list | where like(memberOf, "%contractor%") | eval user=lower(sAMAccountName) | fields user ] )
| dedup device_name
| table device_name user src_IP

Ciao.

Giuseppe

gcusello
SplunkTrust
SplunkTrust

Hi @LynneEss,

good for you, see next time!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I think @gcusello needs a couple of NOTs in the search since @LynneEss wanted devices which are not in the company list and users who were not contractors.

This run-anywhere example generates 100 rows with random ip addresses for 60 (3 * 5 * 4) user/device combinations, and removes the first 20 devices (assumed to be known by the company) and then removes a fifth of the users who are registered as being contractors. You can run the separate parts to see the data and compare with the results to see that the only device / user combinations are neither known devices nor contractors.

 

| makeresults count=100
| streamstats count as row
| eval user=mvindex(split("ABC",""),row%3).mvindex(split("abcde",""),row%5)
| eval src_ip=(random()%255+1).".".(random()%256).".".(random()%256).".".(random()%256)
| eval device_name=user.mvindex(split("WXYZ",""),row%4)
| search NOT
    [| makeresults count=20
    | streamstats count as row
    | eval device_name=mvindex(split("ABC",""),row%3).mvindex(split("abcde",""),row%5).mvindex(split("WXYZ",""),row%4)
    | table device_name] NOT
    [| makeresults count=60
    | streamstats count as row
    | eval user=mvindex(split("ABC",""),row%3).mvindex(split("abcde",""),row%5)
    | eval memberOf="employed as ".mvindex(split("permanent,contractor",","),row%5)." workforce"
    | where like(memberOf,"%contractor%")
    | table user]
| table device_name user src_ip

 

index=firewall vpn_connection=success NOT [ | inputlookup AD_Computer_LDAP_list | 
 rename name AS device_name | fields device_name ] NOT [ | inputlookup AD_User_LDAP_list | where like(memberOf, "%contractor%") | eval user=lower(sAMAccountName) | fields user ] 
| dedup device_name
| table device_name user src_IP
Get Updates on the Splunk Community!

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

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