Client Management Suite

 View Only

Bulk Delete Resources 

Mar 18, 2010 07:38 PM

File: DeleteItems.cs

Description: This script runs a user-defined SQL query and deletes the resulting items. Simply open the .cs file in any editor and include the SQL query that will return the list of items you wish to delete. It should be run from a command prompt and will show progress by adding a "." ('dot' or 'period') for every 50 items deleted.  This will delete 1 item at a time but is able to delete as many items as are found in the result set returned by the SQL query.  WARNING: when deleted a large amount of items, this script could take awhile to run.  In testing, it will delete approximately 2,000 items per hour.  Of course, this will vary depending on user environments.

Usage: 'nscript.exe deleteitems.cs' (Nscript.exe is found in c:\program files\altiris\notification server\bin, by default)

Disclaimer: Deleting items from the database is irreversible - please make a backup of all data before proceeding

Statistics
0 Favorited
5 Views
1 Files
0 Shares
1 Downloads
Attachment(s)
zip file
DeleteItems.zip   1 KB   1 version
Uploaded - Feb 25, 2020

Tags and Keywords

Comments

Mar 24, 2016 11:02 AM

Updated Script for v8.0 - It outputs the names of the items as it deletes too.

 

 

Feb 10, 2016 01:39 PM

This looks like it's working just fine in our 7.6 environment.  We decided to delete all computers and let them re-import via AD and agent check-in.  We've upgraded from 7.1 to 7.5 and now 7.6 and are finally ready to start pushing this to full production and wanted a clean DB, but didn't want to reconfigure all our settings, filters, and policies.

Used the following query:

SELECT Guid FROM vCMDBComputer

Screen Shot 2016-02-10 at 9.23.22 AM.png

Oct 21, 2015 09:05 AM

Didn't realize in 7.6 you can simply use filters under Manage > computers to search all computers, then select whatever you want(yes multiples) and delete! :) Much safer/easier.

Oct 21, 2015 08:52 AM

Ran a test job this morning but it's not working on 7.6.

D:\Program Files\Altiris\Notification Server\Bin>NScript.exe DeleteItems.cs
Deleting Items...
        Deleting 447 items.
0 deleted

Trying to troubleshoot now, I'm not familiar with C so I don't have a clue if I need to change anything.

Specifically, this is the select statement I'm trying to use in the .cs file. I tried a single GUID as well but same error.

static public string sql = @"SELECT GUID FROM vComputer WHERE IsManaged=0 and [os name] = 'mac os x'";

 

Oct 20, 2015 04:15 PM

I'm going to test it tomorrow on 7.6 because I need to purge over 1000 unmanaged computers. Will post my findings tomorrow.

Jun 18, 2015 03:19 PM

7.5 compatible??  Dont have a dev environemnt to test on.

 

 

Dec 12, 2012 01:20 PM

Anyone have a retire computers from file script...

 

NS 7.1.8280

Nov 13, 2012 11:17 PM

I had a similar error, but found it was in fact still deleting the computers . Try running again and I think you'll see a lower number. I also tweaked the code to explicitly write the item.name that was deleting and provide the count of deleted items so far instead of a period.

Nov 13, 2012 12:08 PM

I am trying to delete all the unmanged computers using the following SQL command SELECT Guid FROM vComputer WHERE IsManaged = 0. I am running from the default location of nscript.exe and running under the application user id. The script starts and shows that it will be deleting 491 items (this is the number I expected). After a few minutes, I get the following error:

* The script threw an exception.
Message: Failed to connect to the local NS communications group.

Stack Trace:
============
   at Altiris.NS.Server.GetNSChannel()
   at Altiris.NS.GroupCommunications.NSSharedCache`2..ctor(Int32 maxSize, String
 cacheName, Boolean filtering)
   at Altiris.NS.ItemManagement.Item.EnsureItemCacheExists()
   at Altiris.NS.ItemManagement.Item.GetItemFromCache(Guid itemGuid)
   at Altiris.NS.ItemManagement.Item.GetItemInternal(Guid itemGuid, IEnumerable`
