NetVoyant Device Inventory through ODBC

Continuing my effort to document the various ways I've used the ODBC connector for the NetQoS products, here's my next query and controls I've built and that I use in production.  Today's query comes from a need to export a list of all devices polled by NetVoyant to CSV.  This can usually be done by going to Inventory >> Devices and exporting to CSV from there.  However, that control has a limit of 5000 devices and we're monitoring more than that.  Luckily ODBC doesn't have that limitation.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand Name="DeviceInventory" Property="False" SupportedViewTypes="Table"> <![CDATA[ select convert(concat( '<a href=\'http://npc/npc/default.aspx?pg=', if(itemsubtypename='router','r',if(itemsubtypename='switch','sw',if(itemsubtypename='server','s','d'))), '&Deviceid=', itemid, '\'>', itemname, '</a>' ) using utf8) as dev_name, convert( v6_ntoa(address) using utf8) as address, convert( if(snmpversion=0,'Not SNMP Capable',snmpversion) using utf8) as snmpcapable, itemsubtypename, if( char_length(itemdesc)=0, null, if(char_length(itemdesc)<100,itemdesc, concat(left(itemdesc,100),'...')) ) as description from dst_device where sourceid=10; ]]> </SelectCommand> <odbcconnectionstring Name="NPC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.10.10;Port=3308;Option=3;Database=netqosportal;User=netqos;Password=netqos;</OdbcConnectionString>
To create the view, run the following SQL commands against the NPC server:

INSERT INTO controls SELECT if(min(id)-1>0,min(id)-1,29999), 'NetVoyant Devices in NPC', 'Graph', 'NetVoyant-ODBC', '/nqWidgets/Integration/wicTable.ascx', 1, 0x200000, 0, 0, 0, 1, '|nc|g|', '', 'Y' FROM controls WHERE datasourcetype=0x200000; INSERT INTO control_properties (ControlID, Editable, PropertyName, PropertyValue) VALUES (-1, 'N', 'Title', 'NetVoyant Devices in NPC'), (-1, 'N', 'DisplayColumns', 'dev_name;address;snmpcapable;itemsubtypename;itemdesc'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Device Name,Address,SNMP Version,Type,Description'), (-1, 'N', 'ColumnWidths', ',,,,'), (-1, 'N', 'HorizontalAlignments', 'Left,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'dev_name'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'DeviceInventory'), (-1, 'N', 'Integration.DataType', 'SelectCommand'), (-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), (-1, 'N', 'Integration.Parameter.TopN', '200'), (-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','NPC'); UPDATE control_properties SET ControlID = (select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;