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.

iOS App Review: Latitude

Google Latitude - Google, Inc.
Summary - This handy little app scares people who are paranoid about Google being their big brother.  I look at Google in this way: "If Google wanted any of my information, they'd get it.  So why go to the effort of hiding it?"  Latitude tracks my current GPS position and uploads it regularly to the Google cloud.  Within the app, I have a few defined friends that also use Latitude.  I choose to share my current location with them.  This means I can always check where my wife, my Dad, my brother, and my best friend are.  Hopefully the service won't be turned off without the functionality being moved into Google+.

Pros - Easy to setup, easy to add friends, quick to launch, and I always know if my parents are on their way over for a surprise visit to see the grandkids.  I can also set an option (that's disabled by default) to keep a log of my locations (instead of just my current location).  This could be useful for parents trying to keep an eye on their kids or for catching the crooks that steal your phone.

Cons - Google knows where you've been.  Not many people use it.  It's not yet integrated with Google+ (maybe someday).

iOS App Review: Grabatron

Grabatron - Future Games of London

Summary - This game has kept itself on my first page of apps for several months now.  It's a simple game where you fly a UFO by tilting the iPhone.  Tap the screen and a giant claw descends.  The goal is to grab stuff with the claw and destroy it.  Simple gamification techniques make the game easy to put down and resume later while still feeling like I've made progress.  The whole thing has a fun 1950s Aliens Attack! feel to the graphics and theme.

Pros

• Easy to learn the mechanics of the game
• In app upgrades can be bought with real money or crystals earned in the game
• Crystals can also be earned by watching neatly tucked away ads
• The object of the game is destruction!

Cons

• Loads slowly on my iPhone4.  Once loaded, performance is fine
• Music and sound effects were annoying at first.  Easy to disable though

iOS App Review: Tower Madness

TowerMadness - Limbic Software

Summary - One of the most fun games I've played.  It's not that much different than other tower defense games, but consistent new maps and tower upgrades keep me coming back and playing the same parts of the game over and over.

Pros

• New maps released somehow right after I get tired of the existing maps
• New weapon upgrades released somehow right after I get tired of the existing weapon upgrades
• Good graphics
• Easy to learn
• Endless mode allows for longer play with more money and more aliens to kill
• Pinch/spread zoom in/out seamlessly integrates with 3D environment
• 1x/2x/4x play modes so you can play faster

Cons

• No 'upgrade all the way' or 'upgrade as much as I can afford' option (instead you have to go through each upgrade manually
• No option in endless mode to send 10/50/100 waves at once (instead you have to tap the single button 10/50/100 times)
• More maps can be bought as in app purchases - that's how they make money
• More weapons can be bought as in app purchases
• 8x play mode would be nice
• My iPhone 4 slows down when more than 40 waves are present on the playing field and in 4x mode.  Doesn't seem to affect score
• One tower has an upgrade that turns it into a Tesla Coil.  While it's a great weapon, it doesn't work against airborne enemies.  Since it's all science fiction anyway, I would prefer that this upgrade gave the tower the ability to engage airborne enemies, much like the famed Tesla Death Ray

SA Collector Feed List through ODBC

Continuing my effort to document the various ways I've used the ODBC connector for the NetQoS products, I'm going to post a couple of the queries and controls I've built and that I use in production.  Today's query comes from a need to review all the collector feeds for all the collectors on two different master consoles.  Collector feeds are configured for each collection NIC on standard collectors.  I haven't run this query on a MC with a MTP connected, but it should result in the same thing.
The query comes from three different tables and results in a list of feeds and options to edit each collector.  Unfortunately, the functionality to directly modify the feed properties within the GUI isn't present yet, but something like it should be coming soon.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand name="SACollectors" property="False" supportedviewtypes="Table"> <![CDATA[ select convert(concat( '<a target=\'_blank\' href=\'//', c.PropValue, '/SuperAgent/Admin/', 'editAdminCollectors.aspx?CollID=', b.probe_id, '\'>', a.probe_desc, '</a>' ) using utf8) as Collector_Name, b.probe_id as Collector_ID, b.feed_index as Feed_Index, b.probe_id * b.feed_index as Sorter, convert(inet_ntoa(a.address) using utf8)  as Mgmt, convert(inet_ntoa(a.monitor_address)  using utf8) as Monitor1, convert(inet_ntoa(a.monitor_address2)  using utf8) as Monitor2, a.ip_addresses as IP_Address_List, b.feed_name as Feed_Name, convert(inet_ntoa(b.listen_ip) using utf8)  as Listen_IP, b.options as Options from probes a, probe_feeds b, performance_center_properties c where b.inactive=0 and a.probe_id=b.probe_id and c.propname='DS.ConsoleHost'; ]]> </SelectCommand> <odbcconnectionstring Name="SAMC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.23.67;Port=3308;Option=3;Database=super;User=super;Password=nqsuper;</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), 'SuperAgent Collector Feeds', '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', 'SuperAgent Collector Feeds'), (-1, 'N', 'DisplayColumns', 'Collector_Name;Collector_ID;Feed_Index;Mgmt;Monitor1;Monitor2;IP_Address_List;Feed_Name;Listen_IP;Options'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string,string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Collector Name,Collector ID,Feed Index,Management IP,Monitor 1 IP,Monitor 2 IP,IP Address List,Feed Name,Listen IP,Options'), (-1, 'N', 'ColumnWidths', ',,,,,,,,,'), (-1, 'N', 'HorizontalAlignments', 'Left,Left,Left,Left,Left,Left,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'Sorter'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'SACollectors'), (-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','SAMC'); UPDATE control_properties SET ControlID = ( select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;

