Some database columns missing from dblookup

[FloydATC]

I have a fairly complicated SQL statement to look up switchport information based on MAC address. The database is maintained by other systems that scan the network on regular intervals.

When executed manually with a known MAC address, it returns a proper result:

+--------------+------------+--------------+-----------+--------+-------+------+
| switch       | switchport | description  | status    | duplex | speed | vlan |
+--------------+------------+--------------+-----------+--------+-------+------+
| RAFJOM-SW-06 | Fa0/6      | SikretKlient | connected | a-full | a-100 |   56 | 
+--------------+------------+--------------+-----------+--------+-------+------+
1 row in set (0.00 sec)

However, when I try using the dblookup in a search, the fields "switch" and "switchport" are always empty:

* | head 1 | eval mac="00:80:64:4e:95:10" | lookup mac_to_switchport mac OUTPUT switch, switchport, description, status, duplex, speed, vlan | table mac, switch, switchport, description, status, duplex, speed, vlan

mac             switch  switchport  description     status      duplex  speed   vlan
00:80:64:4e:95:10                   SikretKlient    connected   a-full  a-100   56 

I've tried hundreds of MAC addresses and the results all look fine except for the two fields that always come up empty in Splunk. Notice that the presence of the "description", "status" etc. means that a valid record was found and I have confirmed that no record exists with a blank or NULL "switchport" column.

mysql> select * from scanned_switchports where name = "";
Empty set (0.09 sec)

mysql> select * from scanned_switchports where name IS NULL;
Empty set (0.00 sec)

I created the lookup via Splunk Web but here's what it looks like in "/opt/splunk/etc/apps/dbx/local/dblookup.conf":

[mac_to_switchport]
advanced = 1
database = Atlas
fields = switch,switchport,description,status,duplex,speed,vlan
input_fields = mac
query = SELECT\
  hosts.name AS switch,\
  scanned_switchports.name AS switchport,\
  scanned_switchports.description AS description,\
  scanned_switchports.status AS status,\
  scanned_switchports.duplex AS duplex,\
  scanned_switchports.speed AS speed,\
  IF (scanned_switchportvlans.tagged=0, scanned_switchportvlans.vlanid, NULL) AS vlan\
FROM scanned_macs\
LEFT JOIN scanned_switchportmacs ON (scanned_switchportmacs.macid = scanned_macs.id)\
LEFT JOIN scanned_switchports ON (scanned_switchports.id = scanned_switchportmacs.switchportid)\
LEFT JOIN scanned_switchportvlans ON (scanned_switchportvlans.switchportid = scanned_switchportmacs.switchportid)\
LEFT JOIN hosts ON (hosts.id = scanned_switchports.hostid)\
WHERE scanned_macs.mac LIKE $mac$\
AND scanned_switchports.description NOT LIKE "qinq %"\
HAVING vlan > 0\
ORDER BY scanned_switchports.updated DESC\
LIMIT 1

I'm running Splunk 6.1.1 build 207789 and dbx 1.14 on CentOS/Linux. The database connector is mysql-connector-java-5.1.30-bin.jar

In case the names "switch" or "switchport" were causing problems, I tried changing them to "foo" and "bar" in both the dblookup and the search string but this did not fix the problem.

...any ideas on how to proceed with troubleshooting this?

[FloydATC]

Downgrading mysql-connector from 5.1.x to 3.14 fixes the problem, although I'm sure it introduces problems elsewhere. The reason is explained in the connector 5.1.x README file; Oracle has changed .getColumnName() to always return the original column name and added a new method .getColumnLabel() to get the actual name. I guess Splunk must change dbx to use the new method if available.

[FloydATC]

Investigating further, I have tried executing the same SQL query via the "Database Query" interface in Splunk DB Connect, and I discovered what looks like the cause of the problem.

SELECT
  hosts.name AS switch,
  scanned_switchports.name AS switchport,
  scanned_switchports.description AS description,
  scanned_switchports.status AS status,
  scanned_switchports.duplex AS duplex,
  scanned_switchports.speed AS speed,
  IF (scanned_switchportvlans.tagged=0, scanned_switchportvlans.vlanid, NULL) AS vlan
FROM scanned_macs
LEFT JOIN scanned_switchportmacs ON (scanned_switchportmacs.macid = scanned_macs.id)
LEFT JOIN scanned_switchports ON (scanned_switchports.id = scanned_switchportmacs.switchportid)
LEFT JOIN scanned_switchportvlans ON (scanned_switchportvlans.switchportid = scanned_switchportmacs.switchportid)
LEFT JOIN hosts ON (hosts.id = scanned_switchports.hostid)
WHERE scanned_macs.mac LIKE "00:80:64:4e:95:10"
AND scanned_switchports.description NOT LIKE "qinq %"
HAVING vlan > 0
ORDER BY scanned_switchports.updated DESC
LIMIT 1


Database Results
    name            description     status      duplex  speed   vlan
1   RAFJOM-SW-06    SikretKlient    connected   a-full  a-100   56

Notice that the "AS switch" and "AS switchport" expressions are ignored and the switch name incorrectly appears as "name". I tried changing the other column names and it turns out that the "AS vlan" expression is the only one that has any effect. Is this a known problem in dbx or the mysql connector? How do I get around it? Changing the database structure is out of the question.