I have a need to delete a server that appears in the Nimsoft database. What is the safest way of achieving this?
The server in question had it's robot_id changed at the system (I don't know why a colleague did this) so it then appears in CM_DEVICES twice. It is also appearing as two computer systems in CM_COMPUTER_SYSTEMS. I'd like to remove the incorrect one.
However, I know the dev_id and cs_id appear in other tables and wondered which tables I need to ensure these references are removed from. Or will it be safe to leave these?
It would also be a bonus is old QoS data and alarms in the NIS were removed too.
I believe discovery should clean it up after 7 days (default), otherwise there's a list of tables you need to clean to restart discovery. Those are kind of old though.. I guess I myself would get the device_id from cm_computersystem table and work through the DEV and CI tables from there.
First, I'd wait to see if discovery cleans it up by itself, though.
Using Serviec Level Manger query tool or other DB management toolset
First check for the system in the cm_computer system table
select * from cm_computer_system where ip = '<ip of the host>' and name = '<name of the host>'
Then delete record
delete from cm_computer_system where ip = '<ip of the host>' and name = '<name of the host>'
Then verify it deleted
select * from cm_computer_system where ip = '<ip of the host>' and name = '<name of the host>'
I'd at least look into these tables https://na4.salesforce.com/articles/HowToProcedures/Reinitializing-Discovery?popup=true
I believe trigger for delete in cm_computer_system will only remove stuff from cm_nimbus robot and the cross reference table, which do not have triggers themselves.
edit: seems that in newer versions it also deletes from cm_relationship_cs_cs and cm_relationship_ci_cs
S_QOS_DATA has triggers to delete the actual qos data in RN_ tables. It wont touch the HN_ tables, though.
Tried this out on my laptop since in the UMP it had 3 entries for my laptop listed in the tree. Specified Nimsoft Type as Robot and still lists all 3.
But anyway went into SLM, did a SQL query to 1st Select * from
select * from cm_computer_system where 'name = 'DanLaptop'
3 Items returned.
I wanted to delete all these entries so I tried the following query:
delete from cm_computer_system where name = 'DanLaptop'
Took like 4 minutes to complete.
But can someone explain why when I do a refresh in the SLM took in the Quality Of Service Branch Entry, it still lists my Laptop with all the QoS data entries. 100+. And when I doulble click on it I still see that data.
I stopped the Nimsoft Robot Watcher service on my laptop prior to doing the steps above.
We are trying to delete QoS data out of the database but it just takes such a long time for each and every QoS item listed under each machine. We found we cannot just do a "Delete Object" or "Delete " on the Host. The QoS data is still retained after the fact even though the Delete QoS Object... Dialog box says:
---------------------------Delete QoS Object...---------------------------Do you wish to remove all QoS data related to the node 'loc1-lap042' and the current viewstructure,where robot='loc1-lap042' and origin='alphahub' ?Please note that this operation is irreversible...---------------------------Yes No Cancel---------------------------
I hit Yes but even after a F5/Refresh the data still shows. Is there any way to make this operation quicker?
You may want to look at the post here for cleaning up qos objects:
That script is alright, but with today's schema it's not very optimized and doesn't take every qos table into account.
Dblaco I explained in my previous post how deleting stuff on cm_computer_system doesn't affect the qos data.
Deleting qos from SLM, in my experience, does effectively remove the qos object and data (at least s_qos_data and rn_ tables, not sure about the other tables such as hn).
I guess speeding up the deleting is more of database optimization. If you don't have partitioning enabled, I'm sure that should make a big impact on the speed (delete vs truncate).
Hi Jonhcw, So when you have to delete a machine from w/in UIM and you have to delete the Robot and then all associated QoS data what process do you follow/use?
I open up SLM thick client (the old one, not the UMP SLM version) then in the LHP tree, under the "Quality of Service" branch I r-clicked and added a new Browse Menu Item. I did it by Client and the order is Data Selector 1: ORIGIN then ROBOT so I can then find the client, then find the robot easily.
I am trying to do the delete on the Robot and assuming it would delete all QoS data when I preform a r-click and "DELETE" but it doesn't. I just hit F5 in the SLM tool, and all that data still exists.
The method that does work is the following:
Open ORIGIN - Open Robot - Then right click on each Entry under the robot and you have to do a DELETE DATA. Do a Delete Data on every single entry under the robot (each delete takes 15minutes) then once all is gone you can then finally do a DELETE on the ROBOT and its finally removed for good. This method takes > couple of hours which is why I'm trying to find the best, optimal way to get this accomplished with a SQL Query.
What method, or Browse Menu are you using and deleting by?
Is there 1 SQL query that will accomplish this, deleting the Robot and ALL associated QoS tables associated to this robot? I just hit a new issue today with someone deleting
The RN_QOS_DATA_XXXX tables are tied to the S_QOS_DATA source/target combo, so you'd need to write a statement that selected all the s_qos_data entries and delete them.
So something like
delete from s_qos_data where robot = 'robotname'
So can we put together the right SQL queries that accomplishes all this in one post?
What should be ran first so we don't create orphan'd data?
When in the SLM tool, If I r-click on a QoS entgry under my laptop I have the following:
---------------------------QoS Target Properties---------------------------QoS Name: QOS_CPU_USAGESource : loc1-lap042Target : loc1-lap042TableId: 670020Table : RN_QOS_DATA_0003---------------------------
So a correction to the Query for the S_QOS_DATA should lookup the HOSTNAME as "SOURCE" not Robot.
So the queries should be:
I'm running the 2nd Query now so I'll check what happens afterwards. If anything is left in in the SLM tool for my lapto after I do a refresh.
Is there a way to increase the SQL Query Timeout execution from w/in the SLM Tool? My DELETE is timing out.
---------------------------Query failed---------------------------Query timeout expired---------------------------OK---------------------------
Or would it just be better to run this directly on the SQL Studio Manager interface?
I'd recommend management studio as well That's where I mostly operate with SLA/qos when I have to do anything beyond the very basics.
My procedure for removing robots mostly consists of deleting qos data (currently), and I do that from SLM as it seem to work for me (I'm on older version and don't use the historic data either).
Also from time to time I run a query to delete qos data that hasn't been updated in something like 180 days.
Hi John, could you share that "if item has not been updated in > 180 days then delete it" query? That would be very helpful.
BTW the query above on my one laptop took > 1.5 hours. If we have to delete 100+ machines from a site that would just be totally unacceptable.
Its deleting all of the raw data associated with that device, so you definitely want to be wary of how much data that could be.
Here is a LUA script that can be used to delete raw data, also you can get the query from it.
-- script: removeOldQosData
-- purpose: this script will delete all qos-data-sources and their data which have not delivered qos-values in a specified time
-- author: email@example.com
-- date: 2010/05/13
-- (c) 2010 Nimsoft
iDays = 14 -- delete qos-sources which did not deliver values in the past X days. Only specify FULL days here
sDatabaseType = "sqlserver" -- specify the database type you're using. Valid values: sqlserver | mysql
-- if you have oracle, write me a e-mail
bHot = 0 -- set this to 0 if you do not want the script to perform any real modifications
-- do not modify after this line unless you know what you're doing --
database.open("provider=nis;database=nis;driver=none") -- open the database
-- first, find all used RN_TABLES
sSql = "select distinct r_table from s_qos_data"
tRtables = database.query(sSql )
if bHot == 0 then
print( "-- table count: "..#tRtables.." RN-Tables" )
print( "-- the following output is valid SQL, you can copy this and execute it in your database shell manually" )
print( "housekeeping will delete data from "..#tRtables.." RN-Tables" )
-- now iterate over all rn_tables and determine orphaned qos-data
for iIx, tRow in pairs(tRtables) do
sDelQuery = ""
if sDatabaseType == "sqlserver" then
sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having datediff(day, max(sampletime), getdate() ) > "..iDays.." );"
if sDatabaseType == "mysql" then
sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having max(sampletime) < date_add( now(), INTERVAL -"..iDays.." DAY);"
if bHot ~= 1 then
print( sDelQuery )
print( "deleting orphaned qos-data for table "..tRow["r_table"] )
sDelUndeliveredQuery = "delete from s_qos_data where r_table='"..tRow["r_table"].."' and table_id not in (select distinct table_id from "..tRow["r_table"]..")"
if bHot ~= 1 then
print( sDelUndeliveredQuery )
print( "deleting never delivered qos-data for table "..tRow["r_table"] )
database.query( sDelUndeliveredQuery )
print( "-- housekeeping done." )
Guys; don't run the remove from CM_COMPUTER_SYSTEM if you're on an 8.1 UIM or higher - it'll cause issues with a new addition called datomic keys. It's a in-memory keystore; udm_manager is actually the probe that houses it.
The way to remove items - would be to open up the Probe Utility console for discovery_Server and use the "remove_master_device_by_cskey".
Step by step.
Using Infrastructure Manager...
Go to primary hub.
Scroll and select discovery_server, press Ctrl-P.
Find the remove_master_device_by_cskey in the Probe commandset.
Pass a cskey that you want to remove.
Press the green button.
This should remove safely from both CM_COMPUTER_SYSTEM and datomic keys.
To find out what they key is -
Run a select * from CM_COMPUTER_SYSTEM; the column cskey is the column we would need to copy paste.
Note; if you run discovery wizard or if you continue to return metrics from the robot in question (IE, rsp probe, or a robot is still installed and publishing metrics) - it will be rediscovered.
Is there a way to pass it multiple cs_keys? (comma separated or what not?)
It will take a comma delimited list as input.
Do cskeys change over time or will a discovered device permanently retain the same cskey? Basically can I have my DBA run the suggested query once to provide a list of all currently discovered devices and their cskeys or will I need to get an updated query output every time I need to remove a device?
First, I would like to introduce myself, then take a moment to answer your specific question.
My name is Matthew Caraway and I have been at CA for about five years now. I hail from the days of the "Nimsoft" product name. During this time I have spent time in R&D as an Engineer and also time in Product Management. Currently, I am the Product Owner responsible for some of the core components of the UIM Platform. Discovery Server, Discovery Agent, UDM Manager, Hub and Robot are some of the items for which I have responsibility for.
Regarding your specific question... You will want to query the cm_computer_system each time that you are intending to delete devices from the Discovery Server. Whenever a device is created in the cm_computer_system table, a new UUID is assigned for this record. The csKey column contains the UUID for each device. If you were to send an old list of csKeys to the Discovery Server when running this command it will return an error as they will not be found.
If you have the interest, I would love to gather with you on a Webex and have a discussion about changes to deleting devices. I recognize that querying the database each time you need to remove a device is painful. My proposed workflow will have a rich UI that is supported by an API accessible to customers. This will allow for users to delete devices manually and also provide the much desired capability to leverage automation during this process. We also intend to allow management of qos and alarms when removing devices. At this point I have some specific use cases prioritized and initial mock ups for the UI available. Any feedback you can provide would be greatly appreciated.
If you (or anyone else reading this thread) would like, please send me an email at firstname.lastname@example.org and we will schedule some time to meet and discuss this further.
I would LOVE to be in on this discussion…
Sr. Monitoring Engineer
866.239.5000 | 678.835.4956
Hi Mathew and Gerry,
Please include me in this same discussion. I am having a hard time understanding why there is not an easier way to delete devices from UIM and I'd like to be a part of any solution or improvement to the tool.
my contact information is below:
I would like to participate as well.
The Delete a Robot procedure has matured in the later 8.x versions.
The discovery_server's probe utility makes this a better method.
Hop onto the UIM SQL box and open SQL Studio and run a new query on the NimsoftSLM db
The query you can use will be:
//This query will search for all the robots under an entire hub:
WHERE origin LIKE ('%HUB_NAME%')
ORDER BY name
//This query will search for a specific robot:
WHERE name LIKE ('%robot_name%')
If deleting all robots under a hub: Select the entire cs_key column and r-click then a “Copy without Headers” on the query results and paste this into Notepad++.If you are removing individual machines you would then copy out the target machines’ CS_KEY value.
If you need to remove many machines we can copy out the entire column of cs_key values and then paste them into Notepad++.
Within Notepad++, go to the end of the 1st line, and do the following key commands:
All the CS_KEYs will now be on one line separated by a coma. Copy this entire line.Then follow the procedure in the article above, opening the discovery_server's probe utility and paste the full line of cs_keys into the VALUE text box.
That would be it. Then also would have to manually delete all the SLM data related to those robots from w/in the SLM portlet w/in UMP.
There is any future planning to enable this deleting option in easier way in front end ....This is consuming a huge admin effort.
It really does consume tons of our time. Just add the remove function like all the other front ends please. Makes it not very unified when you can delete in other places but not the UMP without jumping through hoops.
Utility that can be used to delete devices, following the rules described previously in this discussion:
Delete server/device in UIM via command line tool (v2.2)
note: a new version is being tested and documented that will include:
- delete device from Infrastructure manager
- delete device alarms from nas_transaction_log / _summary
Not able to access the link
hi Luc, I am unable to access the link which you have mentioned, please enable the permission for me
This should he the correct link: Delete server/device in UIM via command line tool (v3.3) , elso do a find in documents.
Getting un-authorized access error while accessing your link, please give access
<callto:tel:4071122> <sip:email@example.com> <callto:mob:+919995269269>
"Not able to access the link" same here
Sorry, this link was a reference to an internal site.
Will repackage/test/document asap and publish it.
Please let us know when we can see this. I haven't seen an update to this thread since you stated that you would package/test asap. Excited to make device deletion easier.
Please send me an email at firstname.lastname@example.org. I'd like to connect you with our upcoming UIM Beta program. We have developed an initial release for deleting devices in UIM. This is targeted to be in the UIM 8.5.1 release. Beta program is scheduled to begin in early 2017.
CA UIM Product Manager
This package was posted as a new entry:
Delete server/device in UIM via command line tool (v3.3)
I am using the latest 8.5.1 UIM. Do we have that front end deleting option in this version?
Can i get the details? Still, we are using the old method of deleting it from SQL studio using cskey and probe utility.
Remove Devices in USM - CA Unified Infrastructure Management - 8.5.1 - CA Technologies Documentation