ODBC Connector for NPC

Most of the built in reporting for NPC is great and can be modified by the administrator to get things to look exactly the way they need to be. However, sometimes the built in reports just don't do it. Show my side note.

Software should come out of the box with the best settings enabled but be customizable to the point that it can field even the strangest requests.  NPC is close to that.  CAPC promises to lock down the customizability in an effort to ensure that people are looking at things the right way.  The problem is that people don't usually want to change the way they look at things.  CAPC will fail at this unless it becomes much more flexible.  Ok, stepping off my soap box and returning to topic.

Specifically, the data that we need to pull is not necessarily available either because the views themselves are limited or because the queries are not the kind of reports that are normally run in NPC or even in the data source. In this case, we have to turn to the ODBC Connector. Not much is known about this, but I was recently introduced to it as a way of bringing external data into NPC. Turns out it's very flexibly and fairly easy to work with.

The ODBC connector allows you to source a table view in NPC with any MySQL query on any server your  connector server has MySQL access to.  So, if you can run the query manually, you can use the query as the source of a view on an NPC page.  Pretty cool huh?  I've built a couple of queries and views into my own NPC.  Now instead of having scheduled tasks that run queries nightly and display that (sometimes stale) output in a browser view, we have dynamic live data in paginated form.  Here's how you do it.

Standard warnings apply: you'll break your stuff, I won't help you fix it, neither will CA support.  Don't do this.  For more details look at the footer of this page.  Make a backup first.

Install the ODBC connector.  Usually this is done on the NPC server, but there's no reason it couldn't be done on one of the data sources or even on a standalone server.  Warning: all NetQoS services on the target server will be stopped as part of the installation, so you'll need to do a reboot at the end of the installation.  Don't worry, you're prompted to do it.

After you get it installed, you'll need to hack NPC a little so you can add it as a data source.  Run the following command on the NPC server.
mysql -P 3308 -D netqosportal -e "REPLACE INTO data_source_types2 (SourceType, RelatedSourceTypes, SourceName, ServiceURL, RequiredVersion, MaxInstances, pollinterfacetype) VALUES (0x200000, 0x200000, 'ODBC Connector','{DS.Protocol}://{DS.Host}:{DS.Port}/Odbc/OdbcService.asmx',16777216, 10, 2);"

Now add the ODBC connector as a data source in NPC just like you'd add any other data source.  So far, so good.  Now we need to design our query, build the view that will show the data, and put that view on the page.

I use HeidiSQL because it's easy to look around at things and involves less typing than the command line.  Use whatever you want. Either way, design your query such that you can run it and the output looks the way you would want it to look in NPC.  Don't use spaces in the field names, we will replace the field names later with more readable names.  For example, here's an easy query: select 'Hello World!' as title, 'Nice' as Column2, '1' as Column3;.  This results in one row with three columns.  This query needs to be added to the configuration XML in D:\netqos\portal\integration\ODBC\configuration.xml.  You add it between the <SelectCommands> tags and using a <SelectCommand> tag like this (the tags are probably case sensitive, do it like the ones already there):
<selectcommands> <selectcommand Name="HelloWorld" Property="False" SupportedViewTypes="Table"> <![CDATA[      select 'Hello World!' as title, 'Nice' as Column2, '1' as Column3; ]]> </SelectCommand> </SelectCommands>
Once that's in there, you'll also need to specify a portion of the configuration that will tell the ODBC connector which server to connect to and against which to run the query.  You add it between the <OdbcConnectionStrings> tags with an <OdbcConnectionString> tag liek this:
<odbcconnectionstring Name="NetVoyantMC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.1.67;Port=3308;Database=nms2;User=nmsuper;Password=redpoint;</OdbcConnectionString>
This should all be self explanatory.  Save the XML configuration and we should be ready to move on to the next step: building the view.

Even though we might be pulling data from the NetVoyant database, we can't use the NetVoyant view wizard to create the views for the ODBC connector.  Luckily, it only takes a few database inserts to create a new view.  Be careful, as removing a view is a bit more complicated than adding a view.  To add a view, simply use the following SQL script.  The comments in the script should be descriptive enough.  Each comment is about the line that follows it.  They key is to make sure the Integration.Parameter.SelectCommandName is the same as the name specified in the ODBC configuration.xml (in this case 'HelloWorld') and that the Integration.Parameter.OdbcConnectionStringName is set to the name specified in the config.xml (in this case 'NetVoyantMC').

