How can I use the lookup command where the target field is the root of the source field?

[andrewtrobec]

Hello all,

I have to use a lookup to get data but the problem is that the source field for the lookup is longer than the target field, but in all cases the target field is the root of the source field. Examples:

Source,Target
A1-A2-A3-A4-A5,A1-A2-A3
B1-B2-B3-B4-B5,B1-B2-B3-B4
C1-C2-C3-C4,C1-C2-C3
D1-D2-D3-D4-D5-D6,D1-D2-D3-D4-D5

In all cases the keywords are separated by -, but there is no fixed number of -to define the root. Basically I need to say something like "Where the target field is the root of the source field, return desired columns"

Is this possible through the lookup command?

Thank you and best regards,

Andrew

[sundareshr]

Have you tried wild-card matching for your lookup? Here is a great answer to a earlier question. Give this a try

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

[andrewtrobec]

I've followed the instructions but something doesn't make sense to me. Here are my configurations:

props.conf

[mysourcetype]
LOOKUP-sector = sectorlookup "Lookup Field" OUTPUT Sector

transforms.conf

[sectorlookup]
filename = L_Sectors.csv
match_type = WILDCARD("Lookup Field")

L_Sectors.csv

Lookup Field,Sector
A1-A2-A3*,Sector1
B1-B2-B3-B4*,Sector2
C1-C2-C3*,Sector3
D1-D2-D3-D4-D5*,Sector4

My question is: how do I structure the lookup command? Right now I have

* | lookup L_Sectors.csv "Lookup Field" OUTPUT Sector | table "Lookup Field", Sector

but I'm not getting results. Am I doing the search correctly?

[sundareshr]

Change you lookup command to this and you don't need the props.conf entry, that's for automated lookup. First lets get it to work in SPL, if that does, you can use the same syntax in props.

... | lookup L_Sectors.csv "Lookup Field" AS ID_Field OUTPUT Sector | table ID_Field, Sector

Also, I would suggest removing the space in the field name. Keeps is cleaner.

[sundareshr]

Is this data in your lookup file or in the events? If this is lookup, what does in you events?

[andrewtrobec]

Hello sundareshr, thanks for the reply.

The data above are the fields that I need to use for the lookup, source is in the events while target is in the lookup.

To give a clearer example, ID_Field is what I need match in the lookup. The events looks like:

ID_Field,S_Field1,S_Field2
A1-A2-A3-A4-A5,S_Field1Value1,S_Field2Value1
B1-B2-B3-B4-B5,S_Field1Value1,S_Field2Value2
C1-C2-C3-C4,S_Field1Value2,S_Field2Value3
D1-D2-D3-D4-D5-D6,S_Field1Value2,S_Field2Value2

While the lookup looks like:

ID_Field,T_Field1
A1-A2-A3,T_Field1Value1
B1-B2-B3-B4,T_Field1Value2
C1-C2-C3,T_Field1Value3
D1-D2-D3-D4-D5,T_Field1Value4

I need to create:

ID_Field,S_Field1,S_Field2,T_Field1
A1-A2-A3-A4-A5,S_Field1Value1,S_Field2Value1,T_Field1Value1
B1-B2-B3-B4-B5,S_Field1Value1,S_Field2Value2,T_Field1Value2
C1-C2-C3-C4,S_Field1Value2,S_Field2Value3,T_Field1Value3
D1-D2-D3-D4-D5-D6,S_Field1Value2,S_Field2Value2,T_Field1Value4

Under normal circumstances this would be easy, but in my case ID_Field in the lookup is the root of ID_Field in the events.

Hope this helps to clarify!

Regards,

Andrew