Splunk Search

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

yuvsc
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

woodcock
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

yuvsc
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

woodcock
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
Get Updates on the Splunk Community!

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...