Compare results from lookup and search - with a twist!

[greekleo89]

Hi all,

New to splunk and i have seen that this has been asked many times but most of the results are based on matching one column from a search with another, my query is slightly different.

I have the following :

A search  that outputs the following in a table/columns/rows:

name, feed, alarm

attack, true, false

block, true, true

I also have a lookup table which has a predefined list of the above with the correct values, i.e

name,feed,alarm

attack, true,true 

.....and so on.

What i am looking for is for the search to compare its results with the lookup table and if there are any rows from the search that do not match those rows from the lookup to represent those to me as a result. I'm only interested in what isn't matching.

At the same time i would like to handle the possibility of there being an result from the search that doesn't exist in the lookup table.

Thank you in advance and i look forward to your answers :).

[PickleRick]

The typical - https://www.duanewaddle.com/proving-a-negative/

<your_base_search>
| eval locator=1
| append
 [ | inputlookup your_lookup
   | eval locator=2 ]
| stats sum(locator) as locator by name feed alarm
| where locator!=3
| eval whereis=if(locator=1,"search only","lookup only")

[greekleo89]

Hi and thank you for your feedback.

I have had a play and i am getting search_only as the results for every single entry, the input lookup doesnt seem to work as even if i give the inpulookup file a wrong name the results are the same.

[PickleRick]

Check whether your inputlookup command alone works as expected. You need to have the same set of fields as a result as your main search.

[greekleo89]

Hi,

The issue was that in the input the false/true statements were in capital.

However looking at the results (btw deduping on name) i can see the following:

name feed alarm locator whereis
Feed1 false false      2         lookup
Feed1 false true         1       search_only

However for OCD purposes 😛 i only want to see what is wrong, so for the above, both in the lookup and search only the feed is correct, but the alarm is not matching, how would i go about just showing me what is wrong?

Thanks

[PickleRick]

Just limit to one value.

| where locator=2

or

| where whereis="lookup"

or the other way around 🙂

[greekleo89]

Cool, that has worked, thank you for your help.

[greekleo89]

One more question:

In the search there is a field with a customer name - this field does not exist in the lookup table as the lookup tab le represents the default values .

The search represents the live values/config and the idea is to compare with the defaults and see what has been mis-configured.

On my output i'd like to show the customer name next to each row so that i know which customer has wrong configuration.

Thanks

[PickleRick]

Change

| stats sum(locator) as locator by name feed alarm

to

| stats sum(locator) as locator values(customer) as customer by name feed alarm

[greekleo89]

Just tried that and the field is blank although in the original search it is populated.  I'll keep digging, thank you