Splunk Search
Highlighted

eval case before inputlookup file search

Communicator

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.

Tags (2)
0 Karma
Highlighted

Re: eval case before inputlookup file search

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

0 Karma
Highlighted

Re: eval case before inputlookup file search

Communicator

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 ?

0 Karma
Highlighted

Re: eval case before inputlookup file search

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"
0 Karma
Highlighted

Re: eval case before inputlookup file search

Communicator

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.

0 Karma
Highlighted

Re: eval case before inputlookup file search

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.

0 Karma
Highlighted

Re: eval case before inputlookup file search

Explorer

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

Highlighted

Re: eval case before inputlookup file search

Legend

@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.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: eval case before inputlookup file search

Communicator

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

0 Karma
Highlighted

Re: eval case before inputlookup file search

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.

0 Karma