INSERT INTO controls
SELECT
-- sets the controlid to the highest unused id below 30000
if(min(id)-1>0,min(id)-1,29999), 
'Hello World', -- name of the view
'Graph',
'ODBC Connector Views', -- category of the view
'/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
-- name of the view
(-1, 'N', 'Title', 'Hello World'), 
-- sql column names separated by semicolon
(-1, 'N', 'DisplayColumns', 'title;column2;column3'), 
-- format of the columns (normally string)
(-1, 'N', 'DisplayFormats', 'string,string,string'), 
-- nice column names
(-1, 'N', 'DisplayNames', 'Message','Column 1','Column 2'),
-- column widths (not sure on units)
(-1, 'N', 'ColumnWidths', ',,'), 
-- alignment of columns
(-1, 'N', 'HorizontalAlignments', 'Left,Center,Right'), 
-- sql column name to sort by
(-1, 'N', 'SortField', 'title'), 
-- desc or asc
(-1, 'N', 'SortDirection', 'Desc'), 
-- name of selectcommand in config.xml (which query to run)
(-1, 'N', 'Integration.Parameter.SelectCommandName', 'HelloWorld'), 
-- don't change this
(-1, 'N', 'Integration.DataType', 'SelectCommand'), 
-- don't change this
(-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), 
-- max 200
(-1, 'N', 'Integration.Parameter.TopN', '200'), 
-- name of odbcconnectionstring (which server to query)
(-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','NetVoyantMC'); 

UPDATE control_properties 
SET ControlID = (select min(id) from controls where datasourcetype=0x200000)
WHERE ControlID = -1;

You can have selectcommands and odbcconnectionstrings in yours configuration.xml even if they're not used.  

After using the SQL commands above to create the view, simply edit a page and look in the corresponding view category for your new view.  Add it to the page and it should run the query when the page is refreshed, showing updated data.

If you've added the view but need to modify the select query in the config.xml, you'll need to update the corresponding properties to display them.  Also, you can use the 'concat()' and 'convert() using utf8' functions in a query to put advanced html into a particular column (like a link to a certain item's details page). Something like this would give you a row for each collector with links to edit each collector (I'll post the full configuration in my next post):
select convert(concat('<a href=\'http://superagentmc/SuperAgent/Admin/editAdminCollectors.aspx?CollId=',probe_id,'\'>Edit</a>') using utf8) as EditCollectorLink from probes;

In the next few posts, I'll post some queries (like the one above) and views that I've built to do some basic reporting in NPC from SuperAgent and NetVoyant.

iOS App Review: Flashlight by Rik

I've been asked before what apps I have installed on my iPhone.  I figured I could detail which ones I use and why through my blog.  This will give me room to put my reasons for/against a certain app.  I'll try to include my overview, pros, and cons.  Here goes nothing...

Flashlight by Rik - Henri Asseily
Summary - This turns the iPhone's camera's flash into a flashlight.  Instead of flashing on when taking a picture, this app turns on the flash continuously.
Pros - Tons of options including an option to darken the screen while in use.  When in darkened mode it shows your battery usage so you can keep an eye on things.  It includes standard flashlight functions like auto SOS message through Morse code, manual Morse code, and strobe (more just for fun).  There's also an option to control the intensity, so it can be turned down to make your battery last longer.  It's also free.
Cons - Like any other flashlight app, it will consume battery while it's on.  You can't use other apps and keep the flashlight on.  This is not unique to this flashlight app and has to do more with iOS' so called 'multitasking'.

Automating iTunes

In order to train my kids to go to sleep with ambient noise and without I wanted to setup a way to have music play in their room on some nights and no music playing on other nights.  Given that the holiday season is here, I decided to go ahead and document how I did this.

First of all, I put a small form factor PC in their room on one of the bookshelves behind the books.  I hooked up some cheap speakers to it and installed iTunes.  This was enough to get started.  Since iTunes on that PC was joined to my home share, it was easy to control the music using my iPhone.  There were several problems with this: 1) I had to remember to turn it off or it would be going all night, 2) I had to carve out a section of my iTunes library that would not wake the boys up, and 3) I sometimes had to turn on the music while I was holding a sleeping baby (not easy).  So, I decided to automate things.  A quick Google search led me to Maximize Software's collection of Windows iTunes scripts.  After unpacking the scripts and familiarizing myself with the various options, I started building a batch file that would automate things.

Let's define the objectives:

1. I want the music to come on and turn off by itself - this should be pretty easy to do using a scheduled task.
2. I don't want the music to start off at full blast in case we've put the boys down early.    
3. I want the music to play whenever we put the boys to sleep, which could be as early as 7pm but as late as 9pm.
4. I want to be able to rotate playlists so it's not the same thing every night.
5. I want to be able to kick off the music any time.
6. I want to be able to manually override either the current song or the current volume level.

