Why is the output from my lookup command empty?

[iomega311]

I have created a lookup table that contains about 15 columns and about 100K rows that contains CMDB info.
I want to be able to use this lookup table to enrich queries that contain a host name.

For example, my lookup called "cmdb.csv" contains the following columns: hostname, domain, ip address, and owner.

I then have a query where I want to take the hostname that is returned and use it to query my lookup table to get back the corresponding domain, ip address, and owner.

Example query:

index=IDS sourcetype=symantec action=allowed classification=malware
| lookup cmdb.csv hostname OUTPUT domain ip owner
| table time hostname action classification domain ip owner

The query executes without any errors displayed, and returns information within the table for time, hostname, action, and classification... but domain, ip, and owner are each blank. (only the fields that are in the lookup table are blank)

When I (intentionally) misspell the lookup table name or any of the fields... it then throws errors that either the lookup table or fields don't exist. This tells me that I have the correct input file name and am referencing the correct field names.

When I do an "| inputlookup cmdb.csv | search hostname=hostname" the information is displayed that I am hoping to see without any blank fields.

So, I have a lookup table with the correct info in it, but when I attempt to use the "lookup" command to output corresponding fields, the information that is contained within the lookup table is blank.

Are there any suggestions on how I can get the information to return back when using the lookup command?

Thanks!

[lekanneer]

Been there done that with the lookups to hold CMDB info.

But I ended up to develop a solution.

If you're looking for an efficient and functional ServiceNow to Splunk integration (also CMDB) take a look at: https://www.thedutchdatadifference.nl/splunk-servicenow/

I created that solution and continuously adding new features. With this solution you can really query individual nodes but also all sorts of relationships.

[jboselly]

Did you ever find an answer to this? I'm having the same issue. I've confirmed that the fields I'm matching on are named identically, and there is overlapping data in those fields from the two sources. In the results however the output fields are all NULL.

I even built a test query like such:

| inputlookup kvstore_1
| eval index="one"
| append
[| inputlookup kvstore_2
| eval index="two"]
| table item index
| stats values(index) by item

And I do get "item" values which are associated to both "indexes", indicating the field name and data within the field do match. Example results:

item          index

device_a   one

           two

device_b   one

           two

Strangely enough if I tack on "|where mvcount(index) >1" similar to what @iomega311 suggests, I get zero results.

It's the strangest thing, I cross reference lookup tables all the time, this is the first time I've seen something like this.

[iomega311]

@jboselly I did end up getting it to work! For me, there were two issues going on... from what I can remember.

The first issue was that the lookups that were being affected were really large. I am in a clustered environment and the lookups were not replicating properly. Our admins got that part fixed.

If I am remembering correctly, the temporary fix was to add the optional argument of "local=true" to force the lookup to run on the search head instead of anything remote.

The other issue was that I didn't understand at that time that while it is said that "field NAMES are case sensitive, but field VALUES are not"... that is untrue for key/value pairs in lookup files by default.

To change that, you must create a lookup definition, then go into the advanced settings, and turn off case sensitivity.

Once we resolved those two issues, the lookups started returning the expected results.
Hopefully this helps!

[nickhills]

Sounds like @DalJeanis deserves the credit for at least half of the problem - please accept his answer as it was crucial in resolving this.

[DalJeanis · Accepted solution]

First, check your capitalization on your hostname. If they don't match exactly, then they won't be found.

[iomega311]

This was part of the issue that was causing lookups to not return anything. I created a lookup definition and removed the case sensitivity requirement.

[renjith_nair]

@iomega311 ,
Is it possible that the hostname returned from events does not have any match in lookup due to any possible reasons.

Try appending the result and check if you are getting any records

index=IDS sourcetype=symantec action=allowed classification=malware|eval source="event"
| append [|inputlookup cmdb.csv|eval source="csv"]
| stats count,values(source) as source by hostname |where mvcount(source) >1