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!

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...