How do I match values with different file extensions in a lookup?

[user93]

I have a lookup table, but the match is not exact to the relevant indexed field.

The field that is indexed has string.extension and the lookup table has string.extension. The strings match but the extensions do not. I want to output a new field from the lookup table where the strings match.

index=server host=relevant sourcetype=stats topic!=abc.htm | top topic by product limit=3 | lookup "topic name" base as topic OUTPUT title

My title field is empty. How do I make the near match work, or at least ignore the file extensions? I have some limitations as a user and I'm not able to upload or modify the lookup table.

Thanks for any help. I'm brand new to Splunk. Love the product, but still brand new.

[Vijeta · Accepted solution]

Try this-

index=server host=relevant sourcetype=stats topic!=abc.htm | top topic by product limit=3 |rex field=topic "(?<string>\w+)"|join string|[|inputlookup "topic name" |rex field=base "(?<string>\w+)"]

[user93]

This is really great, but did not work immidiately. The regex works, but I get an error that subsearches are only valid as commands.

I tested the rex command with: |inputlookup "topic name" | rex field=basename "(?\w+)"

I get the desired output for the rex command with the string added as a new field without the extension. Now I just have to join the two without the error. Thanks for helping me on the right track. I'll continue trying and report back if I have success.

[Vijeta]

@user93 There was an extra | in my previous search after join use this-

index=server host=relevant sourcetype=stats topic!=abc.htm | top topic by product limit=3 |rex field=topic "(?<string>\w+)"|join string[|inputlookup "topic name" |rex field=base "(?<string>\w+)"]

[user93]

Don't know how I missed it too 🙂

Thank you so much. This worked perfectly 🙂

[Vijeta]

@user93 Great! Please accept the answer if your problem is solved.

[user93]

Ok! Answer accepted. Thank you again.

I have a new problem I'm working on now. Some of the items in the table seem to be disappearing if they don't have a match in the table. This one though, I think with enough effort I can figure out.