Compare one field from the search with a field in the lookup table, and list if there is a difference

[arsalanj]

Hey everyone,

I have a list that contains usernames and Countries.
The name of the list is user1.csv and its added in the lookup table files.

Cisco_ASA_user,Country
user1,United States
user2,United States
User3,United states

The countries are the locations that a user supposed to login from all the times.
I want to create a search that will compare the country of the user who logged in with the country on the list and shows me the result if it is not a match.

Let's say user1 supposed to login from the United States all the time, but for some reason, the country has changed to England.

This is my search so far:
index=cisco_asa vendor_class="aaa/auth" Cisco_ASA_message_id=113039
| iplocation src_ip
| table Cisco_ASA_user , Country | rename Cisco_ASA_user AS username Country AS Origin

This will give me the username and the country.

I don't know how to compare the Origin and username field values from my search with the values from |inputlookup user1.csv | fields Cisco_ASA_user Country.

Basically, I want to see the results where username=Cisco_ASA_user AND Origin!=Country

Any ideas of how to make this work?

[arsalanj · Accepted solution]

I could make it work.
I just added a little sauce to it thanks to you guys by giving me an idea of how it works.

index=cisco_asa vendor_class="aaa/auth" Cisco_ASA_message_id=113039
| iplocation src_ip
| fields Cisco_ASA_user, Country
| lookup user1.csv Cisco_ASA_user OUTPUT Country as Country_from_lookup Cisco_ASA_user as user
| search Country!=Country_from_lookup | where Cisco_ASA_user=user and Country!=Country_from_lookup
| table Cisco_ASA_user Country Country_from_lookup

with this search now I can get a table that shows me users who are supposed to be in a specific location but logged in from a different one.

Thanks a lot.

[niketn]

@arsalanj please try the following:

index=cisco_asa vendor_class="aaa/auth" Cisco_ASA_message_id=113039 
| iplocation src_ip
| fields Cisco_ASA_user, Country 
| lookup user1 user as Cisco_ASA_user OUTPUT Country as Country_from_lookup
| search Country!=Country
|  table Cisco_ASA_user Country Country_from_lookup

[somesoni2]

@niketnilay, I believe you wanted to write | search Country!=Country_from_lookup here.

[arsalanj]

CsvDataProvider - Unable to find filename property for lookup=user1 will attempt to use implicit filename.
04-19-2019 07:26:29.044 WARN CsvDataProvider - No valid lookup table file found for this lookup=user1
04-19-2019 07:26:29.044 ERROR CsvDataProvider - The lookup table 'user1' does not exist or is not available.
04-19-2019 07:26:29.044 WARN CsvDataProvider - Unable to find filename property for lookup=users1 will attempt to use implicit filename.
04-19-2019 07:26:29.044 WARN CsvDataProvider - No valid lookup table file found for this lookup=users1
04-19-2019 07:26:29.044 ERROR CsvDataProvider - The lookup table 'users1' does not exist or is not available.
04-19-2019 07:26:29.044 ERROR LookupProcessor - Error in 'lookup' command: Could not construct lookup 'user1, user, as, Cisco_ASA_user, OUTPUT, Country, as, Country_from_lookup'. See search.log for more details.
04-19-2019 07:26:29.046 ERROR SearchPhaseGenerator - Fallback to two phase search failed:Error in 'lookup' command: Could not construct lookup 'users1, user, as, Cisco_ASA_user, OUTPUT, Country, as, Country_from_lookup'. See search.log for more details.
04-19-2019 07:26:29.047 ERROR SearchOrchestrator - Error in 'lookup' command: Could not construct lookup 'user1, user, as, Cisco_ASA_user, OUTPUT, Country, as, Country_from_lookup'. See search.log for more details.
04-19-2019 07:26:29.048 INFO SearchStatusEnforcer - Enforcing disk quota = 10485760000
04-19-2019 07:26:29.048 INFO DispatchManager - DispatchManager::dispatchHasFinished(id='1555683988.37872', username='admin')

[somesoni2]

Try this

index=cisco_asa vendor_class="aaa/auth" Cisco_ASA_message_id=113039 
  | iplocation src_ip
  | fields Cisco_ASA_user, Country 
  | lookup user1.csv Cisco_ASA_user OUTPUT Country as Country_from_lookup
  | search Country!=Country_from_lookup
  |  table Cisco_ASA_user Country Country_from_lookup

[arsalanj]

Thank you, but It didn't work.

Error in 'lookup' command: Could not construct lookup 'user1, user, as, Cisco_ASA_user, OUTPUT, Country, as, Country_from_lookup'. See search.log for more details.

[somesoni2]

Just change | lookup user1 with | lookup user1.csv. Basically this

index=cisco_asa vendor_class="aaa/auth" Cisco_ASA_message_id=113039 
 | iplocation src_ip
 | fields Cisco_ASA_user, Country 
 | lookup user1.csv user as Cisco_ASA_user OUTPUT Country as Country_from_lookup
 | search Country!=Country_from_lookup
 |  table Cisco_ASA_user Country Country_from_lookup

[arsalanj]

I already tried that. it shows the same result.

user1.csv exists in Lookup table files:
/opt/splunk/etc/users/admin/search/lookups/user1.csv

admin
search
Private | Permissions Enabled Move | Delete

I don't know, should I add it somewhere else too?

This is the search log:

Unable to find filename property for lookup=user1.csv will attempt to use implicit filename.
04-19-2019 07:57:48.651 INFO CsvDataProvider - Assuming implicit lookup table with filename 'user1.csv'.
04-19-2019 07:57:48.651 INFO CsvDataProvider - Loading lookup table='user1.csv', file size=249, modtime=1555636649
04-19-2019 07:57:48.651 ERROR LookupDataProvider - Could not find all of the specified lookup fields in the lookup table.
04-19-2019 07:57:48.654 ERROR SearchPhaseGenerator - Fallback to two phase search failed:Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.
04-19-2019 07:57:48.655 ERROR SearchOrchestrator - Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.
04-19-2019 07:57:48.655 INFO SearchStatusEnforcer - Enforcing disk quota = 10485760000
04-19-2019 07:57:48.656 INFO DispatchStorageManager - Remote storage disabled for search artifacts.
04-19-2019 07:57:48.656 INFO DispatchManager - DispatchManager::dispatchHasFinished(id='1555685867.38290', username='admin')
04-19-2019 07:57:48.657 INFO UserManager - Unwound user context: admin -> NULL
04-19-2019 07:57:48.657 INFO UserManager - Unwound user context: admin -> NULL
04-19-2019 07:57:48.659 ERROR dispatchRunner - RunDispatch::runDispatchThread threw error: Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.