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;

Automating NFA Parser Reports

UPDATE: CA Support has endorsed the NAST tool as the replacement for the NFAParser.  I haven't tested it, but if it's like the other updated tools it will run faster.  The nice thing is that the syntax for running the NAST tool silently is the same as the NFAParser.  So, it doesn't take much to update this tool to use the new tool.

---

A while back I was tasked with making it possible to view NFA Parser output inside NPC.  It was actually easier than I thought.  I came up with something that isn't as optimal as I would like it (I'll explain why later), but it works for now.

The first thing you have to do is to download the NFA Parser which is part of CA's Support Tools 6 and copy it to each harvester. If you don't want to use all the tools, you can just download the parser and put it on each harvester.  The output of the parser is an HTML file which is ready to be published to a web service so you can link to it from NPC.  The easiest way to do this is to call the parser with a working directory of C:\inetpub\wwwroot on the harvester.  That way the output will be put in that directory, ready to be viewed in a browser.  However, every time you run the parser, the output file's name contains a date/time stamp, so that makes it a little difficult to link to.  The solution is to wrap it all in a batch file that clears the old output, calls the new output, then renames the new output to some static name.  Here's what that batch script would look like:
"D:\SupportTools6\NetQoS Utilities\ReporterAnalyzer\NFAParserv9.exe" batch %1 del nfaout.htm ren nfasum*.htm nfaout.htm

This could be tweaked a bit to keep the last X files using the following batch script:
del /q nfaout%2.htm for /L %%A in (1 1 %2) do ( ren nfaout%%A.htm nfaoutC.htm ren nfaoutB.htm nfaout%%A.htm ren nfaoutC.htm nfaoutB.htm ) "D:\SupportTools6\NetQoS Utilities\ReporterAnalyzer\NFAParserv9.exe" batch %1 ren nfasum*.htm nfaout1.htm

This second option moves the existing files up in a queue by renaming them with a higher name, except for the highest one that gets deleted.  So, if I created a scheduled task like this: C:\inetpub\wwwroot\nfa.bat 1 5, I would eventually end up with 5 files, each one representing one of the last 5 runs with nfaout1.htm being the most recent, each spanning 1 minute.  This second method is the option I'm using in production and it seems to work just fine.  In order to easily give access to the files, I create an HTML table with a column for the servers then a column for each of the retained reports.  Then I put in a row for each harvester.  I put that HTML in my custom content directory and load it into a browser view.

<head> <script> function whendiditrun(dayoffset,prepost){ var d = new Date(); d.setHours(prepost,0,0,0); d.setDate(d.getDate()-dayoffset); var seconds = new Date(); seconds = (seconds - d) / 1000; interval = Math.floor(seconds / 8640) / 10; if (interval >= 1) {return interval + " days ago";} interval = Math.floor(seconds / 360) / 10; if (interval >= 1) {return interval + " hours ago";} interval = Math.floor(seconds / 6) / 10; if (interval >= 1) {return interval + " minutes ago";} return Math.floor(seconds) + " seconds ago"; } </script> </head> The NFA parser interprets the raw NetFlow data coming into the harvester, generates a report, and runs every 12 hours on each harvester (local time to the harvester). The resulting report for each harvester shows what flows have been received in the 1 minute preceeding the execution of the script. The output of the last 5 reports can be displayed using the links below. <table border="1"> <tr><td rowspan="2"><center> Server</center> </td> <td colspan="5"><center> Last Run</center> </td></tr> <tr> <td><script>document.write(whendiditrun(0,0));</script></td> <td><script>document.write(whendiditrun(1,12));</script></td> <td><script>document.write(whendiditrun(1,0));</script></td> <td><script>document.write(whendiditrun(2,12));</script></td> <td><script>document.write(whendiditrun(2,0));</script></td></tr> <tr> <td>Harvester 1</td> <td><a href="http://harvester1/nfaout1.htm">View</a> </td> <td><a href="http://harvester1/nfaout2.htm">View</a> </td> <td><a href="http://harvester1/nfaout3.htm">View</a> </td> <td><a href="http://harvester1/nfaout4.htm">View</a> </td> <td><a href="http://harvester1/nfaout5.htm">View</a> </td> </tr> </table>
Obviously running the report more frequently and with a longer timespan will increase load on the harvester, so don't turn it on to run for 1 minute every minute.