1 accessPermissions, ItemLoadFlags itemLoadFlags)
   at Altiris.NS.ItemManagement.Item.GetItem[T](Guid itemGuid, IEnumerable`1 acc
essPermissions, ItemLoadFlags itemLoadFlags)
   at Altiris.NS.ItemManagement.Item.GetItem[T](Guid itemGuid)
   at Altiris.NS.ItemManagement.Item.GetItem(Guid itemGuid)
   at DeleteItems.DeleteSQLResults(String sql)
   at DeleteItems.Main(String[] args)


============================================
Errors occurred. Press the enter key to exit
============================================

Any ideas?

 

Oct 11, 2012 05:03 AM

Problem solved finally, it required to use the application identify to log in the server to perform the task.

But any ideas how to write the SQL if I have a large amount of machines, which i only have the computer names ?

Thanks.

 

Oct 10, 2012 10:18 PM

Hi KSchroeder,

It is running same as the original script with adding the SQL, and it is running within the server admin command prompt. 

Here is my script:

 

 

 

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
 
using Altiris.Common;
using Altiris.NS;
using Altiris.NS.ContextManagement;
using Altiris.NS.ItemManagement;
using Altiris.NS.Security;
using Altiris.NS.Logging;
 
class DeleteItems
{
 
   #region Internal SQL
 
   static public string sql = @"select Guid from vComputer where name = ' **computername*** ' ";
 
   #endregion
 
   #region Helper Methods
 
   //
   // Method takes a SQL query as parameter 
   // and performs Item.Delete on the resulting guids
   //
   static public int DeleteSQLResults(string sql)
   {
      ArrayList arrResults = new ArrayList();
      using (DatabaseContext context = DatabaseContext.GetContext())
      {
         SqlCommand command = (SqlCommand)context.CreateCommand(sql);
 
         using (SqlDataReader reader = command.ExecuteReader())
         {
            while (reader.Read())
            {
               arrResults.Add(reader.GetGuid(0));
            }
         }
      }
 
      int count = 0;
 
      Console.Write("\tDeleting " + arrResults.Count + " items.");
 
      foreach (Guid guid in arrResults)
      {
 
         Item i = Item.GetItem(guid) as Item;
         if (i != null)
         {
            try
            {
               if (!i.CanDelete())
               {
                  i.ItemAttributes = ItemAttributes.Normal;
                  i.Save();
               }
 
               Item.DeleteItem(i.Guid);
               count++;
 
               if (count % 50 == 0)
                  Console.Write(".");
            }
            catch (Exception ex)
            {
               Altiris.NS.Logging.EventLog.ReportError("Error deleting " + i.Name);
               Altiris.NS.Logging.EventLog.ReportError(ex.Message);
               Altiris.NS.Logging.EventLog.ReportError(ex.StackTrace);
            }
         }
      }
      return (count);
   }
 
   #endregion
 
   static void Main(string[] args)
   {
      //
      // Set the security context for the current user
      //
      SecurityContextManager.SetContextData();
 
      int count;
 
      Console.WriteLine("Deleting Items...");
      count = DeleteSQLResults(sql);
      Console.WriteLine("\n" + count + " deleted");
   }
 
}

 

 

 

Oct 10, 2012 05:50 PM

Jonouil,

I would be sure that you are running the command in an elevated command prompt on the SMP server, preferably using the Application Identity account to logon to the SMP server.  Your query should not specify the database directly; just run it as above; you shouldn't need to have a "USE <database>" command or fully-quality the table references in the query.

Oct 09, 2012 03:27 AM

Hi, I trying to use this but it seems cannot connect our database, as we are using a out of box DB.

Here are the result after the scripts ran:

 

D:\Program Files\Altiris\Notification Server\Bin>NScript.exe deleteitems.cs
Deleting Items...
 
* The script threw an exception.
Message: Failed to construct DatabaseContext object. Connection to database fail
ed.
 
Stack Trace:
============
   at Altiris.NS.ContextManagement.AdminDatabaseContext.ThrowDatabaseNotReadyExc
eption(String exceptionDetails, Exception innerException)
   at Altiris.Database.DatabaseContext`1.Initialize(Boolean beginTransaction, Is
olationLevel isolationLevel)
   at Altiris.Database.DatabaseContext`1.GetContext(Boolean createNewTransaction
, Nullable`1 isolationLevel, Boolean independentContext)
   at Altiris.Database.DatabaseContext`1.GetContext(Boolean createNewTransaction
, Nullable`1 isolationLevel)
   at Altiris.Database.DatabaseContext`1.GetContext(Boolean createNewTransaction
)
   at Altiris.Database.DatabaseContext`1.GetContext()
   at Altiris.NS.ContextManagement.DatabaseContext.GetContext()
   at DeleteItems.DeleteSQLResults(String sql)
   at DeleteItems.Main(String[] args)
 
 
