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!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...