Giving Existing Users Access to New Data Sources

UPDATE: Turns out I had released a more limited set of commands prevously.  Think of this method as version 2.  It's more complete and replaces the previous method.

---

There is a limitation in NPC that is a little annoying.  If you have a slew of users created in NPC (either using LDAP integration or just local product authentication) and you add a new data source, only nqadmin and nquser get access to the new data source.  By default, all other users don't get any access to the data source.  This doesn't mean they don't get access to the data, it just means that they can't log into the web GUI for that data source.  The 'proper' action would be to edit every single user and grant them either user, power user, or admin rights to the new data source.  With the advent of SSO and LDAP integration, this just won't work (especially if you have more than a couple dozen users).  And if you've made it a habit to only use the nqadmin account for root level tasks and you are using your own account (setup as an administrator), you would be able to add the data source but not be able to access it until you edit your own user account and give yourself access.  The silly thing is that any new users based on the nqadmin or nquser account would have access to the data source.  The problem is with existing users.

This is a difficult nut to crack; There are a couple features that could be built into NPC that would prevent the data source adder from having to touch every single user:

1. Give all users no access.  This is what happens now.
2. Give all users 'user' access to the data source.  This means that you would still have to log edit your own account and grant yourself admin access, not to mention any other accounts that need to be administrators on the new data source.
3. Give all users except the data source adder 'user' access.  This would allow you immediate admin access, but any other admins would still have to be edited manually.
4. Give all users the same access to the new data source as they have in NPC.  By this I mean that if a user is an administrator in NPC, they become an administrator for the new data source as well.  If they're a user in NPC, they become a user in NPC.  
5. Give all cloned users the same access as the account they were cloned from.  So, if a user was cloned from the nquser account (which inherits access to the new data source by default), then that user would also inherit access to the new data source.  
6. Setup product privilege sets and assign them to users.  I'd create three: all admin, all power user, and all user.  Then I could go to one place to make the change and all the users would be affected.

None of these features are in NPC or is there any indication they will be built into CAPC.  So, in the mean time, here's how you fix it.  The following query will essentially do option #4 above, giving all users the same access to all data sources as they have in NPC.  So, if they're a user in NPC, they'll have user access to all the data sources.  If they're an admin in NPC, they'll have admin access to all the data sources.

replace into data_source_rights select a.userid, b.sourceid, a.userlevel from user_definitions a, data_sources2 b where b.sourceid in (select distinct sourceid from data_source_rights) and a.removable='Y';

There are two ways that this query can be run: 1) scheduled task or 2) manual batch file after adding a data source.  Either way should be sufficient.  Option 2 would be more efficient since this only needs to be done after a new data source is added to NPC.

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;

iOS App Review: Remote

Apple
Summary - This app gets a ton of usage at our house.  In the last few years, Apple has been improving on their AirPlay feature and Remote reaps the benefits.  This app on my iPhone can connect to any of the iTunes libraries and AppleTVs on my network and control them.  We use this mainly as an additional remote control for our AppleTV.  I can swipe and tap to navigate around the AppleTV UI as well as browse through shared content from my desktop without interrupting the current content on the AppleTV.  When I find a movie, TV show, or music playlist I want to play, the Remote app starts it streaming to the AppleTV.  I can also use this to control music playing on my desktop.  So, if I walk into the other room, I can easily stop the music from playing without going back to my office.  We also use it from time to time to control the iTunes music playing in the nursery (since that PC is a headless PC; e.g. no monitor attached).

Pros - This one is definitely easy to setup.  Just enable the home sharing option by signing in with your Apple ID and you get full control of all the iTunes libraries and devices joined to the home share.  Apple's recent update also added a 'stay-connected' feature that keeps me connected for a few minutes after using the remote.  This means faster resume time if I was just using it a few minutes ago.  The app also supports AirPlay.  I discovered that from the app I can start music playing in my office, then also extend it to the speakers connected to my AppleTV.

Cons - I haven't really found any downsides to the app.  It's well designed and does exactly what I want it to do.