Splunk Search

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

katzr
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

josephpe
Explorer

I have same issue, tried everything here and still another user on this instance cannot access the query or run the dashboard and it gives him no result.
Data is coming out without the | lookup, but when added with lookup, it does not work 😞

0 Karma

woodcock
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

DalJeanis
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.

sbbadri
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

katzr
Path Finder

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

0 Karma

sbbadri
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...