Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

Bulk Delete Resources

Created: 18 Mar 2010 • Updated: 24 Mar 2010 | 26 comments
Andrew Bosch's picture
+16 16 Votes
Login to vote
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

Comments 26 CommentsJump to latest comment

Wallo's picture

Great info Andrew, does this work on 7?

0
Login to vote
Andrew Bosch's picture

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

------------------------------------
Sr. Principal SQA Engineer
Symantec

0
Login to vote
KSchroeder's picture

Wow...this could be dangerous!

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

0
Login to vote
Andrew Bosch's picture

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!  :)

------------------------------------
Sr. Principal SQA Engineer
Symantec

0
Login to vote
Wallo's picture

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!

0
Login to vote
rweiss77's picture

Great query, thank you for providing this!

0
Login to vote
knwilson1's picture

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

0
Login to vote
TGiles's picture

That is the correct location to specify your SQL query.

0
Login to vote
orlando_huerta's picture

Have you tested in 7.0?

0
Login to vote
Nitin.Mak's picture

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

0
Login to vote
Andrew Bosch's picture

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

------------------------------------
Sr. Principal SQA Engineer
Symantec

0
Login to vote
Nitin.Mak's picture

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

0
Login to vote
Nitin.Mak's picture

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

0
Login to vote
Nitin.Mak's picture

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>

0
Login to vote
Andrew Bosch's picture

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

------------------------------------
Sr. Principal SQA Engineer
Symantec

0
Login to vote
Nitin.Mak's picture

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 ?

0
Login to vote
Andrew Bosch's picture

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

------------------------------------
Sr. Principal SQA Engineer
Symantec

0
Login to vote
ziggy's picture

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

+1
Login to vote
KSchroeder's picture

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

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

0
Login to vote
Jonouil's picture

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
============================================
 
0
Login to vote
KSchroeder's picture

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.

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

0
Login to vote
Jonouil's picture

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");
   }
 
}

 

 

 

0
Login to vote
Jonouil's picture

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.

 

0
Login to vote
maledesma's picture

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?

 

0
Login to vote
KSchroeder's picture

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.

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

0
Login to vote
Jeremy V's picture

Anyone have a retire computers from file script...

 

NS 7.1.8280

0
Login to vote