https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127640#M184372 <P>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.</P> Fri, 11 Apr 2014 15:24:58 GMT megancarney 2014-04-11T15:24:58Z https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127638#M184370 <P>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.</P> <P>I setup DBX to connect to a MySQL database that had a mapping between host IP addresses and the operating system on that host.</P> <P>When I tried to the use the lookup table I got this error:</P> <PRE><CODE>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 "&lt;redacted_host_ip&gt;" to required datatype LONG com.splunk.dbx.lookup.DBLookupException: Unsupported field SQL error while performing lookup: java.sql.SQLException: Cannot convert value "&lt;redacted_host_ip&gt;" 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) </CODE></PRE> <P>The search was:</P> <PRE><CODE>sourcetype=proxy_logs | lookup KACE_machine_inventory host_ip OUTPUT os_name </CODE></PRE> <P>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.</P> <P>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.</P> <P>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.</P> Thu, 10 Apr 2014 18:30:57 GMT https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127638#M184370 megancarney 2014-04-10T18:30:57Z https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127639#M184371 <P>hi, another way to do this is to use the SQL command CONVERT... e.g. <CODE>SELECT CONVERT(varchar(10), host_ip),os_name FROM table_name</CODE>. That way you're not messing with the schema in KACE's database, which can lead to performance or upgrade problems.</P> Mon, 28 Sep 2020 16:21:51 GMT https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127639#M184371 jcoates_splunk 2020-09-28T16:21:51Z https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127640#M184372 <P>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.</P> Fri, 11 Apr 2014 15:24:58 GMT https://community.splunk.com/t5/Splunk-Search/Conversion-errors-with-DatabaseLookupExecutor-Splunk-DBX/m-p/127640#M184372 megancarney 2014-04-11T15:24:58Z