How to match wildcard in a join left?

[JChris_]

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:

cn	issuer	whitelisted
srv1.example.com	C=US, O=Amazon, OU=Server CA 1A, CN=Amazon	YES
srv1.example.com	C=US, O=Amazon, OU=Server CA 1B, CN=Amazon	YES
srv1.example.com	C=US, O=Acme, OU=Acme CA, CN=Acme
srv1.foobar.example.com	C=US, O=Let's Encrypt, CN=R3	YES
srv2.foobar.example.com	C=US, O=Let's Encrypt, CN=R3	YES
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

How can I achieve this?

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

| join type=left cn

[ITWhisperer]

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

[JChris_]

Bump.