So, the first thing I did was copy all the music I wanted to play into that computer's iTunes library.  I wanted to be able to use my computer independently of the nursery music, so that meant all the music needed to be local.  The next thing I did was setup a couple of playlists.  The first is called AllMusic and is a smart list with the following criteria: Media Kind = Music & Playlist is not 'Waves' & Playlist is not 'Christmas' & Location = 'on this computer'.  Waves and Christmas are the other two playlists.  Christmas is also a smart play list with the following criteria: Media Kind = Music & Genre = Christmas & Location = 'on this computer'.  Waves is a manual list that contains a single 1 hour 8 minute MP3 of waves crashing on a beach.  The idea behind the playlists is that each playlist gets a night and there would also be one quiet night, setting up a rotation of 4 nights.

The next thing to do was to setup the batch file to play any given playlist, ramp the music up, and ramp the music down.  Here's the batch file:

@echo off
SetVolume.vbs 0
PlayPlaylist.vbs "%1"
for /L %%A IN (0,1,100) DO (
 echo.Increasing volume by 1/sec.
 call:WAITER 1
 SetVolume.vbs +1
)
call:WAITER 7200
for /L %%A IN (100,-1,0) DO (
 echo.Decreasing volume by 1/10 sec.
 call:WAITER 10
 SetVolume.vbs -1
)
Stop.vbs
SetVolume.vbs 100
GOTO:EOF

:WAITER
echo.Waiting for %~1 second(s).
choice /C x /N /T %~1 /D x > NUL
goto:eof

Let me break it down:

@echo off
SetVolume.vbs 0
PlayPlaylist.vbs "%1"

This section starts things off by turning off screen echo, setting the starting volume to 0, then starting to play the playlist provided as the first parameter when calling the batch file.

for /L %%A IN (0,1,100) DO (
 echo.Increasing volume by 1/sec.
 call:WAITER 1
 SetVolume.vbs +1
)

This section ramps up the volume over 100 seconds.  It increases the volume by 1% every 1 second.  This section makes the first use of the WAITER function shown next.  I chose to increment the volume instead of setting the volume to a specific level so that I could manually override the volume.  This means I could use my phone to increase the volume to 100 immediately instead of waiting 100 seconds.  If the script tries to increase the volume to something past 100, the volume just stays at 100.  If this for loop were in its 20th iteration and I had set it to increase to a specific level each time, then if I immediately pushed it up to 100, the next second would push it back to 21.  Not desirable, especially when ramping down the volume.

:WAITER
echo.Waiting for %~1 second(s).
choice /C x /N /T %~1 /D x > NUL
goto:eof

The waiter function simply waits a predetermined number of seconds.  So, if I wanted to wait X seconds, I would just call waiter like this: call:WAITER X.

call:WAITER 7200

This section waits for 7200 seconds which is 2 hours.  This is my 2 hour window; if I start the music at 6:45, it'll still be playing even if we put the boys to bed late.

for /L %%A IN (100,-1,0) DO (
 echo.Decreasing volume by 1/10 sec.
 call:WAITER 10
 SetVolume.vbs -1
)

This section uses the waiter function again to ramp down the volume over 1000 seconds (or 16 minutes and 40 seconds).

Stop.vbs
SetVolume.vbs 100
GOTO:EOF

This section cleans up after everything is over, making the whole thing ready to manually play music during the day if I wanted to.

I put this patch file into the same directory as the scripts and setup 3 scheduled tasks, each starting on a different day and repeating every 4 days:

1. sleepitunes.bat AllMusic
2. sleepitunes.bat Christmas
3. sleepitunes.bat Waves

This has worked pretty well for us.  I've toyed around with doing something like this as an alarm clock, but since the boys were born, I haven't really needed an alarm clock.  However, it wouldn't be very difficult.  The ramp up rate could be changed simply by changing the first call to the waiter function from 'call:WAITER 1' to 'call:WAITER 10'.  

NPC and the footer.text Property

Often when displaying report data, it is handy to have a little explanation about the data being presented.  In the case of NPC, this is accomplished by means of the footer.text property.  As it turns out, this footer.text is HTML based and can use some fairly advanced HTML features.

I say this can be done in NPC, but it can't; at least unless you jailbreak NPC.  At this point, CA has decided that CAPC will replace NPC.  Currently, there's no upgrade path and many of the features that some customers rely upon haven't been rebuilt into CAPC (remember CAPC is a rebuild from the ground up, so just because it was in NPC doesn't mean it will be in CAPC). I've already posted on the community about some of the features that I require before I will upgrade myself or anyone else to CAPC (browser view for example).

