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