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!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...