Splunk Search

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

Communicator

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?

0 Karma
1 Solution

Communicator

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

View solution in original post

0 Karma

Communicator

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)

0 Karma

Communicator

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

View solution in original post

0 Karma

Communicator

and then optionally add the next part on the end:

... | fields - subCode
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!