============================================
Errors occurred. Press the enter key to exit
============================================
 

Sep 24, 2012 12:39 PM

Can also confirm that this works fine on 7.1 SP2; I used it to purge computers created via AD Import.

Dec 16, 2011 12:07 PM

This is great.  It needs lots of red flags and warnings, becuase you could do real damage.  With great power comes great responsibility.

Mar 29, 2011 10:57 PM

NScript.exe uses the NS database settings so it will connect to a remote SQL server...

Mar 29, 2011 01:24 AM

how do i run from default location of the script which is NS and my DB is on separate box ..will the script automatically connect to remote DB system and execute the script ?

Mar 27, 2011 12:03 PM

You have to run it from the default location - I believe it's hardcoded...

Mar 26, 2011 11:35 AM

I copied the nscript.exe and nscript.exe.config from NS to db server.. then i tried to execute the abve script and gave me following error... need help !!! to set it right.

D:\Altiris\Altiris_Bulk_delete_script\DeleteItems>nscript.exe DeleteItems-1resource.cs

* The script file "Altiris.Common, Version=6.0.0.0, Culture=neutral, PublicKeyTo ken=d516cb311cfb6e4f" could not be read


============================================
Errors occurred. Press the enter key to exit
============================================


D:\Altiris\Altiris_Bulk_delete_script\DeleteItems>

Mar 26, 2011 11:29 AM

Hi .. i hav modified the script and put the above query to delete one resource. Now want to check how do I run the script as DB is on the separate server and to execute this script i need nscript.exe which is on notification server ...so can I just simply copy nscript.exe on DB server and execute the nscript.exe deleteitems.cs ??

Regards/Nitin

Mar 26, 2011 11:07 AM

You mean I should put following query in the script to test deletion of 1 resource before going for big one.

select vc.guid from vcomputer vc where vc.[ismanaged] = 0 and Server = [server name] and IsLocal = 1 where vc.guid = "7BCCA983-4A92-4675-B281-022AC04E03C5 "

is this right query to be put in the script ? or do I have to include few other tables apart from vcomputer in the query?? and will this script remove all relevant entries/links of unmanaged resources ?

Thanks a lot again !

Regards/Nitin

Mar 04, 2011 04:49 PM

Does this work?

'SELECT Guid FROM vComputer WHERE IsManaged = 0'

BTW, we will not be able to provide a SQL script that will completely remove all these unmanaged resources...it needs to be done through the C# code provided above.  Let's figure out the SQL needed to find all these resources and then let the script do the rest...

Mar 04, 2011 02:20 AM

I am looking for deleting approx 12000 unmanaged resources which got in during AD import. How do I use this SQL script to remove these clients. Can someone please help in modifying this SQL script with right queries inside which i can just go and run on the SQL server...atleast for few devices 1st.

Thanks in advance !

Regards/Nitin

Oct 19, 2010 10:58 PM

Have you tested in 7.0?

Jul 12, 2010 05:23 PM

That is the correct location to specify your SQL query.

Jul 12, 2010 04:15 PM

Hi all,

Can someone please let me know where exactly in the .CS file I need to add the SQL query?  I am assuming I would replace the following line with my query like so: 

static public string sql = @"select Guid from vSiteResource";

static public string sql = @"ENTER SQL QUERY HERE";

Just want to make sure I don't somehow delete 260K items and only the 8K I am actually targeting.

Thanks,
Kevin

Apr 28, 2010 08:46 AM

Great query, thank you for providing this!

Mar 23, 2010 08:40 PM

Absolutely very helpful!  Thanks again Andrew.

This would have saved me hours of work, back when Connector had the import bug which was importing resources even when pressing the "Test Import" button... thanks!

Mar 20, 2010 11:40 PM

I agree 100%.  Please, please, please...test your SQL query before you open the flood gates.  From a different perspective, it can be very helpful too!  :)

Mar 19, 2010 11:11 PM

Wow...this could be dangerous!

Mar 19, 2010 12:05 AM

Yes, we just used this on a 7 box the other day...

Mar 18, 2010 11:39 PM

Great info Andrew, does this work on 7?

Related Entries and Links

No Related Resource entered.