Archive

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

Path Finder

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.

CiscoASAuser,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=ciscoasa vendorclass="aaa/auth" CiscoASAmessageid=113039
| iplocation src
ip
| table CiscoASAuser , Country | rename CiscoASAuser 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 CiscoASAuser Country.

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

Any ideas of how to make this work?

Tags (1)
0 Karma
1 Solution

Path Finder

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=ciscoasa vendorclass="aaa/auth" CiscoASAmessageid=113039
| iplocation src
ip
| fields CiscoASAuser, Country
| lookup user1.csv CiscoASAuser OUTPUT Country as Countryfromlookup CiscoASAuser as user
| search Country!=Countryfromlookup | where CiscoASAuser=user and Country!=Countryfromlookup
| table CiscoASAuser Country Countryfromlookup

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.

View solution in original post

0 Karma

Path Finder

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=ciscoasa vendorclass="aaa/auth" CiscoASAmessageid=113039
| iplocation src
ip
| fields CiscoASAuser, Country
| lookup user1.csv CiscoASAuser OUTPUT Country as Countryfromlookup CiscoASAuser as user
| search Country!=Countryfromlookup | where CiscoASAuser=user and Country!=Countryfromlookup
| table CiscoASAuser Country Countryfromlookup

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.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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, CiscoASAuser, OUTPUT, Country, as, Countryfromlookup'. 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, CiscoASAuser, OUTPUT, Country, as, Countryfromlookup'. 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, CiscoASAuser, OUTPUT, Country, as, Countryfromlookup'. 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')

0 Karma

SplunkTrust
SplunkTrust

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

Path Finder

Thank you, but It didn't work.

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

0 Karma

SplunkTrust
SplunkTrust

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

Path Finder

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.

0 Karma