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