Someone kindly answered my question in the Splunk channel, but I thought I'd post the answer here since it didn't show up in my searches on the site.
I setup DBX to connect to a MySQL database that had a mapping between host IP addresses and the operating system on that host.
When I tried to the use the lookup table I got this error:
2014-04-10 11:07:35.376 dbx1929:ERROR:DatabaseLookupExecutor - Error while performing SplunkLookup DatabaseLookupExecutor: com.splunk.dbx.lookup.DBLookupException: Unsupported field SQL error while performing lookup: java.sql.SQLException: Cannot convert value "<redacted_host_ip>" to required datatype LONG com.splunk.dbx.lookup.DBLookupException: Unsupported field SQL error while performing lookup: java.sql.SQLException: Cannot convert value "<redacted_host_ip>" to required datatype LONG at com.splunk.dbx.lookup.DatabaseLookupExecutor.performSimpleLookup(DatabaseLookupExecutor.java:56) at com.splunk.dbx.lookup.DatabaseLookupExecutor.performLookup(DatabaseLookupExecutor.java:41) at com.splunk.runtime.SplunkLookup.invoke(SplunkLookup.java:26)
The search was:
sourcetype=proxy_logs | lookup KACE_machine_inventory host_ip OUTPUT os_name
The problem was the hostip field in the database was stored as an unsigned int, and Splunk was trying to convert it to a LONG and failing.
To get around this error, you need to either change the lookup to a SQL query that casts the hostip field as a varchar before bringing that data into splunk OR change the data type in the column you're using to something Splunk likes import.
What I did was create a second hostip column (hostipstring) in the database that was a varchar instead of an unsigned int, and excluded the hostip column from the lookup table.
hi, another way to do this is to use the SQL command CONVERT... e.g. SELECT CONVERT(varchar(10), host_ip),os_name FROM table_name
. That way you're not messing with the schema in KACE's database, which can lead to performance or upgrade problems.
Excellent point. I should have been clearer. The database I edited wasn't KACE's internal database. The database I changed was a separate one that's populated with some of the data in KACE.