topic Re: How to extract these characters from fields in events to do an exact match against fields in my CSV lookup table? in Splunk Search

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

alexandermunce — Sun, 18 Dec 2016 23:47:17 GMT

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?

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

alexandermunce — Mon, 19 Dec 2016 01:06:40 GMT

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

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

alexandermunce — Mon, 19 Dec 2016 01:07:12 GMT

and then optionally add the next part on the end:

... | fields - subCode

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

alexandermunce — Tue, 29 Sep 2020 12:10:41 GMT

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)