Monitoring Splunk

Unsupported field Unexpected error while performing lookup

Dan
Splunk Employee
Splunk Employee

I'm trying to do a temporal lookup using advanced SQL based on a primary key of source_ip and the _time field. This is for DHCP IP-to-hostname mappings (they change over time).

I'm not sure what's wrong here, but this doesn't seem to be working just yet.

First, the error:

2013-03-13 17:33:33.694 dbx9815:ERROR:DatabaseLookupExecutor - Error while performing SplunkLookup DatabaseLookupExecutor: com.splunk.dbx.lookup.DBLookupException: Unsupported field Unexpected error while performing lookup: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone <= character varyingcom.splunk.dbx.lookup.DBLookupException: Unsupported field Unexpected error while performing lookup: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone <= character varying
    at com.splunk.dbx.lookup.DatabaseLookupExecutor.performAdvancedLookup(DatabaseLookupExecutor.java:145)
    at com.splunk.dbx.lookup.DatabaseLookupExecutor.performLookup(DatabaseLookupExecutor.java:38)
    at com.splunk.runtime.SplunkLookup.invoke(SplunkLookup.java:26)
    at com.splunk.bridge.session.BridgeSession.call(BridgeSession.java:92)
    at com.splunk.bridge.session.BridgeSession.call(BridgeSession.java:30)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:679)

Second, the command:

sourcetype="pan_traffic" 172.16.40.131 | localop | lookup ip_to_host _time, src_ip

Third, the configurations (dbconf and transform):

[root@splunksearch002 local]# cat dblookup.conf
[default]

[ip_to_host]
advanced = 1
database = secopsdb001
fields = _time,src_ip,src_host,src_mac
input_fields = _time,src_ip
query = select src_host from splunk where src_ip=$src_ip$ AND _time <= $_time$ ORDER BY _time DESC limit 1
table = splunk

[root@splunksearch002 local]# cat transforms.conf
[ip_to_host]
external_cmd = dblookup.py ip_to_host
fields_list = _time,src_ip,src_host,src_mac

Fourth,
When I run this within the dbconnect app like so, it works:

select * from splunk where src_ip='172.16.40.131' AND _time <= '2013-03-13 15:55:36' ORDER BY _time DESC limit 1;

The error seems to be:

ERROR: operator does not exist: timestamp without time zone <= character varying

But why, this clearly works when run by hand.
Sadface.

0 Karma

Dan
Splunk Employee
Splunk Employee

In general Splunk only sends CSV data to the (database) lookup - so everything can only be treated as a string. DB Connect can do datatype conversion, but to do so it depends on either one of 2 conditions:

  • the database/JDBC driver supports parameter metadata. This means that it can analyze the SQL you're about to execute and can tell which datatype is expected for each placeholder beforehand. Some JDBC driver don't support this. Even if the JDBC driver supports it I actually wouldn't recommend relying on it (since in most cases it requires another roundtrip to the database for analyzing the SQL and therefore costs performance).
  • the datatype to be specified in the SQL template. DB connect will generically convert the values it retrieved from Splunk to the datatype specified in the parameter placeholder. The syntax for specifying those datatypes is as follows:

    $<fieldname>[:<DATATYPE>]$

The datatype portion is optional. If it's not supplied, then DB Connect will try to use parameter metadata from the JDBC API. If it's not possible it will fallback to simply supply String values.

In your use-case, I'd recommend specifying the params like this:

select src_host from splunk where src_ip=$src_ip$ AND _time <= $_time:TIMESTAMP$ ORDER BY _time DESC limit 1

If you're storing the _time as an integer, it would look like this:

select src_host from splunk where src_ip=$src_ip$ AND _time <= $_time:INTEGER$ ORDER BY _time DESC limit 1

The type names that can be used are listed here: http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html (we don't support all of those types, most of them work)

Another option would be to convert the value from a String/VARCHAR within the SQL statement. It's probably less efficient, though. I don't have a PSQL instance to try, so I can't guarantee that this works, but it would look something like:

select src_host from splunk where src_ip=$src_ip$ AND _time <= to_timestamp($_time$) ORDER BY _time DESC limit 1

Please let me know if that helps.

Dan
Splunk Employee
Splunk Employee

That worked! Thanks to Genti and Sigi for contributing this information.

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