NetVoyant Duplicates 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 duplicate devices and make it easy to eliminate the duplicates.  I had built a fairly extensive method to do this using Perl script and batch files.  It suffered from all the problems that most NPC browser views suffer from.  So, ODBC is a better way to accomplish this and I'm officially retiring that script.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand Name="NVDuplicates" Property="False" SupportedViewTypes="Table"> <![CDATA[ select convert(concat( '<a target=\'_blank\' href=\'http://NVMC/nv/default.aspx?pg=', if(a.class_id=30000,1,if(a.class_id=4,20004,if(a.class_id=7,30104,11004))), '&Deviceid=', a.device_id, '\'>', a.dev_alias, '</a>' ) using utf8) as Alias, a.dev_alias as aliassort, if(a.expiration_started=0,'-',from_unixtime(a.expiration_started, '%Y/%m/%d %H:%i')) as 'ExpiringSince', if(a.unresponsive_started=0,'-',from_unixtime(a.unresponsive_started, '%Y/%m/%d %H:%i')) as 'UnresponsiveSince', b.smgr_host as Poller, concat( '<a target=\'_blank\' href=\'//', b.smgr_host, '/pollerwebservice/TopologyService.asmx/DeleteDevice?deviceNameOrIpAddress=', a.dev_name, '\'><img border=0 src=\'images/deleteX.gif\' /></a>' ) as deletethis from service_managers b, devices a inner join ( select dev_alias as dupon from devices where model_id<>0 group by dev_alias having (count(dev_alias)>1) ) dup on a.dev_alias = dup.dupon where b.smgr_id = a.dev_owner order by a.dev_alias; ]]> </SelectCommand> <odbcconnectionstring Name="NetVoyant">Driver={MySQL ODBC 5.1 Driver};Server=192.168.20.21;Port=3308;Option=3;Database=nms2;User=nmsuper;Password=redpoint;</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 Duplicates', '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 Duplicates'), (-1, 'N', 'DisplayColumns', 'deletethis;Alias;ExpiringSince;UnresponsiveSince;Poller'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Action,Device Alias,Expiring Since,Unresponsive Since, Poller'), (-1, 'N', 'ColumnWidths', ',,,,'), (-1, 'N', 'HorizontalAlignments', 'Center,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'aliassort'), (-1, 'N', 'SortDirection', 'Desc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'NVDuplicates'), (-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','NetVoyant'); UPDATE control_properties SET ControlID = ( select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;