However, since NPC now has one foot in the grave (CA is evangelizing IM2.0 which requires CAPC more than Jim Jones' kool-aid), I see no reason why anyone wouldn't want to start experimenting with some of the hidden features in NPC.  So, if you haven't jailbroken NPC yet, you might consider it.  However, if you don't have NV as a data source, don't worry about it.  The NV view wizard is all you really get.  While that opens tons of doors to customization you never thought possible, it doesn't do anything for non-NV customers.  If you're interested, email me.  Once CA puts the last nail in NPC's coffin, I'll publish the steps here.  Until then, I need deniability.

So, on to the topic of this post.  When using the view wizard to create/edit NV views in NV (or NPC if you've jailbroken it) it turns out you can put some fairly complex html in the footer field.  Obviously, you can use {Resolution} and {Samples} in there to pull the current resolution and data point count (whether it's sampled data or not).  However just today I tried and was able to put a fully formed html table in the footer. I then tried to take it a step further and embed a YouTube video.  That worked fine as well.

In case you don't see it, this can be useful when trying to explain to new users what certain views mean.  For example, I could put together a complete page with live data and YouTube videos or text explaining what each one means and how to use it.  For example, I could put some text or an image in the footer that could expand a DIV section containing a YouTube video explaining how to interpret the data.  The nice part about this is that the footer is tied to the view.  Previously, I would put a separate browser view embedding the help information.  This is good for situations where you want to put the data on the left and explanation on the right.  However, putting the explanation in the footer ensures that the explanation stays with the data even through moves and copies.

So in order to put a hidden YouTube video in the footer of a view:

1. First check to make sure the view supports a footer (most do, this is just a sanity check).
2. Install HeidiSQL and connect to your NPC server.
3. Go to the control_properties table and filter it to only show your view.  Get the controlid from the NPC web gui by looking at the status bar when you open the view menu and mouseover the edit option.  Then set a filter for controlid=X, pageid in (0, Y), and propertiesid in (0, Z).
4. Find the footer.text property.  
5. Edit the propertyvalue column and insert your html.  
6. Save the record and you're good to go.

If you change the footer.text where pageid=0, propertiesid=0, and userid=0, you are modifying the default definition of the view and any existing views tied to the default settings and any new views will have this modified setting.

Here's the code you would insert if you wanted to embed my SA video.  You'll need to take out the carriage returns to make it one long line:

<script language="javascript"> function toggle(showHideDiv, helpimg){ var ele=document.getElementById(showHideDiv); var img=document.getElementById(helpimg); if(ele.style.display=="block"){ ele.style.display="none"; img.src='../npc/images/expand.gif' }else{ ele.style.display="block"; img.src='../npc/images/contract.gif' } }</script> <a href="javascript:toggle('hiddendiv','helpicon');"><img border="0" id="helpicon" src="../npc/images/expand.gif" /> Show help</a> <div id="hiddendiv" style="display: none;"> <iframe allowfullscreen="allowfullscreen" frameborder="0" height="315" src="http://www.youtube.com/embed/9vPZRViW43c" width="420"></iframe></div>

Windows 8 Highlights

I've been watching (mostly passively) the development and announcements around Windows 8.  Microsoft's new flagship desktop operating system will be unleashed sometime later this month.  I say unleashed, but I really should say released again since there was a developer's preview version of the operating system, a consumer preview version, and a release preview version that were released previously.  In my case, the multiple previews actually cemented a prejudice I have against MS' radical new ideas in their first iteration.  I actually downloaded and installed the developer preview and it told me everything I needed to know about Windows 8: it's the next Windows ME, or Windows Vista and I won't be rushing to it any time soon; Microsoft has tried to do something radically different, but they never get it right on the first try.  It's always the iteration afterward that hits pay-dirt (right after ME was XP and right after Vista was 7).
Either way, Michael Mace put together a great introductory video explaining what changes with this version of the operating system.  Here it is.

NQSync

Here's another one of my tools.  I found myself constantly needing to make sure the supportcig.exe is up to date on all my servers.  I also spend a lot of time tweaking my nqbackup.bat script, and every time there's a change, I need to copy it out to all my servers.  I also ended up trying to organize the mess that is the huge quantity of supportcig files distributed across all my servers.  So, of course, I scripted it.



NQSync.bat does several things I'm proud of.  First of all, it finds two unused drive letters on the system on which it's being run, starting with Z.  It does this so that the script can temporarily map network drives out to all the target systems and to the master system.  I should explain: the script can be run from anywhere as long as the credentials used to run the script have admin rights to all the servers.  First it maps a drive to the master server's D: drive.  Then it maps another network drive out to each of the target servers in turn (this is why we need two unused drive letters).  Then, depending on the options will do three actions:

• Update the supportCIG.exe file on the remote server - the script will copy the supportCIG.exe file from the master's D:\SupportCIG\ directory to the target server(s) D:\SupportCIG\ directory.
  To enable this option, include the switch /u anywhere on the line when calling the script.
• Gather the supportCIG output files onto the master server - the script will copy any zip files found in D:\SupportCIG\ to the master server into the D:\SupportCIG\[Server]\ directory, where [Server] is the server's name or IP address.
  To enable this option, include the switch /g anywhere on the line when calling the script.
• Synchronize tools to the remote server - the script will the entire contents of the D:\DBTools3 and D:\SupportTools6 directories out to the remote server in the same location.  This is how I get a copy of my updated nqbackup.bat file out to all the servers.
  To enable this option, include the witch /s anywhere on the line when calling the script.

The script itself will need to be modified to identify the master server.  Just go to line 2 and put the name or IP address of the master server.  Also, you'll need to provide a text file containing the name or IP address of each target server (one per line).  Obviously, it doesn't need to contain the master server's IP address.  Make the path to this text file one of the arguments.  For example:

nqsync.bat /u /g D:\lists\allservers.txt

This would update the CIG and gather all the outputs for all the servers in the text file.  Obviously, if the text file is in the same directory as nqsync.bat, you can exclude the full path and just put the name of the file.

NQSync.bat uses some fun code to find the first and second available drive letters.  I've tested this by using this script.  It identifies which drive letters are in use and which are available.

Installing IM 2.0 on Vanilla CentOS 6.3

Following up this post, I've decided to go ahead and post the current versions of all my scripts.  I should explain a little about my environment.  I have the VM's setup cloned off a vanilla^* CentOS 6.3 64-bit installation.  They each use DHCP to get their IP addresses from a DHCP server that has reservations to they always use the same IP addresses^†.  On the same network I have a web server that hosts the installers for CAPC, IMDA, & IMDR.  So, I've written scripts that will download the installers, run all the pre-requisites and checks and run the installers.  The scripts are here, here, here, here, here, & here. They are not up to date with the current installer. They will need to be modified to work properly with the current installer.

Here's how I use them:
DLAll.sh - this is not really a script, just a text file containing the bootstrap commands to kick off all the other scripts.  I copy and paste from this script into the ssh session.  Each section should be clearly commented as to what it does.  The password to get into my server is masked, but it wouldn't work on your network unless your server were a clone of mine anyway.  Modify as you wish.  I couldn't get the PC to talk to the DA without disabling the firewalls, so that's at the top.  I usually disable the firewalls on all of them and will probably build that configuration into my base image in the future.

InstallDR1.sh - this script gets everything ready for the Data Repository installation.  These are basically all the commands that are run as root.

InstallDR2.sh - this script contains the commands that the dradmin user needs to execute.  This one requires manual intervention at 4 places, so I've documented what I do at each stop.

InstallDA.sh - this one installs the Data Aggregator

InstallDC.sh - this one installs the Data Collector

InstallPC.sh - this one installs Performance Center

I think that might be all.  So i'm going to leave it at that for now.  If you've got any suggestions on how the scripts could be improved, let me know.

^*Vanilla=updates applied and startup mode changed to console only
^†This may be the source of other problems since the linux host file doesn't actually have the IP address in it.

Installing CAPC on vanilla CentOS 6.3

Since IM 2.0 came out, I've been testing the installation of the various components on CentOS 6.3.  (No your're not seeing things, my last post was going to be this post, but I went on a rant instead.)  I've come up with a few scripts that I use to get everything configured.  The first script was actually to install the data aggregator and repository.  It's not really a script, since some parts can't be scripted.  However, the parts that can be scripted have been.  I just copy and paste the commands into a putty window.
Anyway, the CAPC installation can be completely automated.  This is on a vanilla CentOS 6.3 64-bit installation.  The only change I made was to change the /etc/inittab to boot to the console instead of the GUI (change the 5 to a 3) and reboot.  I don't bother setting static IP addresses in my lab.  I use DHCP reservations.  It makes it a snap to configure.  I connect to the server via putty and paste in the following commands:
#turn off firewalls service iptables save service iptables stop chkconfig iptables off service ip6tables save service ip6tables stop chkconfig ip6tables off yum -y update #update the system yum -y install glibc.i686 unzip bc sudo libstdc++-4.4.6-4.el6.i686 #install missing libraries echo "Red Hat Enterprise Linux Server release 5.8 (Tikanga)" > /etc/redhat-release #make centos look like redhat cat /etc/selinux/config | sed -e 's/SELINUX=.*/SELINUX=disabled/' > /etc/selinux/config #disble selinux wget --user im20 --password ******** --progress=bar -P /opt/CA http://192.168.1.110/IM20/CA_PC_20_Linux/CAPerfCenterSetup.bin #download performance center chmod u+x /opt/CA/CAPerfCenterSetup.bin #set execute permissions echo 'USER_INSTALL_DIR=/opt/CA -fileOverwrite_/opt/CA/PerformanceCenter/uninstall/Uninstall_PerformanceCenter.lax=Yes -fileOverwrite_/opt/CA/PerformanceCenter/ThirdPartyContent/CAPC_ThirdPartyContent.txt=Yes -fileOverwrite_/opt/CA/PerformanceCenter/CAPC_SeedFile.xml=Yes' > /opt/CA/PCinstaller.properties #create properties file /opt/CA/CAPerfCenterSetup.bin -i silent -f /opt/CA/PCinstaller.properties #install
There you go.  I'll post the other scripts in separate posts since they're a bit more involved.

