topic Re: Why is lookup command not giving result as expected? in Splunk Search

Why is lookup command not giving result as expected?

RanjiRaje — Thu, 13 Apr 2023 16:25:20 GMT

Hi All, I am facing some issue in using lookup command. Need your suggestions here please..

I have a lookup file as below: In that I have same host under different base.

Base	Host	Category
X	device1	Lin
X	device2	Win
X	device3	Lin
M	device2	Lin
M	device14	Win
M	device15	Win

I need to compare the hosts (from Base 'M') with hostname reporting under particular index and need to get the list of matching hosts.

Query:
index=indexA
| lookup lookupfilename Host as hostname OUTPUTNEW Base,Category
| fields hostname,Base,Category
| stats count by hostname,Base,Category
| where Base="M"

As per my lookup file, I should get output as below (considering device2 & device14 available in splunk index)

hostname	Base	Category
device2	M	Lin
device14	M	Win

But I am getting 2 entries under device2 as below (entry under category "Win" is incorrect) :

hostname	Base	Category
device2	M	Win
device2	M	Lin
device14	M	Win

Please help me on the query that I have framed. thanks in advance

Re: Why is lookup command not giving result as expected?

ITWhisperer — Thu, 13 Apr 2023 16:28:40 GMT

| eval Base="M" | lookup hosts.csv Host as hostname Base OUTPUTNEW Category | fields hostname,Base,Category | stats count by hostname,Base,Category

Re: Why is lookup command not giving result as expected?

RanjiRaje — Thu, 13 Apr 2023 16:39:22 GMT

Hi , thanks for your suggestion. I tried the way u said. but it is also not providing the result as expected

Re: Why is lookup command not giving result as expected?

ITWhisperer — Thu, 13 Apr 2023 16:48:30 GMT

OK that seems unlikely as setting the Base to "M" and including in the lookup would mean that only rows with Base="M" and Host=hostname would be returned.

What other rows are you getting back from the lookup (that you weren't expecting?)?

Re: Why is lookup command not giving result as expected?

RanjiRaje — Thu, 13 Apr 2023 17:03:53 GMT

The other Base which has the same hostname is getting included in my result which is incorrect.

in my example:

Host "device2 " is present in both the Base (M, X). As per my where condition, I should get only Base 'M' with Category 'Lin'

But I am getting 2 entries for device2 (by referring both the Base) with Category 'Lin' and 'Win'

Re: Why is lookup command not giving result as expected?

yuanliu — Thu, 13 Apr 2023 20:21:32 GMT

Is it possible that your lookup table has data entry error? Use this as a test:

| lookup lookupfilename | stats count by Host Base Category | rename Host as hostname | where Base == "M" AND hostname == "device2"

Does this give you the desired results?

Re: Why is lookup command not giving result as expected?

ITWhisperer — Thu, 13 Apr 2023 22:41:34 GMT

Is it possible that Base is coming from your events in the index?

Please show your full SPL where this is failing.

Re: Why is lookup command not giving result as expected?

woodcock — Sun, 16 Apr 2023 02:11:03 GMT

The problem is that your lookup will create TWO multivalued fields: "Base" and "Category" and unless you entangle them row-wise they association will be lost. You can do this 2 ways. You can merge the 2 fields into a single field called "Base_and_Category" and then do filter/split/mevexpand, but this is probably more trouble than it is worth. The other option is to lookup each (potentially) multivalue field separately and filter/stats/mvexpand before doing the other field. Try this:

| rename COMMENT AS "Everything above is setup; everything below is your answer"

| lookup eraseme.csv Host AS hostname OUTPUT Base
| stats count BY hostname Base
| search Base="M"
| lookup eraseme.csv Host AS hostname Base OUTPUT Category