Wednesday, August 15, 2012

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:

These queries will execute the deletion:

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:

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:

To delete all these views, their properties, and remove them from any pages they may be on, use these:

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.