Splunk Search

How can I show all things in a lookup table that don't show up in the logs?

New Member

The lookup table connects A and B. Logs have B. I want to see the A that has not run according to logs B.

Tags (4)
0 Karma

Esteemed Legend

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
0 Karma

New Member

what is lookup mylookup supposed to do? Do I search for A in the lookup table? Please specify which B (logs or lookup table)

0 Karma

Esteemed Legend

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.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!