Lookup searches and regex?

[klaudiac]

Hi guys,

New to Splunk so pardon the simplicity of my question!

Im trying to bounce my csv list off another one. I've a list of servers in my spreadsheet, uploaded it to a lookup table files. By my problem is (I think) that the other file has the server names in a different format.

Eg. my server name is lets say "gb1234" and the other file it's saved as "UK gb1234" or "EU UK gb1234"

I came up with a basic search command: 

| inputlookup MY_LIST.csv

| eval host=upper(host)

| lookup OTHER_LIST asset_key as host

| fields host, asset_version, asset_bu

But I'm guessing that because of the format in the search i need to use regex.... But where and how??  

[gcusello]

Hi @klaudiac,

if the longer field is in the main lookup, you can put the regex before the lookup command.

| inputlookup MY_LIST.csv
| rex field=host "(?<my_host>\w+)$"
| eval host=upper(host)
| lookup OTHER_LIST asset_key as my_host
| table my_host asset_version asset_bu

If instead the longer field is in the secondary lookup, you cannot use the lookup command, but you have to append the secondary lookup (with inputlookup) adding the regex in the subsearch; then you have to correlate the two lookups using a stats command; something like this:

| inputlookup MY_LIST.csv
| eval host=upper(host)
| append [ | inputlookup OTHER_LIST | rex field=asset_key "(?<host>\w+)$" | eval host=upper(host) | fields host asset_version asset_bu]
| stats values(asset_version) AS asset_version values(asset_bu) AS asset_bu BY host

Ciao.

Giuseppe

[bowesmana]

You can't do regex during the lookup, but maybe the easiest option is to 'clean' the data in the second lookup, so that you create a new column which is the last word of the multi-word host name and then lookup on that.

But it will depend on the permutations of that second host name as to how you could address this

You can use wildcard lookups by adding * to the lookup value and creating a lookup definition defining the field as a wildcard field, but that will again require some processing of the lookup.