Splunk Search

Why is my lookup not working, but inputlookup is working?

Path Finder

My lookup is named FutureHires and | inputlookup FutureHires shows that the lookup is being pulled in correctly. However when I try to join the lookup on PersonnelNumber (see below) which exists in my index and my lookup- I cannot pull any results. Can any of you help me?

index=hrdata source="$ldap$" Status=1 | lookup FutureHires PersonnelNumber OUTPUT "Hire Start Date" "Action Type" "Immediate Manager Email ID" as "Manager Email" "Site Name" as Site

This is the beginning of the search and it doesn't pull anything. All of the fields exist with that naming in the lookup file. Even if I don't specify output- I cannot pull anything

Also I checked permissions and all file locations and all of that looks correct. This lookup was working correctly until I uploaded an input file incorrectly and somehow broke it.

0 Karma

Esteemed Legend

According to your search, you MUST have the following:

Events in index=hrdata source="$ldap$" that have fields called Status which have values of 1.
Of those events, some (most, hopefully all) MUST have a field called PersonnelNumber.
The lookup called FutureHires MUST have a field called PersonnelNumber and at least some of those values must EXACTLY match the values of the same field in your events.
For starters, I would get rid of the OUTPUT part of your lookup and see what values do get added.

NOTE: case matters for both field names and field values or matches will NOT be found in the lookup.

0 Karma

SplunkTrust
SplunkTrust

First, try each of these , verify there is output. If not, then the field name is the problem... it is not called the same thing in each file/index

 index=hrdata source="$ldap$" Status=1 | head 3 | table PersonnelNumber 

...and...

| inputlookup FutureHires | head 3 | table  PersonnelNumber

Second, if both those produced output, then try this

 index=hrdata source="$ldap$" Status=1 
| table PersonnelNumber index
| inputlookup append=t FutureHires
| eval index=coalesce(index,"FutureHires")
| stats values(index) by PersonnelNumber
| where mvcount(index) >1

That will give you results of any records that are found on both. If it produces no output, then there are no matching records.


Third, if there ARE NO matching records, but there SHOULD BE matching records, then it is probably a format error. That may mean that one of them is numeric and the other is not, so try this...

 index=hrdata source="$ldap$" Status=1 
| table PersonnelNumber index
| inputlookup append=t FutureHires
| eval index=coalesce(index,"FutureHires")
| eval PersonnelNumber = tonumber(PersonnelNumber)
| stats values(index) by PersonnelNumber
| where mvcount(index) >1

Fourth, If that THIRD search finds matching records, then we have to figure out which file has the non-numeric version. We can use tonumber() or tostring() before the lookup to reformat the field from the base search to match the lookup. Either way, just so we know.

| inputlookup append=t FutureHires
| eval PersonnelNumberNum=tonumber(PersonnelNumber)
| eval PersonnelNumberString=tostring(PersonnelNumber)
| eval flag=case( PersonnelNumberNum=PersonnelNumber,"It's a Number",  PersonnelNumberString=PersonnelNumber,"It's a String",  true(), "It's a Unicorn")

Fifth, If that THIRD search finds NO matching records, then there are no actual matches between the files.

To verify this, let's try one more method for finding matches... which really shouldn't work, but I'm a belt-and-suspenders-and-safety-pins-and-duct-tape kind of guy.

 index=hrdata source="$ldap$" Status=1 
    [| inputlookup append=t FutureHires | table PersonnelNumber]
    | head 5

If that finds nothing, then again there is no overlap in the files.

Motivator

Check

transforms.conf

[FutureHires]
filename = FutureHires.csv

or from UI
Settings->lookups->lookup table files-> there should be entry for FutureHires
settings->lookups-> lookup definitions - > there should be entry for FutureHires

Search Query:
index=hrdata source="$ldap$" Status=1 | lookup FutureHires PersonnelNumber as PersonnelNumber OUTPUTNEW "Hire Start Date" "Action Type" "Immediate Manager Email ID" as "Manager Email" "Site Name" as Site

Assuming that PersonnelNumber field got extracted from the indexed data and PersonnelNumber field also available in lookup file

0 Karma

Path Finder

No this still doesn't let me pull in the data. Any other ideas?

0 Karma

Motivator

instead of source=$ldap$, can you please give actual source path with log file name.
for e.g. source=/opt/splunk/forwarder/var/log/splunk/splunkd.log

0 Karma