IM 2.0 on Linux vs. Windows

With the release of IM 2.0, I've been testing the installation of the various components on CentOS because my lab's investor (my wife) doesn't see the need to purchase a RedHat license.  All the better anyway since others might want to know if CentOS is an option for installation to save on adoption costs.  Frankly, I'm not sure why CA decided to go with RHEL.
While it is probably the most popular Linux server operating system, all (I repeat, ALL) of the previous NetQoS software ran on Windows.  I'm not counting MTP since it's sold as an appliance not software.  The target audience for the original NetQoS products was the network engineer.  It has since bloomed to include application owners and server administrators.  However, if you look at the role of the person who normally administers and champions the NetQoS products, it's still a network engineer.
It is my opinion that network engineers are most familiar with two operating systems: Cisco IOS and Windows.  There will be that case where the network engineer used to be on the server team but is now working on the network side.  While this obviously happens, I think there are just as many server-turned-network engineers who come from Linux/mixed (Windows & Linux, come on even Linux only environments have Exchange) environments as come from Windows only environments.  So, my conclusion is that most network engineers will be most familiar with Cisco IOS and Windows (both from server OS and desktop OS experience).  IM 2.0 should have been released on Windows.
There is another possible reason to use Linux over Windows: speed.  I agree with this argument.  Even with CentOS, I can turn off the GUI and save the resources that would otherwise be dedicated to display a locked screen 99.999% of the time.  However, the minimum RAM requirement for IM 2.0 is 4GB.  What!? I thought Linux was a better performer and could get away with not having as much RAM.  Well, it turns out that even in a lab environment when monitoring a very small infrastructure, 3GB isn't always enough.  The fact that I installed DA/DR on a box with only 1GB was pointed to as a possible reason why I was seeing problems on my installation.  Wait guys, if i have to dedicate a ton of resources, why don't we just run it on Windows?
Wasn't IM 2.0 supposed to be developed on Java?  If that's the case, why does the OS even matter?  Shouldn't it be a fairly trivial matter to compile installers for all of the major operating systems?

