How to extract these characters from fields in events to do an exact match against fields in my CSV lookup table?

[alexandermunce]

I have set up a lookup table csv file and this has been uploaded to Splunk, and I have also set up an associated stanza within transforms.conf which looks something like the below;

Table: sub_ref.csv
Fields: sub_code    company
        ABCD        Company 1
        ABCC        Company 2
        ABCA        Company 3

And in transforms.conf:

[sub_ref]
sub_ref.csv

Now this would be a fairly simple lookup, however the issue falls in the data.

I am comparing three separate event fields against the sub_ref lookup fields.

For the sake of this example, lets say the event fields are:
- comp
- comp_id
- branch

The third event field (branch) contains values which match exactly the data within the sub_code field in my CSV lookup - ie, 4 characters.

The first two event fields above, however, contain the lookup_field values followed by a trailing number, eg;

comp (event field)
ABCD001
ABCD002
ABCC001
ABCC002

So, obviously as my testing has proven, I am not able to lookup against this event field using my current lookup field data as there is not an exact match.

I have seen there is a way to include wildcards in my CSV lookup, by adding the following to my transforms.conf stanza:

match_type = WILDCARD(sub_code)

However, this requires admin changes which I am trying to avoid at this stage.

What I would rather do is to split up the first two fields into 2 new fields which extract the first 4 characters and then complete the lookup against these new fields - how would I go about this?

Is this the best way to go about this?

[alexandermunce]

My next concern is that I am having troubles with the LOOKUP command - it will not let me check multiple event_fields against the SINGLE lookup_field;

When I try to do a second lookup as below, there is no new field output;

... | lookup sub_ref sub_code AS comp, sub_code AS comp_ID OUTPUT company as Company

Do I need to run the lookup command once for each event field and then output all as the one output field (Company)

[alexandermunce · Accepted solution]

The solution I have come up with is as follows:

rex field=comp "(?P<subCode>\w{4}).*" | lookup sub_ref sub_code AS subCode OUTPUT company AS Company

[alexandermunce]

and then optionally add the next part on the end:

... | fields - subCode