Query with field from lookup table + concatenate + wildcards

[cruzcr]

I have a lookup file titled airports.csv. In the file, i have several fields, but one is AirportCode. This field has several thousand 3 letter airport codes. I need to query to see if these three letter codes, concatenated with an "=" symbol, appear anywhere in a particular field in my sourcetype titled URL. The end result is essentially a query that searches URL="*=AirportCode*"

lookup: airports.csv
lookup field: AirportCode
sourcetype: sct
sourcetype field: URL

I've used the below in testing my lookup and it works fine

[|inputlookup airports.csv | rename AirportCode as Airport | fields + Airport | head 1 ]

I've also tested with this, but it seems like it returns the presence of AirportCode anywhere in the logs, not just within the URL field. From here I've yet to have any luck using eval to concatenate the "=" and not get an error.

index=IDX sourcetype=sct |lookup airports.csv AirportCode as URL

I know I'm still a ways off, so any guidance is appreciated.

[TISKAR]

@cruzcr, Can you try this please:

 index=IDX sourcetype=sct | rex field=URL  "\*=(?<AirportCode >.*)\*" |lookup airports.csv AirportCode as URL

[cruzcr]

I get a regex error for missing terminator

[TISKAR]

Can you test please

[cruzcr]

This runs but it returns a super high volume of hits. Doesn't look like its correctly searching the =(airportcode) against the URL field, but instead is matching it to anywhere in the logs.

[TISKAR]

Sorry, can yout try this.

index=IDX sourcetype=sct | rex field=URL "\*=(?<AirportCode>.*)\*" |lookup airports.csv AirportCode as URL

[somesoni2]

Try this

index=idx sourcetype=sct [|inputlookup airports.csv | eval URL="*=".AirportCode."*" | table URL]

[kamlesh_vaghela]

Hi @cruzcr ,

Have you tried format command ? Can you please try this?

|inputlookup airports.csv | eval URL="*=".AirportCode."*" | table URL  | format | table search


index=IDX sourcetype=sct [|inputlookup airports.csv | eval URL="*=".AirportCode."*" | table URL | format | table search]

https://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Format

[cruzcr]

I revisited this answer today and was able to get it to work. I had to tweak my CSV but the query as you wrote it worked. Thank you!

[kamlesh_vaghela]

@cruzcr
Great!!!

Can you please share your answer and accept it? It will help the community.

🙂

Happy Splunking

[cruzcr]

I tried the above, it runs but does not return any results. Can you help explain what the table search does? I think the issue with your example is that URL is already a field in the sourcetype I want to search across, but it seems like the table search would execute in the subsearch without searching the specific URL field in the sourcetype. I tried modifying to below but I'm still am not getting any results.

index=idx sourcetype=sct [|inputlookup airports.csv | eval AC="*=".AirportCode."*" | table AC | format | table search]

I also tried this but I get an error for invalid term to the right of the "="

index=idx sourcetype=sct url=[|inputlookup airports.csv | eval Airport="*=".AirportCode."*" | rename Airport as query| fields query]