Lookup in column A, grab value from column B, compare to a field in search result and don't display if values match?

[timm747747]

Hey guys, I have a search that gives me a login from a country along with the user and the user's "work country". Unfortunately the work country is an abbreviation so I have a lookup table that contains a list of countries and their abbreviations.

What I want to do is lookup the Country in the lookup table column A, grab the value in column B from the lookup table and then if it matches the user's work country do not display the line in my search results.

I've searched and tried a ton of things with no luck.

Any ideas?

Thanks!

T

[woodcock · Accepted solution]

Like this:

| tstats prestats=false local=false summariesonly=true count from datamodel=Authentication WHERE `aaa_src_external` by Authentication.app,Authentication.src, Authentication.user,Authentication.action,_time, index 
| iplocation Authentication.src 
| search Country!="United States" AND Country!=Canada AND Authentication.action=success AND Authentication.app!=Exchange AND index!=amp_* 
| rename Authentication.user as user 
| `get_identity4events(user)`


| lookup country_abbrev.csv user_work_country OUTPUT Country AS user_work_country
| where user_work_country = Country

| fields _time Authentication.app Authentication.src Country user user_identity_tag user_work_city user_work_country user_managedBy 
| rename Authentication.app as "Authentication App",Authentication.src as "Authentication Source", user as User, user_identity_tag as "User Identity Tag", user_work_city as "User's Work City", user_work_country as "User's Work Country", user_managedBy as "User's Manager"

[timm747747]

I probably should have posted the search I'm using. The lookup table is called country_abbrev.csv and it has a column labeled country which is the full country name and a column labeled "user's work country" which is the country abbreviation.

| tstats prestats=false local=false summariesonly=true count from datamodel=Authentication WHERE 
`aaa_src_external` by Authentication.app,Authentication.src, Authentication.user,Authentication.action,_time, index 
| iplocation Authentication.src 
| search Country!="United States" AND Country!=Canada AND Authentication.action=success AND Authentication.app!=Exchange AND index!=amp_* 
| rename Authentication.user as user 
| `get_identity4events(user)` 
| fields _time, Authentication.app,Authentication.src,Country,user,user_identity_tag,user_work_city,user_work_country,user_managedBy 
| rename Authentication.app as "Authentication App",Authentication.src as "Authentication Source", user as User, user_identity_tag as "User Identity Tag", user_work_city as "User's Work City", user_work_country as "User's Work Country", user_managedBy as "User's Manager"

[hardikJsheth]

You can try out following search .

index=main sourcetype=test |table user, countery_abbveriation | lookup country_table countery_abbveriation output country | table user country countery_abbveriation

Replace the column name, index, source type as apporpriate to your environment.