How do I write a lookup after a lookup is search matches?

[HansNL]

Hi, am working on a lookup in a lookup.

i have the following search:

index=* source="*WinEventLog:Security" EventCode=4688
[| inputlookup attacktoolsh.csv WHERE discovery_or_attack=attack | stats values(filename) as
search | format]
| transaction host maxpause=10m
| where eventcount>=5
| fields - _raw closed_txn field_match_sum linecount
|table ComputerName, New_Process_Name, Process_Command_Line, _time, eventcount

This works fine, the lookup attactoolsh.csv has the tools, an i have a hit on a client.

now i would like to intergrate a second lookup file in the search that looks a file with a computername/username in it, that if the search hits on attacktoolsh.csv it looks in the second file and if a computer/user is in that file the search should not produce a notable. 

in short, computer A is running "nmap" this is allowed on computer A and Computer A is in the second file. Computer B is running "nmap" and is not allowed to run this, so produce a notable / warning.

anybody an idea how to intergrate this toghter.

Thanks.

[HansNL]

OK fixed the lookup command, apperantly the lookup table was corrupted during import.
But still, this did not do the trick, the output is still empty.

just to give some more input.
Attacktoolsh.csv has a content of:
"discovery_or_attack",filename,hash,platform
attack,nmap,,linux
attack,nc,,linux
attack,tcpdump,,linux
attack,putty,,windows

the secondlookup.csv  file has:

filename,computername,username
nmap,pc001,
putty,pc002,user1

the default search give an output of all so no problem there.

but what i am lookin for it that primary search only show computers that are not in the secondlookup.csv list.
so pc003 that runned putty should be displayed.

[marysan]

Hello. you need a common field between your first and primary search that computername can be a common field :
index=* source="*WinEventLog:Security" EventCode=4688
[| inputlookup attacktoolsh.csv WHERE discovery_or_attack=attack | stats values(filename) as
search | format]
| transaction host maxpause=10m
| where eventcount>=5
| fields - _raw closed_txn field_match_sum linecount
|table ComputerName, New_Process_Name, Process_Command_Line, _time, eventcount
|lookup  secondlookup.csv computername as ComputerName output filename
|search NOT filename=*
(for pc003, there is no filename field in your second lookup  )

[marysan]

@HansNL
did this work for you?

[marysan]

Hi
Assuming your second lookup is contaning 3 fields :filename,computername,username this query shoud be worked :

index=* source="*WinEventLog:Security" EventCode=4688 [| inputlookup attacktoolsh.csv WHERE discovery_or_attack=attack | stats values(filename) as filename| format]
|mvexpand filename
|lookup secondlookup.csv filename as filename output computername,username
|search computername=*
| transaction host maxpause=10m | where eventcount>=5 | fields - _raw closed_txn field_match_sum linecount |table ComputerName, New_Process_Name, Process_Command_Line, _time, eventcount

[HansNL]

Hi, and thanks for the fast response.
Sadly this does not work, i do get an " lookup command: could not construckt lookup.

have tried diverent settings but keeps up comming with this error after the mvexpand command.

any ideas