Splunk Enterprise Security

Why is the output from my lookup command empty?

iomega311
Explorer

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!

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

lekanneer
Loves-to-Learn Lots

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.

0 Karma

jboselly
Engager

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.

0 Karma

iomega311
Explorer

@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
Ultra Champion

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

If my comment helps, please give it a thumbs up!
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

iomega311
Explorer

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.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...