Splunk Search

Lookup Madness: How to match results to ONLY the names in a list I have using a lookup?

DesertSocBum
Explorer

I am trying to match results to ONLY the names in a list I have using a lookup.  I cant figure out for the life of me what I am doing wrong, been trying every single variated on lookup and inputlookup  I can think of or find online.  Anyone have any idea what I am doing wrong?

index=epp "Device Control" AND ("USB Storage Device" OR "Internal CD or DVD RW" OR "Internal Floppy Drive" OR "Zip Drive") AND NOT ("file read" OR "Connected" OR "unblocked" OR "Disconnected")
| rex field=_raw "epp\.tusimple\.ai\s\-\s(?<LogSource>.*)\s\-\s"
| rex field=_raw "\[Event\sName\]\s(?<EventAction>.*)\s\|\s\[Client\sComputer"
| rex field=_raw "\[Client\sComputer\]\s(?<Hostname>.*)\s\|\s\[IP\sAddress"
| rex field=_raw "\[IP\sAddress\]\s(?<IPAddress>.*)\s\|\s\[MAC\sAddress"
| rex field=_raw "\[MAC\sAddress\]\s(?<MACAddress>.*)\s\|\s\[Serial\sNumber"
| rex field=_raw "\[Serial\sNumber\](?<SerialNumber>.*)\|\s\[Client\sUser"
| rex field=_raw "\[Client\sUser\](?<UserName>.*)\|\s\[Device\sType"
| rex field=_raw "\[Device\sType\](?<DeviceType>.*)\|\s\[Device\]"
| rex field=_raw "\|\s\[Device\](?<DeviceDescription>.*)\|\s\[Device\sVID\]"
| rex field=_raw "\|\s\[Device\sSerial\](?<DeviceSerial>.*)\|\s\[EPP\sClient\sVersion\]"
| rex field=_raw "\[File\s\Name\](?<FileName>.*)\|\s\[File\sHash\]"
| rex field=_raw "\|\s\[File\sType\](?<FileType>.*)\|\s\[File\sSize\]"
| rex field=_raw "\|\s\[File\sSize\](?<FileSize>.*)\|\s\[Justification\]"
| rex field=_raw "\[Date\/Time\(Client\)\](?<EventTimeStamp>.*)\|\s\[Date\/Time\(Server\sUTC\)\]"

[ | inputlookup R_Emp.csv | table EventTimeStamp LogSource EventAction UserName FileName FileType FileSize Hostname IPAddress MACAddress SerialNumber DeviceType DeviceDescription DeviceSerial ]

Labels (1)
Tags (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

It sounds like you want a subsearch that incorporates the lookup file into the base search.

 

index=epp "Device Control" AND ("USB Storage Device" OR "Internal CD or DVD RW" OR "Internal Floppy Drive" OR "Zip Drive") AND NOT ("file read" OR "Connected" OR "unblocked" OR "Disconnected") 
```Read in the lookup file.```
```Be sure to change "foo" to the name of field in the lookup file.```
```Change '1000' to a number larger than the number of entries in the lookup.```
[ | inputlookup R_Emp.csv | return 1000 $foo ]
| rex field=_raw "epp\.tusimple\.ai\s\-\s(?<LogSource>.*)\s\-\s"
| rex field=_raw "\[Event\sName\]\s(?<EventAction>.*)\s\|\s\[Client\sComputer"
| rex field=_raw "\[Client\sComputer\]\s(?<Hostname>.*)\s\|\s\[IP\sAddress"
| rex field=_raw "\[IP\sAddress\]\s(?<IPAddress>.*)\s\|\s\[MAC\sAddress"
| rex field=_raw "\[MAC\sAddress\]\s(?<MACAddress>.*)\s\|\s\[Serial\sNumber"
| rex field=_raw "\[Serial\sNumber\](?<SerialNumber>.*)\|\s\[Client\sUser"
| rex field=_raw "\[Client\sUser\](?<UserName>.*)\|\s\[Device\sType"
| rex field=_raw "\[Device\sType\](?<DeviceType>.*)\|\s\[Device\]"
| rex field=_raw "\|\s\[Device\](?<DeviceDescription>.*)\|\s\[Device\sVID\]"
| rex field=_raw "\|\s\[Device\sSerial\](?<DeviceSerial>.*)\|\s\[EPP\sClient\sVersion\]"
| rex field=_raw "\[File\s\Name\](?<FileName>.*)\|\s\[File\sHash\]"
| rex field=_raw "\|\s\[File\sType\](?<FileType>.*)\|\s\[File\sSize\]"
| rex field=_raw "\|\s\[File\sSize\](?<FileSize>.*)\|\s\[Justification\]"
| rex field=_raw "\[Date\/Time\(Client\)\](?<EventTimeStamp>.*)\|\s\[Date\/Time\(Server\sUTC\)\]"

| table EventTimeStamp LogSource EventAction UserName FileName FileType FileSize Hostname IPAddress MACAddress SerialNumber DeviceType DeviceDescription DeviceSerial ]

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Have you tried it like this?

