topic Re: Lookup Madness in Splunk Search

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

DesertSocBum — Fri, 06 Jan 2023 22:34:31 GMT

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?

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

Re: Lookup Madness

richgalloway — Fri, 06 Jan 2023 21:48:33 GMT

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"?

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

ITWhisperer — Fri, 06 Jan 2023 22:36:33 GMT

Have you tried it like this?

Re: Lookup Madness

DesertSocBum — Fri, 06 Jan 2023 23:51:10 GMT

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

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

richgalloway — Sat, 07 Jan 2023 00:52:48 GMT

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

Re: Lookup Madness

richgalloway — Sat, 07 Jan 2023 17:51:51 GMT

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 ]

Re: Lookup Madness

DesertSocBum — Sat, 07 Jan 2023 01:15:04 GMT

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!