DX Unified Infrastructure Management

Expand all | Collapse all

Deleting Servers from Nimsoft

Scott Stuessi

Scott StuessiJun 22, 2016 10:41 AM

  • 1.  Deleting Servers from Nimsoft

    Posted Nov 06, 2014 06:30 PM

    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.

     

    Thanks



  • 2.  Re: Deleting Servers from Nimsoft

    Posted Nov 06, 2014 07:00 PM

    Hi,

     

    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.

     

    -jon



  • 3.  Re: Deleting Servers from Nimsoft

    Posted Nov 06, 2014 07:01 PM

    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>'

     

     



  • 4.  Re: Deleting Servers from Nimsoft

    Posted Nov 06, 2014 07:06 PM
    Is it sufficient to only delete from the CM_COMPUTER_SYSTEM table? It appears as a device too. And then there's all the associated tables that have the keys referencing the now deleted system.


  • 5.  Re: Deleting Servers from Nimsoft

    Posted Nov 06, 2014 07:17 PM


  • 6.  Re: Deleting Servers from Nimsoft

    Posted Nov 09, 2014 01:24 PM
    It is supposed to be sufficient. There are cascading delete triggers on the tables you are deleting. Note that in deleting the computer you will also delete dependent data including possibly qos. Make sure that nothing belonging to the old id is important.


  • 7.  Re: Deleting Servers from Nimsoft

    Posted Nov 10, 2014 10:07 AM

    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.

     

    -jon



  • 8.  Re: Deleting Servers from Nimsoft

    Posted Nov 18, 2014 09:56 PM

    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.

     

    Then verify it deleted

             select * from cm_computer_system where 'name = 'DanLaptop'

    0 results.

     

    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?

     

    Thanks,

    Dan



  • 9.  Re: Deleting Servers from Nimsoft

    Posted Nov 18, 2014 10:38 PM


  • 10.  Re: Deleting Servers from Nimsoft

    Posted Nov 18, 2014 11:17 PM

    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).

     

    -jon



  • 11.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 12:12 AM

    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 

     

    Thanks,

    Dan



  • 12.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 01:10 AM

    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'



  • 13.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 01:44 AM

    Thanks Bryan.

    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_USAGE
    Source : loc1-lap042
    Target : loc1-lap042
    TableId: 670020
    Table : 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:

     

    • DELETE FROM CM_COMPUTER_SYSTEM WHERE NAME = 'HOSTNAME'
    • DELETE FROM S_QOS_DATA WHERE SOURCE = 'HOSTNAME'

     

    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. 



  • 14.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 01:54 AM

    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?



  • 15.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 01:59 AM
    MGMT Studio


  • 16.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 11:14 AM

    I'd recommend management studio as well :smileyhappy: 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.

     

    -jon



  • 17.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 07:26 PM

    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. 

     



  • 18.  Re: Deleting Servers from Nimsoft

    Posted Nov 19, 2014 07:31 PM

    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:      chris.luekermann@nimsoft.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" )
    else
       print( "housekeeping will delete data from "..#tRtables.." RN-Tables" )
    end 
    
    -- 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.." );"
          end
          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);"
       end
    
       if bHot ~= 1 then
          print( sDelQuery )
       else
          print( "deleting orphaned qos-data for table "..tRow["r_table"] )
          database.query(sDelQuery)
       end
    
       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 )
       else
          print( "deleting never delivered qos-data for table "..tRow["r_table"] )
          database.query( sDelUndeliveredQuery )
       end
    end
    
    print( "-- housekeeping done." )
    
    database.close()

     



  • 19.  Re: Deleting Servers from Nimsoft

    Posted Jul 01, 2015 02:56 PM

    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.

     

    Regards,

    Philip Becker



  • 20.  Re: Deleting Servers from Nimsoft

    Posted Oct 30, 2015 12:45 PM

    Is there a way to pass it multiple cs_keys? (comma separated or what not?)



  • 21.  Re: Deleting Servers from Nimsoft

    Posted Oct 30, 2015 12:47 PM

    Yes;

     

    It will take a comma delimited list as input.

     

    Regards,

    Philip Becker



  • 22.  Re: Deleting Servers from Nimsoft

    Posted Jan 05, 2016 12:01 PM

    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?

     

    Thanks,

      Greg



  • 23.  Re: Deleting Servers from Nimsoft

    Posted Jan 06, 2016 10:58 AM

    Hi Greg,

     

    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 matt.caraway@ca.com and we will schedule some time to meet and discuss this further.

     

    Matthew Caraway

    Product Owner



  • 24.  Re: Deleting Servers from Nimsoft

    Posted Jan 06, 2016 12:43 PM

    Matthew,

    I would LOVE to be in on this discussion…

     

    Gerry Lawrence

    Sr. Monitoring Engineer

    866.239.5000 | 678.835.4956

    Gerry.Lawrence@QtsDataCenters.com<mailto:Gerry.Lawrence@QtsDataCenters.com>



  • 25.  Re: Deleting Servers from Nimsoft

    Posted Jan 06, 2016 01:35 PM

    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:

     

    Mike Johnson

    IT Director

    (832)732-9114



  • 26.  Re: Deleting Servers from Nimsoft

    Posted Jan 07, 2016 10:43 AM

    I would like to participate as well.

     

    Brandy



  • 27.  Re: Deleting Servers from Nimsoft

    Posted Jan 07, 2016 12:08 PM

    The Delete a Robot procedure has matured in the later 8.x versions.

    The discovery_server's probe utility makes this a better method.

     

    https://wiki.ca.com/display/UIM82/Remove+Master+Devices

     

    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:

    SELECT origin,name,ip,dns_name,nimbus_type,cs_id,cs_key,dedicated,mac,os_name

    FROM cm_computer_system

    WHERE origin LIKE ('%HUB_NAME%')

    ORDER BY name

     

    Or

     

    //This query will search for a specific robot:

    SELECT origin,name,ip,dns_name,nimbus_type,cs_id,cs_key,dedicated,mac,os_name

    FROM cm_computer_system

    WHERE name LIKE ('%robot_name%')

    ORDER BY 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:

      1. Hold Shift then right arrow.
      2. Ctrl+H
      3. In the Replace dialog in the Replace with: put a coma: ,
      4. Hit replace all.

    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.

    1. Now open the UIM Admin Console:  http://<Primary-Hub_IP>:8080/adminconsole/ and login.
    2. Drill down to your Primary Hub. 
    3. On the right side, find the discovery_server probe and in the drop down menu select “Probe Utility”:
    4. In the Probe Utility GUI, in the drop down list, select the “remove_master_devices_by_cskeys” menu entry.
    5. Then double click on the Value Cell next to the csKeys row and paste the entire line from within Notepad++ into this cell:  
    6. Then press on the PLAY, green arrow button to execute the delete.
    7. Depending on the list, wait a bit then you’ll see the message that ## devices have been added to the queue for deletion:
    8. Now you have to just wait for the job to finish deleting these robots from the database.
    9. You can check the probeDiscovery queue from within the Status tab of the hub probe on the Primary UIM Hub. The discovery_server probe will processes the messages within this queue on a timed basis.
    10. After a period of time (up to 30 minutes) you can then check the CS_COMPUTER_SYSTEM table by re-running the query above. The list will be shorter or it will be empty if doing all.


    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.



  • 28.  Re: Deleting Servers from Nimsoft

    Posted Jan 15, 2016 02:07 PM

    There is any future planning to enable this deleting option in easier way in front end ....This is consuming a huge admin effort.



  • 29.  Re: Deleting Servers from Nimsoft

    Posted Jun 21, 2016 04:18 PM

    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.



  • 30.  Re: Deleting Servers from Nimsoft

    Posted Jun 22, 2016 04:27 AM

    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



  • 31.  Re: Deleting Servers from Nimsoft

    Posted Jun 22, 2016 07:42 AM

    Not able to access the link



  • 32.  Re: Deleting Servers from Nimsoft

    Posted Sep 09, 2016 09:35 AM

    hi Luc, I am unable to access the link which you have mentioned, please enable the permission for me

     

    Lijin



  • 33.  Re: Deleting Servers from Nimsoft

    Posted Sep 09, 2016 11:50 AM

    This should he the correct link: Delete server/device in UIM via command line tool (v3.3) , elso do a find in documents. 



  • 34.  Re: Deleting Servers from Nimsoft

    Posted Jun 20, 2017 11:39 AM

    Getting un-authorized access error while accessing your link, please give access

     

     

     

    Regards,

    Lijin Sarachandran

    Infrastructure Specialist

    Technical Manager 

    Delivery Lead 

           <callto:tel:4071122>      <sip:lijin.sarachandran@ust-global.com>     <callto:mob:+919995269269>



  • 35.  Re: Deleting Servers from Nimsoft

    Posted Jun 22, 2016 10:41 AM

    "Not able to access the link"      same here



  • 36.  Re: Deleting Servers from Nimsoft

    Posted Jun 22, 2016 11:24 AM

    Sorry, this link was a reference to an internal site.

    Will repackage/test/document asap and publish it.



  • 37.  Re: Deleting Servers from Nimsoft

    Posted Dec 22, 2016 05:20 PM

    Luc,

     

    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.

     

    Best,

     

    Jesse



  • 38.  Re: Deleting Servers from Nimsoft

    Posted Dec 22, 2016 05:45 PM

    Hi Jesse, 

     

    Please send me an email at carma44@ca.com. 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. 

     

    Matthew Caraway

    CA UIM Product Manager



  • 39.  Re: Deleting Servers from Nimsoft

    Posted Dec 23, 2016 02:16 AM

    Hi Jesse,

    This package was posted as a new entry:

    Delete server/device in UIM via command line tool (v3.3) 

    Regards, Luc



  • 40.  Re: Deleting Servers from Nimsoft

    Posted Jun 12, 2017 07:03 AM

    Hi Jessie,

     

    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.

     

    Please advice...



  • 41.  Re: Deleting Servers from Nimsoft