Using two lookups to drive a search

[kmattern]

I have two lookup tables.The first is a list of all New York Customers and looks like the first listing below. The second is a list of all their clients. I want to input from the NYCustomers list to get the corresponding list of clients from the AllClients.csv file for the associated customer and list the client's mac address and login name and other fields from the clientiis log. What is wrong with the search?

NYCustomers.csv
"CustName","custid"
"NYCStop1","NYC-001"
"NYCStop2","NYC-002"
"NYCStop3","NYC-003"
"NYCStop4","NYC-004"


AllClients.csv
"Customer","mac_addr","cs_client"
""NYCStop1"",001BD31AFEFF,"StJames_Test"
""NYCStop1"",005056A40019,EACLIENT
""NYCStop1"",00237DB6299E,jsmith
""NYCStop1"",2C768AE1D54E,araby02
""NYCStop1"",0006AA00B609,"Miltopetest-PC"


| inputlookup NYCustomers.csv | fields CustName | rename CustName as Customer
| lookup AllClients.csv Customer OUTPUT mac_addr
| search sourcetype="clientiis" earliest=-7d@d | table mac_addr, cs_client

[Ayn]

The problem with the search is that you're misunderstanding how the search pipeline works. Generally speaking, it works like a funnel - commands after the first one operate only on whatever events are available. So when you use search at the end of your example, the only thing it will be searching is the output from the previous commands, which is basically what you got from your inputlookup.

You need to rebuild your search a bit so that you start off with searching the events you need. If I understand you correctly, the way you identify your NY customers in your clientiis logs is through the MAC address? If so, the first idea that comes to mind is to use subsearches for creating filters that the search can use.

sourcetype="clientiis" earliest=-7d@d [| inputlookup NYCustomers.csv | lookup AllClients.csv Customer as CustName OUTPUT mac_addr]

What will happen is the subsearch will run first of all and then emit its output in a format that can be used as a filter string by the outer search. So the outer search will in the end look something like this:

sourcetype="clientiis" earliest=-7d@d ((mac_addr="addr1") OR (mac_addr="addr2") ... )

[kmattern]

I'll play with that but I have no idea how many mac addresses any given Customer will have. the count will be anywhere from five to 35, or so.

[linu1988]

lookup AllClients.csv is that the name of your lookup?? Did you define the lookup for the search in props and transform.conf?