Why is lookup command not giving result as expected?

[RanjiRaje]

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

[woodcock]

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:

|makeresults
| eval _raw="
Base Host Category
X device1 Lin
X device2 Win
X device3 Lin
M device2 Lin
M device14 Win
M device15 Win"
| multikv forceheader=1
| fields - _* linecount
| outputlookup eraseme.csv
| stats count BY Host
| rename Host AS hostname

| 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

[yuanliu]

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?

[ITWhisperer]

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

[RanjiRaje]

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

[ITWhisperer]

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?)?

[RanjiRaje]

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'

[ITWhisperer]

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

Please show your full SPL where this is failing.