topic Re: How to match two columns based on prefix (Numbers/Letters) and do a loop through each result in Splunk Search

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

Sp3ctre11 — Thu, 11 Oct 2018 02:10:12 GMT

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.

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

HiroshiSatoh — Thu, 11 Oct 2018 03:56:48 GMT

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

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

Sp3ctre11 — Thu, 11 Oct 2018 04:04:46 GMT

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 ?

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

Sp3ctre11 — Thu, 11 Oct 2018 04:10:50 GMT

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]

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

HiroshiSatoh — Thu, 11 Oct 2018 04:46:06 GMT

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

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

woodcock — Thu, 11 Oct 2018 15:23:31 GMT

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.

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

woodcock — Thu, 11 Oct 2018 22:59:35 GMT

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.

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

Sp3ctre11 — Mon, 15 Oct 2018 03:49:16 GMT

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