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.
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 😞
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.
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.
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
No this still doesn't let me pull in the data. Any other ideas?
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