Splunk Search

How to match two columns based on prefix (Numbers/Letters) and do a loop through each result

Sp3ctre11
New Member

So we have a lookup and an index :

We need to correlate the prefix from the lookup with the data from the index, if it matches add a count of 1.

An example :

Prefix in the lookup is = 0d34

the index may contain = 0d34ds3fdsf

We will need to check all the results in the index and see if it matches with any of the prefix in the lookup and then add a count of 1 next to it / else keep going and check other values until match is found.

0 Karma

Sp3ctre11
New Member

unfortuantly, we are still yet to figure this out. These answers are not achieving our goal as of yet..

0 Karma

woodcock
Esteemed Legend

Evidently you need to use this as a filter on data, not a loookup so try this:

index=data [|inputlookup newlookup.csv | eval new = new . "*" | rename new AS Mac | table Mac ]

This assumes that the fields in the data are named Mac and that the field with the prefix data in the lookup is named new.

0 Karma

woodcock
Esteemed Legend

Create a lookup file newlookup.csv that contains fields and data like this:

new,Mac
YourOutputValueHere,0d34*

Note the asterisk appended to the Mac value(s)!
Next create a lookup definition newlookup that points to this file and add the WILDCARD(Mac) setting.

Now use it like this:

index=data | lookup newlookup new | table new Mac

NOTE that we are NOT using newlookup.csv because that would point to the file and skip the definition where the WILDCARD is. You must use newlookup and it will work fine.

0 Karma

Sp3ctre11
New Member

I've already been looking at this case. Not quite sure on how to accomplish this, i've edited the lookup file with WILDCARD=field ?

0 Karma

HiroshiSatoh
Champion

You can use wild card in lookup.
Please refer to this answer.

https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

0 Karma

Sp3ctre11
New Member

I've tried using this before hand,

Lets say my lookup = newlookup.csv
and contains the fields below :
- new
- Mac

I've added under advanced options =

- WILCARD(new), WILDCARD(Mac)

Would this be the syntax? for lookup
index=data
[ |lookup newlookup.csv new OUTPUT Mac]

0 Karma

HiroshiSatoh
Champion

This is set in the configuration file(transforms.conf:). It can not be set from the GUI.

[newlookup]
filename = newlookup.csv
match_type = WILDCARD(new)

newlookup.csv
new,Mac
0d34*,XXXXXXXXXXXXXXXXXXXXXXX

---search---
index=data
| lookup newlookup.csv new OUTPUT Mac

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 ...