eval case before inputlookup file search

[surekhasplunk]

I have a dashboard table with fields like below.
Area field2 filed3
UK 100 200
US 300 400

In the drill down i am using a query to find some fileds from the lookup file used here to show some fields.
Now the problem is with the names under Area column

While displaying in the dashboard i used eval to rename the values to short names as UK instead of United Kingdom.

So in the lookup file United Kingdom and United States exists but UK and US doens not.

So while using a drill down i am using $row.Area$ token and using search query but since row.filed value comes as UK its unable to search in lookup file. So how to make it work so that i can evaluate my query with some case values if UK then change it to United Kingdoma and then search in lookup file and get all desired fileds.

[jenaugle]

One solution would be to have both the short and long Area values in your .csv file, so it will find an answer either way if you do the lookup for both the original dashboard and the drilldown. This would work for both the short and long Area names. Also, this would be better than using a eval/case statement, as eval/case would essentially remove the general flexibility of a lookup as values are added/changed/deleted in the .csv file, since they would also have to be managed in the eval/case. My assumption here is that there isn't some other mitigating factor that I'm not aware of that would preclude using this approach.

lookup .csv file:

Area1 Area2
United States US
United Kingdom UK
US United States
UK United Kingdom

[niketn]

@surekhasplunk, I will agree with @jenaugle, you should update your lookup table to have both Full Country name as well as abbreviated name. You can find the same in geo_attr_countries.csv lookup in the Splunk default Search App: $SPLUNK_HOME$/etc/apps/search/lookups/geo_attr_countries.csv

Try the following command check out the content of the lookup file:

| inputlookup geo_attr_countries.csv
If you have your own lookup what you can do is perform a Splunk Search so that 2 letter abbreviated Country name is mapped with the Country name in your lookup and the remaining fields from your lookup file. Then you can either download result as CSV and replace your lookup with this or else if all rows look as expected you can pipe in outputlook command to push the output table to your lookup file.

[surekhasplunk]

Hi @niketnilay,
Can i have a separate mapping.csv file just with the short name and full name in it then how should i write my query equivalent to and ammend the new mapping.csv file.

|inputlookup myfile.csv | search Area="$row.Area$" | fields employee_name "Employee ID"

Right now $row.Area$ is getting replaced by UK . Now how to use mapping.csv file in the query so that i can tell if Area=UK search in mapping.csv and get the correspoding full name that is United Kindgom then assign that value to Area and the the requried Employee ID

Thanks

[elliotproebstel]

Yes, you could certainly use two lookups, but it would be more efficient to add a single column to your original csv file. That way, you don't look up each field twice.

[surekhasplunk]

Hi @elliotproebstel,

Can you help me with the query using two lookups here

[elliotproebstel]

Sure. I'll assume your current dashboard search returns an event that contains the field Area, which contains the valueUS. So then you'll need a file called mapping.csv that has two columns:

country_abbreviation, country_full
US,"United States"
...

And then you have your existing file myfile.csv, and I'll assume you have multiple columns in that file, but one of them is something like country, and it contains full country names like "United States". If all of that is true, then your search will look like this:

[ base search ] | lookup mapping.csv country_abbreviation AS Area OUTPUT country_full | lookup myfile.csv country AS country_full

[surekhasplunk]

Hi am still struggling with this i am getting error error in lookup command.
my query starts with inputlookup and
| inputlookup myfile.csv |search country= [|lookup mapping.csv |search country_full=”United States" |fields country_abbreviation ] | fields "Employee Forename"

doesnot seem to be working.

[niketn]

@surekhasplunk, is there any problem with using single csv file myfile.csv? Just as country_abbreviation as a new column. This way you will not need to join two lookup files for same field.

[surekhasplunk]

i just got it working with map command thanks

[elliotproebstel]

I'd recommend that you not use an eval to change the value of the field. Instead, use a fieldformat command to change the display of the field but not the actual value. For example, I assume your current eval to replace United States with US and replace United Kingdom with UK looks like this:

[ base search ] | eval Area=case(match(Area, "United States"), "US", match(Area, "United Kingdom"), "UK")

If you replace eval with fieldformat, Splunk will still display "US" in place of "United States", but the actual value of the field will remain unchanged. This will allow you to reference it in the drilldown token. So here's the revised query:

[ base search ] | fieldformat Area=case(match(Area, "United States"), "US", match(Area, "United Kingdom"), "UK")

Check out some info on fieldformat here:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Fieldformat

[surekhasplunk]

Hi @elliotproebstel,

I followed what you said and tried to change my drilldown query but looks like its not getting me any result.

|inputlookup myfile.csv | search Area = "United States" | fieldformat Area=case(match(Area, "United States"), "US", match(Area, "United Kingdom"), "UK") |fields employee_name "Employee ID"

Here i feel since its trying to search first for the vale "United States" and then trying to format it. so is that causing no rsults or whats the issue ?

[elliotproebstel]

Just to clarify - the intent of my suggestion was that you use the fieldformat command in your original query to preserve the value of the field Area before it was passed to the drilldown. The query you posted above has no need for a fieldformat at all, since you are ultimately displaying only the employee_name and "Employee ID" fields. Does this command return any results?

|inputlookup myfile.csv | search Area="United States" | fields employee_name "Employee ID"

If it does, then you should be able to apply the fieldformat command string to your original search (in place of where you are using an eval/case statement. The drilldown will pass a token like $row.Area$ to the drilldown search, and you'll use $row.Area$ like this:

|inputlookup myfile.csv | search Area="$row.Area$" | fields employee_name "Employee ID"

[surekhasplunk]

I think i am not able to put my query clearly.
Let me explain again.

|inputlookup myfile.csv | search Area="United States" | fields employee_name "Employee ID"

This retruns result since area has got full area name
But in my dashboard the area name is just US
So when i pass search Area="$row.Area$" it takes US instead of full name and query results nothing.

[elliotproebstel]

Ok, but in your original search that populated the dashboard, do you have a part of your search string that uses eval/case to replace "United States" with "US"? If so, that is the place where I am recommending you replace eval with fieldformat.