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

[ravinallaparedd]

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

[sundareshr · Accepted solution]

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"

[ravinallaparedd]

Hello Sundareshr,

thanks for your solution, it is working fine.

Regards,
Ravi

[ravinallaparedd]

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