Splunk Search

Lookup question

OgoNARA
Explorer

Hi,

 

I'm pretty new to Splunk and I have a simple question that maybe one of you guys could help me figure out. I have a search that I'm using to find the latest login events for a specific set of users. The problem is that there are about 130 users and I tried specifying the users in the search using (Account_Name=user1 OR Account_Name=user2 OR Account_Name=user3.......) I tried entering all 130 but it didn't work I noticed there was a limit after some point, and then I'd stop receiving results. So I did some research and I noticed people mentioned lookup files. So I created a CSV file with the list of actual users that I'd like to run a report on. how can I join the lookup file to the query so I'm only joining the values from the "UserID" field in my lookup table to the field "Account_Name" that comes with the windows event logs that I'm using to build the query. So far this is my query how could I use the lookup to assist to only filter the 130 users. 

 

index=wineventlog sourcetype=wineventlog EventCode=4624 Account_Name!=*$
| stats latest(_time) as last_login_time by Account_Name
| convert ctime(last_login_time) as "Last Login Time"
| rename Account_Name as "User"
| sort - last_login_time
| table User "Last Login Time"
Labels (3)
0 Karma
1 Solution

neerajs_81
Builder

Assuming your lookup file containing the user ids has the column name  "Account_Name"  which matches the field name in the windows events,  you can do something like this:

 

index=wineventlog sourcetype=wineventlog EventCode=4624 [|inputlookup my_lookup_file.csv | fields Account_Name]
| stats ......
.....
....

 

 
I verified it, it works in my env.  Just make sure the column_name / field_name in lookup is correct to based on what you want to filter on.    
PS: Hit "MARK as Answer" if this solves your query.

View solution in original post

neerajs_81
Builder

Assuming your lookup file containing the user ids has the column name  "Account_Name"  which matches the field name in the windows events,  you can do something like this:

 

index=wineventlog sourcetype=wineventlog EventCode=4624 [|inputlookup my_lookup_file.csv | fields Account_Name]
| stats ......
.....
....

 

 
I verified it, it works in my env.  Just make sure the column_name / field_name in lookup is correct to based on what you want to filter on.    
PS: Hit "MARK as Answer" if this solves your query.

Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...