Splunk Search

Match data from items listed in column of csv file

Explorer

I have a .csv file with multiple columns. This is an auto-generated .csv file, and I only need to search against one specific column of data that contains host names.

I want to look through all of my data and limit results to only show events that match host names listed in the column of the .csv file.

Example of the csv file:

name,city,address,number,ip address,special hosts,time,country
bob,New York City,123 Main,243524,10.23.2.2,NYC02343,12:35,USA
sue,New York City,456 Main,891217,10.153.12.6,NYC08948,11:36,USA

I don't want or care about any data in this file other than data located in the "special hosts" column.
I want to see windows logon events for these hosts that will display data on the event, so long as it matches a hostname from this .csv in the "special hosts" column.

I have imported the .csv file as a lookup file into splunk. Something like pc_names.csv

Example:
sourcetype=wineventlog:security EventCode=4624 AccountName!="*$*" | ~~some command that filters the data based on hosts listed in the "special hosts" column in the .csv file~~ | stats count by AccountName srchostname srcip | sort - count

Any ideas on how I can filter based on that column only?

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Assuming you can (or already have) add your csv file as lookup table file. Try like this

sourcetype=wineventlog:security EventCode=4624 Account_Name!="*$*" [| inputlookup your_csv_file.csv | table "special hosts" | rename "special hosts" as host ]
| stats count by Account_Name src_hostname src_ip | sort - count

View solution in original post

SplunkTrust
SplunkTrust

Assuming you can (or already have) add your csv file as lookup table file. Try like this

sourcetype=wineventlog:security EventCode=4624 Account_Name!="*$*" [| inputlookup your_csv_file.csv | table "special hosts" | rename "special hosts" as host ]
| stats count by Account_Name src_hostname src_ip | sort - count

View solution in original post

Explorer

Thank you! I was missing the table part when trying to figure this out.

0 Karma