Splunk Search

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

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

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?

Tags (1)
0 Karma
1 Solution

arsalanj
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=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.

View solution in original post

0 Karma

arsalanj
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=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.

0 Karma

niketn
Legend

@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!!!"

somesoni2
Revered Legend

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

0 Karma

arsalanj
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, 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')

0 Karma

somesoni2
Revered Legend

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

arsalanj
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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
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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...