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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...