Splunk Search

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

timm747747
Path Finder

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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"

View solution in original post

0 Karma

woodcock
Esteemed Legend

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

timm747747
Path Finder

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

hardikJsheth
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...

Splunk Observability for AI

Don’t miss out on an exciting Tech Talk on Splunk Observability for AI!Discover how Splunk’s agentic AI ...

🔐 Trust at Every Hop: How mTLS in Splunk Enterprise 10.0 Makes Security Simpler

From Idea to Implementation: Why Splunk Built mTLS into Splunk Enterprise 10.0  mTLS wasn’t just a checkbox ...