Like this (change joiner
to host
or whatever field both inputs share)
your search for A here | lookup mylookup | eval type=B | append [|inputlookup mylookup | eval type=A] | eventstats dc(type) AS numTypes by joiner | where numTypes=1 AND type=A
what is lookup mylookup supposed to do? Do I search for A in the lookup table? Please specify which B (logs or lookup table)
You said that "The lookup table connects A and B". Because you did not share the name of your table in your question and because it is important to the answer, I just made one up and called it mylookup
. Because you did not share the fields in your data and in your lookup, I made that up, too and called the key field joiner
.
You first need an inital dataset from your logs ( type=B
) which is what this part means (only you know what this is):
your search for A here | eval type=B
Next we must first enhance the first part of the search with the data from the lookup, which is done with this bit:
| lookup mylookup
Then, we need the "lookup A" ( type=A
) events from the lookup
table which are added with this part:
| append [|inputlookup mylookup | eval type=A]
Now we have all of our events and we can do the desired slicing and dicing. This part adds to each event a field called numTypes
to specify if this event has a partner even in the other dataset:
| eventstats dc(type) AS numTypes by joiner
Lastly we do the basic logic to get only the events you say you'd like:
| where numTypes=1 AND type=A
The numTypes=1
part says that this event does not have a partner event in the other dataset and the type=A
part says that this came from the A
dataset, which is the lookup table.