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.