Splunk Search

Database lookup returning no results

redc
Builder

I've set up a database lookup, but it's not returning any results; it should be returning 5 events. Here are the scenarios (and responses) I've tried (yes, some of them are pretty basic/dumb).


| lookup SMBkSalesCF

Error in 'lookup' command: Must specify one or more lookup fields.


| lookup SMBkSalesCF ValueCode

No results found.


| lookup SMBkSalesCF ValueCode ValueDesc

Error in 'lookup' command: All of the fields in the lookup table are specified as lookups, leaving no destination fields.


| lookup SMBkSalesCF ValueCode ValueDesc output MyFields

Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table.


| lookup SMBkSalesCF ValueCode ValueDesc output ValueCode

No results found.


| lookup SMBkSalesCF ValueCode output ValueCode

No results found.


| lookup SMBkSalesCF ValueCode ValueDesc output ValueCode ValueDesc

No results found.


I've built this file statically as a CSV, and it looks like this:

ValueCode,ValueDesc
A,1-10
B,11-20
C,21-50
D,51-500
E,501+

Practical use-case scenario:

| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueCode as MyField2 OUTPUT ValueDesc

Sample data return expected:

MyField1 | MyField2 | ValueDesc
1        | A        | 1-10
2        | C        | 21-50
3        | C        | 21-50
4        | B        | 11-20
5        | E        | 500+

Actual returned (okay, having problems getting the table for the actual results to display, but basically, MyField1 and MyField2 match the expected results, but ValueDesc is empty):

MyField1 | MyField2 | ValueDesc
1 | A |
2 | C |
3 | C |
4 | B |
5 | E |


Maybe I'm misunderstanding how database lookups work, but I'm expecting to get the textual value (ValueDesc) for the code value (ValueCode/MyField2) for each of those records. Instead, I get zilch.

What am I doing wrong?

Tags (1)
0 Karma
1 Solution

redc
Builder

D'oh! The problem was that MyField2 is actually storing the "ValueKey" table value, NOT the "ValueCode" table value. "ValueKey" is numeric (1, 2, 3, 4, 5) and "ValueCode" is alpha (A, B, C, D, E). So of course, there was no match!

I added "ValueKey" to the lookup and changed the command to:

| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueKey as MyField2 OUTPUT ValueDesc

And got the expected results set.

View solution in original post

0 Karma

redc
Builder

D'oh! The problem was that MyField2 is actually storing the "ValueKey" table value, NOT the "ValueCode" table value. "ValueKey" is numeric (1, 2, 3, 4, 5) and "ValueCode" is alpha (A, B, C, D, E). So of course, there was no match!

I added "ValueKey" to the lookup and changed the command to:

| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueKey as MyField2 OUTPUT ValueDesc

And got the expected results set.

0 Karma

redc
Builder

This is what the "Actual results returned" is supposed to be. For some reason, when I edit the question, it has this in there, but when you actually view the question, it's doing something completely different...

MyField1 | MyField2 | ValueDesc
1 | A |
2 | C |
3 | C |
4 | B |
5 | E |

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