I'm not a developer, so you really shouldn't be reading any of this without your tough over in your cheek.  But still.

Really?
I have to learn Linux?
Really?
I have to purchase RHEL?
Really?
I have to dedicate at least 4GB of RAM in a lab environment?
Really?

Deleting Unused Views from NPC

I posted this on the community, but I've had to look it up a couple times and always came back to my blog to look for it before I went to the community.  That told me I should have it posted here.

If you've used the custom view wizard to create views in NPC, you may have noticed that there's no way to delete views.  Also, if you've ever installed an ePack or used the nqwebtool (not recommended, doesn't work with all versions), NPC views will get created.  There may come a time when you want to delete those views; for example, if you've deleted the dataset the view is tied to or if you recreated a view that already exists and you don't need the duplicate.  Like I said, there's no way to do this in the GUI, but there is a way to do it through the database.

Standard warnings apply, don't do this, it will break your stuff, you're on your own, back up your stuff, don't cry to me, don't tell support I told you this would work without a problem, you're on your own, etc. etc. etc.

You should be able to delete views from NPC by creating a page (preferably not in your 'my pages' menu) and put the views you want to delete onto that page. Then execute a couple queries that delete the information from the database for all the views on that page. You would need to get the page id of the page you created; just look in the url for the pageid. It should be a number, usually 5 digits, like 36598.

In the following example, that's the pageid I'll use. These queries will show you what will be deleted:
select * from controls where id in (select controlid from pagelayout where pageid=36598); select * from control_properties where controlid in (select controlid from pagelayout where pageid=36598); select * from pagelayout where controlid in (select controlid from pagelayout where pageid=36598);
These queries will execute the deletion:
delete from controls where id in (select controlid from pagelayout where pageid=36598); delete from control_properties where controlid not in (select id from controls); delete from pagelayout where controlid not in (select id from controls);
This doesn't delete views created on the device, router, server, switch, or any of the poll instance context pages.  Those would require a bit more work.  The following query should result in the ID numbers of any views in NPC that no longer have the specified dataset:
select distinct controlid from control_properties where propertyname='DataSetName' and propertyvalue='<dataset_short_name>';

Where <dataset_short_name>  is the short name of the dataset (i.e. avail as opposed to 'Device Availability').

If we combine that query with a query to NV to get a list of the datasets, and you could easily make sure to catch all views for obsolete datasets.  The problem is that this is a bit more complex because NetVoyant has some datasets that aren't in the dataset list.  Actually, NV has some contexts that aren't in the dataset list.  So, get the current dataset list with this query on the NVMC:
select dataset_name from datasets;

Then change the results from something like this:
+---------------+ | dataset_name  | +---------------+ | aimdc         | | aimhost       | | aimvm         | | avail         | | ciscoMemPool  | | ciscoSwitch   | | ciscoSystem   | | dsx1near      | | dsx3near      | | etherlikepaus | | etherstats    | | frcircuit     | | hrdevice      | | hrprocessor   | | hrstorage     | | hrswrun       | | ifstats       | | nbarstats     | | protodist     | | qosclass      | | qoscolor      | | qosiphc       | | qosmatch      | | qospolice     | | qosqueue      | | qosred        | | qosset        | | qosts         | | reach         | | rtthttp       | | rttjitter     | | rttstats      | +---------------+
Into something that can be used in the query, like this:
'aimdc','aimhost','aimvm','avail','ciscoMemPool','ciscoSwitch','ciscoSystem','dsx1near','dsx3near','etherlikepaus','etherstats','frcircuit','hrdevice','hrprocessor','hrstorage','hrswrun','ifstats','nbarstats','protodist','qosclass','qoscolor','qosiphc','qosmatch','qospolice','qosqueue','qosred','qosset','qosts','reach','rtthttp','rttjitter','rttstats'

