Splunk Search

How to match wildcard in a join left?

JChris_
Path Finder

Consider I received the following logs:

cn=srv1.example.com;issuer=C=US, O=Amazon, OU=Server CA 1A, CN=Amazon
cn=srv1.example.com;issuer=C=US, O=Amazon, OU=Server CA 1B, CN=Amazon
cn=srv1.example.com;issuer=C=US, O=Acme, OU=Acme CA, CN=Acme
cn=srv1.foobar.example.com;issuer=C=US, O=Let's Encrypt, CN=R3
cn=srv2.foobar.example.com;issuer=C=US, O=Let's Encrypt, CN=R3
cn=srv2.foobar.example.com;issuer=C=US, O=Amazon, OU=Server CA 1A, CN=Amazon
cn=foobar.example.com;issuer=C=US, O=Let's Encrypt, CN=R3

 

And that I have a whitelist CSV lookup with the following content:

cn;issuer
srv1.example.com;C=US, O=Amazon, OU=*, CN=Amazon
srv2.example.com;C=US, O=Amazon, OU=*, CN=Amazon
*.foobar.example.com;C=US, O=Let's Encrypt, CN=*

 

I have a dashboard with a table where I want a column named "whitelisted" to have value "YES" in case the cn and issuer in that row matches the whitelist lookup, or be empty if not. Example of the intended output table:

cnissuerwhitelisted
srv1.example.comC=US, O=Amazon, OU=Server CA 1A, CN=AmazonYES
srv1.example.comC=US, O=Amazon, OU=Server CA 1B, CN=AmazonYES
srv1.example.comC=US, O=Acme, OU=Acme CA, CN=Acme 
srv1.foobar.example.comC=US, O=Let's Encrypt, CN=R3YES
srv2.foobar.example.comC=US, O=Let's Encrypt, CN=R3YES
srv2.foobar.example.comC=US, O=Amazon, OU=Server CA 1A, CN=Amazon 
foobar.example.comC=US, O=Let's Encrypt, CN=R3 

 

How can I achieve this?

 

I tried using the query below but it did not work for the wildcards.

| join type=left cn

 

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You could use the contents of the csv in a search to keep all the whitelisted and then repeat with NOT to get the unlisted ones

| makeresults
| eval _raw="cn;issuer
srv1.example.com;C=US, O=Amazon, OU=Server CA 1A, CN=Amazon
srv1.example.com;C=US, O=Amazon, OU=Server CA 1B, CN=Amazon
srv1.example.com;C=US, O=Acme, OU=Acme CA, CN=Acme
srv1.foobar.example.com;C=US, O=Let's Encrypt, CN=R3
srv2.foobar.example.com;C=US, O=Let's Encrypt, CN=R3
srv2.foobar.example.com;C=US, O=Amazon, OU=Server CA 1A, CN=Amazon
foobar.example.com;C=US, O=Let's Encrypt, CN=R3"
| multikv forceheader=1 
| table cn issuer
| search
    [| makeresults
    | eval _raw="cn;issuer
srv1.example.com;C=US, O=Amazon, OU=*, CN=Amazon
srv2.example.com;C=US, O=Amazon, OU=*, CN=Amazon
*.foobar.example.com;C=US, O=Let's Encrypt, CN=*"
    | multikv forceheader=1 
    | table cn issuer
    | format]
| eval whitelisted="yes"
| append
    [| makeresults
| eval _raw="cn;issuer
srv1.example.com;C=US, O=Amazon, OU=Server CA 1A, CN=Amazon
srv1.example.com;C=US, O=Amazon, OU=Server CA 1B, CN=Amazon
srv1.example.com;C=US, O=Acme, OU=Acme CA, CN=Acme
srv1.foobar.example.com;C=US, O=Let's Encrypt, CN=R3
srv2.foobar.example.com;C=US, O=Let's Encrypt, CN=R3
srv2.foobar.example.com;C=US, O=Amazon, OU=Server CA 1A, CN=Amazon
foobar.example.com;C=US, O=Let's Encrypt, CN=R3"
| multikv forceheader=1 
| table cn issuer
| search NOT
    [| makeresults
    | eval _raw="cn;issuer
srv1.example.com;C=US, O=Amazon, OU=*, CN=Amazon
srv2.example.com;C=US, O=Amazon, OU=*, CN=Amazon
*.foobar.example.com;C=US, O=Let's Encrypt, CN=*"
    | multikv forceheader=1 
    | table cn issuer
    | format]]
0 Karma

JChris_
Path Finder

Bump.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...