RA Router List 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 view the router status for all routers monitored by ReporterAnalyzer (NFA).  The goal is to show a list of the routers along with the address from which RA is receiving Netflow, along with which harvester it's assigned to, how many interfaces have been discovered, how many are enabled, when the router last rebooted, when the last refresh was, and when it was last discovered.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand Name="RARouters" Property="False" SupportedViewTypes="Table"> <![CDATA[ SELECT SQL_CALC_FOUND_ROWS convert(concat( '<a target=\'_blank\' href=\'../npc/default.aspx?pg=98019&itemtypeid=3&itemsubtypeid=0&mn=4&search=', v.routerSysName, '\'>', v.routerSysName, '</a>' ) using utf8) as Router, v.ID, v.routerAddress, v.routerSysName, v.harvester, v.snmpId, v.snmpName, v.snmpVersion, v.snmpPort, v.lastPoll, tv.totalInterfaces, ev.enabledInterfaces, from_unixtime(v.lastReboot,'%Y/%m/%d %H:%i') as Last_Reboot, from_unixtime(v.lastRefresh,'%Y/%m/%d %H:%i') as Last_Refresh, from_unixtime(v.lastDiscovery,'%Y/%m/%d %H:%i') as Last_Discovery FROM routers_all_view v INNER JOIN routers_interfaces_allcount_view tv on v.ID = tv.ID LEFT JOIN routers_interfaces_enabledcount_view ev on v.ID = ev.ID LIMIT 200; ]]> </SelectCommand> <odbcconnectionstring Name="RAMC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.20.20;Port=3308;Option=3;Database=reporter;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), 'ReporterAnalyzer Router Status List', 'Graph', 'ReporterAnalyzer-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', 'ReporterAnalyzer Router Status List'), (-1, 'N', 'DisplayColumns', 'routerAddress;Router;harvester;totalInterfaces;enabledInterfaces;Last_Reboot;Last_Refresh;Last_Discovery'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Address,sysName,Harvester,Total Interfaces,Enabled Interfaces,Last Reboot,Last Refresh,Last Discovery'), (-1, 'N', 'ColumnWidths', ',,,,,,,'), (-1, 'N', 'HorizontalAlignments', 'Left,Left,Left,Left,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'routersysName'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'RARouters'), (-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','RAMC'); UPDATE control_properties SET ControlID = ( select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;