Splunk Search

How to find value from lookup table dynamically by matching substring in field value?

New Member

Hi,

I would like to know how to find value from lookup table dynamically by matching string in field value.

For example :
SNo - ErrorMessage
1.Unable to access One Corp
2.FileSystem_HighUtilization Server: ALL128, Issue:(B)ase, Severity:P2, per E&N
3.TEN -Safari-STA SAT(Audio)
1. MBT GOLD CLOUD MAXSOFT ALERT io's are identical) 12345

Lookup table:
Key-----value
FileSystem ---- Application Problem
Safari ---- Browser Problem
CLOUD ---- Server Issue
access----Login Issue

The query to read tokes from field value and then find match string which are defined in the lookup table and then get corresponding value from lookup table.

Below is expected out put :
SNo----ErrorMessage ----MatchingString (key from lookup table)----Value (corresponding value of key from lookup table)
1 ---- Unable to access One Corp ----access----Login Issue
2 ----- FileSystem_HighUtilization Server: ALL128, Issue:(B)ase, Severity:P2, per E&N -----FileSystem----Application Problem
3 ---- TEN -Safari-STA SAT(Audio)-----Safari----Browser Problem
4 -----MBT GOLD CLOUD MAXSOFT ALERT io's are identical) 12345 ----CLOUD---Server Issue

Can you please help how to frame the query for the above scenario.

Thanks in Advance,

Regards,
Ravi

0 Karma
1 Solution

Legend

Try this

| inputlookup lookupfilename.csv | map search="search index=foo sourcetype=bar ErrorMessage=\"*$key$*\" | eval Value=\"$value$\" " | eval MatchString="$key$" | table SNo ErrorMessage MatchString Value"

View solution in original post

0 Karma

Legend

Try this

| inputlookup lookupfilename.csv | map search="search index=foo sourcetype=bar ErrorMessage=\"*$key$*\" | eval Value=\"$value$\" " | eval MatchString="$key$" | table SNo ErrorMessage MatchString Value"

View solution in original post

0 Karma

New Member

Hello Sundareshr,

thanks for your solution, it is working fine.

Regards,
Ravi

0 Karma

New Member

Hi Sundareshr,

| inputlookup lookupfilename.csv | map search="search index=foo sourcetype=bar ErrorMessage=\"$key$\" | eval Value=\"$value$\" " | eval MatchString="$key$" | table SNo ErrorMessage MatchString Value"

The above syntax gives results only where there are mapping key -value pair, for example index=foo contains 100 records, the above syntax gives only mapping results assuming if 75 records are mapped with key with ErrorMessage then 75 records will get populate, the remain records will be ignored.

inputlookup command to be used in the first place of search string and it can't support to use in the middle for search string. In the below example, it gives 100 records of which 75 records contains mapping key-value and the remaining records are still to be populate with blank in the corresponding Value field.

For example :
index="sample_data_index"|reverse |streamstats current=f last(LogDate) as LogDate last(ErrorMessage ) as ErrorMessage last(AppID) as AppID by incident_number|table incident_number LogDate AppID ErrorMessage Value ( Value - this has to get populate from lookup table with mapping string for ErrorMessage)

The above search string contains 100 records, while using inputlookup after mapping ErrorMessage with corresponding Key-Value from lookup file, search string has to return 100 records, with additional column as Value where it fetches data from lookup field wherever finds match string.

Thanks in advance for helping.

Regards,
Ravi

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!