Then add the following to the end of that list:
'rttstats,rttjitter,rtthttp','event_log','event_list','rttstatscap:operations','ciscoProcess'

So it reads like this:
'aimdc','aimhost','aimvm','avail','ciscoMemPool','ciscoSwitch','ciscoSystem','dsx1near','dsx3near','etherlikepaus','etherstats','frcircuit','hrdevice','hrprocessor','hrstorage','hrswrun','ifstats','nbarstats','protodist','qosclass','qoscolor','qosiphc','qosmatch','qospolice','qosqueue','qosred','qosset','qosts','reach','rtthttp','rttjitter','rttstats','rttstats,rttjitter,rtthttp','event_log','event_list','rttstatscap:operations','ciscoProcess'


So, to identify all the views in NPC that aren't tied to one of these datasets, do this query on NPC:
select * from controls where id in (select controlid from control_properties where propertyname='DataSetName' and propertyvalue not in ('aimdc','aimhost','aimvm','avail','ciscoMemPool','ciscoSwitch','ciscoSystem','dsx1near','dsx3near','etherlikepaus','etherstats','frcircuit','hrdevice','hrprocessor','hrstorage','hrswrun','ifstats','nbarstats','protodist','qosclass','qoscolor','qosiphc','qosmatch','qospolice','qosqueue','qosred','qosset','qosts','reach','rtthttp','rttjitter','rttstats','rttstats,rttjitter,rtthttp','event_log','event_list','rttstatscap:operations','ciscoProcess' ) ) ;
To delete all these views, their properties, and remove them from any pages they may be on, use these:
delete from controls where id in (select controlid from control_properties where propertyname='DataSetName' and propertyvalue not in ('aimdc','aimhost','aimvm','avail','ciscoMemPool','ciscoSwitch','ciscoSystem','dsx1near','dsx3near','etherlikepaus','etherstats','frcircuit','hrdevice','hrprocessor','hrstorage','hrswrun','ifstats','nbarstats','protodist','qosclass','qoscolor','qosiphc','qosmatch','qospolice','qosqueue','qosred','qosset','qosts','reach','rtthttp','rttjitter','rttstats','rttstats,rttjitter,rtthttp','event_log','event_list','rttstatscap:operations','ciscoProcess' ) ); delete from control_properties where controlid not in (select id from controls); delete from pagelayout where controlid not in (select id from controls);
Note: don't copy and paste these queries as they are specific to my installation.  You may have built other datasets and views that should be included in this process.  If you copy and paste these queries, those views will be deleted and you'll have to rebuild them.

Again, this isn't recommended or endorsed by CA. You should not try this.

Running commands on a remote computer

In a Windows environment, running commands on a remote computer isn't as easy as it should be.  There are third party tools out there that basically install an agent on the remote computer that allows you to push commands through the agent to the remote computer's command interpreter.  While this may be fine and probably works, it's not the only option.  In fact, there is a built in method of executing commands on a remote computer.  It involves the scheduled tasks feature of Windows.

Most people use scheduled tasks to run a program on a schedule.  In fact, most people don't even use scheduled tasks.  If they do, they use it to run defrag (if they're using an older version of Windows).  However, scheduled tasks can be very powerful if used properly because programs can be run locally on the box using either system credentials or a specific user's credentials.  While scheduling a task might not seem to be the best way to run code remotely, there's a little known feature that actually makes this work wonderfully: schtasks.  This command line utility allows for programmatic manipulation of scheduled tasks.  The kicker is that this command line utility can be used to manipulate scheduled tasks on a remote machine!

Therein lies the entire strategy of running code remotely.  First of all, Microsoft has put together a surprisingly helpful set of examples.  Check it out to familiarize yourself with the commands.

So, the strategy is this:

1. Package the code to be run on the remote computer into something that can be run silently (i.e. does not require any input from the user).  This may mean writing your batch file or perl script.
2. Copy the package to the remote computer.  Obviously, you'll need RW access to put the script on the remote computer.  This can be done remotely (and even recursively) by mapping a drive to the destination and copying the files to the mapped drive.
3. Use schtasks to create a new task to run once in the past.
4. Use schtasks to run the new task now
5. Use schtasks to delete the task (optional)

To illustrate this, I'll show how I deploy a certain script and run it immediately.  This script also has to be run nightly, but after any update to the script, I have to run it immediately on all the servers.  The script is here and might of interest to any NV users out there.
I use this script:

I call the the update script like this:

>deploy.bat myservers.txt

The argument is the name of a text file containing the names of the servers I want to push the updated file to.

If you wanted to run the batch file once then remove all traces, I use the following script.  The only problem with this one is that you have to wait for your script to finish before you can delete the scheduled task and the script.  This script let's you indicate when it's safe to go ahead with the deletion by querying the scheduled tasks list on the remote computer(s).  It will show 'Running' in the status column while the script is running and 'Ready' when it has finished.

That's about it.  Happy hacking!