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.

Wednesday, August 8, 2012

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!

Tuesday, August 7, 2012

Creating Properties for sysName, sysDescr, and sysObjectID

UPDATE: I've combined this tool with the tool I built that allows administrators to add/delete/rediscover devices without logging into the console.  This tool is combined with the properties creator simply because they both need to run right after discovery.  It actually has 3 parts: a widget, a JavaScript file and the batch file that runs every night.  First the batch file, which is an expansion of the properties creator.  In addition to the normal task of adding properties for new devices, this also adds a pair of properties that, when rendered on the NPC device details page, present the administrator with a rediscover button and a delete button.  By default, a password is required.  The password is set in the external JavaScript file (below) on lines 2 & 16 for rediscovery and deletion, respectively:


Next is the JavaScript file, which must be in the custom virtual directory (with alias 'custom'):


Lastly the widget.  The widget is only for adding new devices.


If you want to add a delete button, remove the text 'style="display:none;"' from the widget source.



Occasionally, I find it necessary to build auto-enable rules in NetVoyant based on SNMP properties like sysName, sysDescr, and sysObjectID.  Unfortunately, these are not all available for every dataset as SNMP parameters that could be used in rules.  However, custom properties are always available in auto-enable rules (not discovery rules since property comparison happens after initial discovery).  What this means is that rules can be built to automatically disable poll instances according to model, OS version, or software version (as obtained via the sysDescr).
In order for this to work however, each device needs custom properties.  Setting these manually is a pain and would take forever for anything other than a lab system.  To combat this, I've built this script (thanks Wade for the query help) that creates custom properties for every SNMP capable device containing the sysDescr, sysObjectID, sysName, sysContact, and sysLocation.

@echo off
set sqlcommand=mysql nms2 --skip-column-names -e "select count(*) from devices where snmp_capable=2 and dev_properties
set propertieslist=(select property_set_id from properties where property_name=
set logfile=D:\updateproperties.log
echo %date% - %time% - Script Started >> %logfile%
for %%A in (sysDescr,sysObjectID,sysName,sysContact,sysLocation) do (
 echo Devices with %%A property: >> %logfile%
 %sqlcommand% in %propertieslist%'%%A')" >> %logfile%
 echo Devices without %%A property: >> %logfile%
 %sqlcommand% not in %propertieslist%'%%A')" >> %logfile%
)
echo Running query  >> %logfile%
set inspropsql=mysql nms2 -e "replace into properties (select dev_properties,
set inspropsql2=0, 0 from devices where snmp_capable=2)"
%inspropsql% 'sysDescr', 18, sys_descr, %inspropsql2%
%inspropsql% 'sysObjectID',18, sys_objectid, %inspropsql2%
%inspropsql% 'sysName', 18, sys_name, %inspropsql2%
%inspropsql% 'sysContact', 18, sys_contact, %inspropsql2%
%inspropsql% 'sysLocation', 18,sys_location, %inspropsql2%
for %%A in (sysDescr,sysObjectID,sysName,sysContact,sysLocation) do (
echo Devices with %%A property: >> %logfile% %sqlcommand% in %propertieslist%'%%A')" >> %logfile% echo Devices without %%A property: >> %logfile% %sqlcommand% not in %propertieslist%'%%A')" >> %logfile% ) echo %date% - %time% - Script Ended ----------------------------------->> %logfile%

This script has to be run on the poller(s).  A new device will not get the properties until the script is run again, so, it is probably best to set it to run as a scheduled task every night right after discovery.

Once you've got the properties in place, you can create auto-enable rules using these properties by referencing them with the $.  So, for example, if I wanted to disable ifstats monitoring on all devices that have a sysLocation like 'France', I would add the following to the Property Rule in the add auto-enable rule dialog box:
$sysLocation like '%France%'
Save the rule, apply it to the dataset, then rediscover the device.  Voilá!

Monday, August 6, 2012

Data source rights and new data sources

When you add a new data source in NPC, be default, only the nqadmin and nquser accounts get access to this new data source.  The nqadmin is made an administrator and the nquser is made a user.  If you have 200 other users (including your normal user account), you won't be given administrative rights to this new data source even if you're an admin on all the other data sources.  In order to fix this the proper way, you have to edit all the users and grant them access the new data source.  While this can be done in bulk, what happens most often is that the admin doesn't take the time to distinguish between administrators and users and either grants everyone user access or every admin access or doesn't grant anyone access.  They may not notice this until someone can't do something they think they're supposed to.

It actually used to be better than this.  NPC had a concept of a permission set.  A permission set was a group of data source access rights that could be applied to users.  So, i could create one permission set called administrators and give them admin access to everything and another permission set called users and give them user access to everything.  I would then assign the admin permission set to all the admins and the user permission set to all the users.  If i added a new data source, all i would have to do is go to the permission set and update the permissions for that new data source and it would be applied to everyone with that permission set.  However, for reasons as yet unexplained, the guys building NPC decided that was too efficient and decided on a per user definition.  I guess since they already had a role component they didn't want to make role-based permissions as well.  Why they didn't just roll permission sets into the roles is beyond me (i can understand technically why they didn't: because it's more versatile that way.  But really, who knows enough about NPC to really use it that way?).

Anyway, if you find yourself in this situation and you don't want to have to do it manually, you can always do it in the database.  Standard disclaimer: don't do this, it will break your stuff, i won't help you fix it, back up your stuff, you're on your own, don't tell support i told you to do this when you call them to have them help you fix it, etc., etc., etc.

Run the following query to turn all NPC admins into admins on all the data sources:
replace into data_source_rights 
     (select a.UserID, b.SourceID, 1 as UserLevel
     from user_definitions a, data_sources2 b 
     where a.userlevel=1 and sourceid<>0)
;

Run the following query to turn all NPC users into users on all the data sources:
replace into data_source_rights 
     (select a.UserID, b.SourceID, 4 as UserLevel
     from user_definitions a, data_sources2 b 
     where a.userlevel=4 and sourceid<>0)
;