index=epp "Device Control" AND ("USB Storage Device" OR "Internal CD or DVD RW" OR "Internal Floppy Drive" OR "Zip Drive") AND NOT ("file read" OR "Connected" OR "unblocked" OR "Disconnected")
| rex field=_raw "epp\.tusimple\.ai\s\-\s(?<LogSource>.*)\s\-\s"
| rex field=_raw "\[Event\sName\]\s(?<EventAction>.*)\s\|\s\[Client\sComputer"
| rex field=_raw "\[Client\sComputer\]\s(?<Hostname>.*)\s\|\s\[IP\sAddress"
| rex field=_raw "\[IP\sAddress\]\s(?<IPAddress>.*)\s\|\s\[MAC\sAddress"
| rex field=_raw "\[MAC\sAddress\]\s(?<MACAddress>.*)\s\|\s\[Serial\sNumber"
| rex field=_raw "\[Serial\sNumber\](?<SerialNumber>.*)\|\s\[Client\sUser"
| rex field=_raw "\[Client\sUser\](?<UserName>.*)\|\s\[Device\sType"
| rex field=_raw "\[Device\sType\](?<DeviceType>.*)\|\s\[Device\]"
| rex field=_raw "\|\s\[Device\](?<DeviceDescription>.*)\|\s\[Device\sVID\]"
| rex field=_raw "\|\s\[Device\sSerial\](?<DeviceSerial>.*)\|\s\[EPP\sClient\sVersion\]"
| rex field=_raw "\[File\s\Name\](?<FileName>.*)\|\s\[File\sHash\]"
| rex field=_raw "\|\s\[File\sType\](?<FileType>.*)\|\s\[File\sSize\]"
| rex field=_raw "\|\s\[File\sSize\](?<FileSize>.*)\|\s\[Justification\]"
| rex field=_raw "\[Date\/Time\(Client\)\](?<EventTimeStamp>.*)\|\s\[Date\/Time\(Server\sUTC\)\]"
| search
[ | inputlookup R_Emp.csv | table EventTimeStamp LogSource EventAction UserName FileName FileType FileSize Hostname IPAddress MACAddress SerialNumber DeviceType DeviceDescription DeviceSerial | format ]
0 Karma

richgalloway
SplunkTrust
SplunkTrust

That won't do because the CSV only has one field in it.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Understand that subsearches (commands within square brackets) run before the main search and the results of the subsearch become part of the query string.  In this case, the inputlookup command reads the R_Emp.csv file and then the table command selects several fields (presumably existing in the lookup file).  That list of fields and their values then are added to the end of the last command before the subsearch - resulting in either unexpected results or an error.

Let's back up a little.  Before we can suggest a solution, we need to know a few things.  What fields are in the lookup file?  What should the final results look like?  What do you mean by "match results to ONLY the names in a list"? 

---
If this reply helps you, Karma would be appreciated.
0 Karma

DesertSocBum
Explorer

I might be going about this the wrong way then.  The original goal was to take the results of the main search with all the regex and only show events that match Employee names in the .csv.  The only thing in the list is rows of names.  Instead of typing out  something like AND ("Joe Dirt" OR "JANE DOE" OR etc. . .  before the regex.  So I only want hits on the names in the list and to ignore results for any other name in the index if that makes sense

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

It sounds like you want a subsearch that incorporates the lookup file into the base search.

 

index=epp "Device Control" AND ("USB Storage Device" OR "Internal CD or DVD RW" OR "Internal Floppy Drive" OR "Zip Drive") AND NOT ("file read" OR "Connected" OR "unblocked" OR "Disconnected") 
```Read in the lookup file.```
```Be sure to change "foo" to the name of field in the lookup file.```
```Change '1000' to a number larger than the number of entries in the lookup.```
[ | inputlookup R_Emp.csv | return 1000 $foo ]
| rex field=_raw "epp\.tusimple\.ai\s\-\s(?<LogSource>.*)\s\-\s"
| rex field=_raw "\[Event\sName\]\s(?<EventAction>.*)\s\|\s\[Client\sComputer"
| rex field=_raw "\[Client\sComputer\]\s(?<Hostname>.*)\s\|\s\[IP\sAddress"
| rex field=_raw "\[IP\sAddress\]\s(?<IPAddress>.*)\s\|\s\[MAC\sAddress"
| rex field=_raw "\[MAC\sAddress\]\s(?<MACAddress>.*)\s\|\s\[Serial\sNumber"
| rex field=_raw "\[Serial\sNumber\](?<SerialNumber>.*)\|\s\[Client\sUser"
| rex field=_raw "\[Client\sUser\](?<UserName>.*)\|\s\[Device\sType"
| rex field=_raw "\[Device\sType\](?<DeviceType>.*)\|\s\[Device\]"
| rex field=_raw "\|\s\[Device\](?<DeviceDescription>.*)\|\s\[Device\sVID\]"
| rex field=_raw "\|\s\[Device\sSerial\](?<DeviceSerial>.*)\|\s\[EPP\sClient\sVersion\]"
| rex field=_raw "\[File\s\Name\](?<FileName>.*)\|\s\[File\sHash\]"
| rex field=_raw "\|\s\[File\sType\](?<FileType>.*)\|\s\[File\sSize\]"
| rex field=_raw "\|\s\[File\sSize\](?<FileSize>.*)\|\s\[Justification\]"
| rex field=_raw "\[Date\/Time\(Client\)\](?<EventTimeStamp>.*)\|\s\[Date\/Time\(Server\sUTC\)\]"

| table EventTimeStamp LogSource EventAction UserName FileName FileType FileSize Hostname IPAddress MACAddress SerialNumber DeviceType DeviceDescription DeviceSerial ]

 

---
If this reply helps you, Karma would be appreciated.

DesertSocBum
Explorer

That did it, and I had return username in there at one point thru my 100 different variations, just didnt set the number.  Thank you! Now I know! 

Get Updates on the Splunk Community!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...