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!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...