Splunk Search

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

alexandermunce
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

alexandermunce
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

alexandermunce
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

alexandermunce
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
0 Karma

alexandermunce
Communicator

and then optionally add the next part on the end:

... | fields - subCode
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...