Where Name0 in

Deleting Machines Directly From The SMS Database

To delete an individual machine from the SMS database using the SMS console it is necessary to create a collection using the direct membership rule wizard or base your new collection on a newly created Query. The first method is slow and can be time consuming, the second option just doubles your work and it the least desired method for most people.

The other automatic means in which you can remove or otherwise delete the machine(s) from the SMS database is to let the SMS_SQL_MONITOR service automatically remove the machine after it has reached its predefined Site maintenance task ‘Delete Aged Discovery Data’ and ‘Delete Aged Inventory History ‘ specifications if you have left it enabled. It is enabled by default and is set to 90 days. You can however change this to a more reasonable time period appropriate for your sites hierarchy if needed.

Below you will find a SQL query that will allow you to delete machines from the SMS database as the task “Delete Special” performs the removal process. It is important to note here that when you delete machines from a collection within the SMS console the machine is deleted from the database however the History table is not purged or removed. When you use the task Delete Special the machine is not only removed from the SMS database but the history for the machine is also deleted.

NOTE: You must uninstall the SMS client software to ensure that it does not report back in. If you are simply removing the machine name(s) from the SMS database because your site support staff has informed you that the machines were retired or re-imaged then this is not necessary. It is important to also note here that this is not a Microsoft supported means for deleting machines from the SMS database and should be used in a non production environment.

When you are done use the query in my earlier post entitled: ‘Searching Your SQL Database For A Specified Column String’ and search for the machine(s) that you just deleted to ensure that they have in fact been purged.

To delete multiple machines using the query that follows you can simply change the line that reads: Where Name0 = 'Machine_Name' To the following: Where Name0 in ('Machine_One', 'Machine_Two') as in the Deleting Multiple Machines SQL Query found at the end of this post.

• Deleting An Individual Machine SQL Query:

Insert DeletedMachines (SmsId)

Select IsNull(Sms_Unique_Identifier0,'')

From System_Disc

Where Name0 = 'Machine_Name'

And Sms_Unique_Identifier0 is not null

Delete System_Disc from System_Disc

Where Name0 = 'Machine_Name'

Delete System_Data from System_Data

Where Name0 = 'Machine_Name'

• Deleting Multiple Machines SQL Query:

Insert DeletedMachines (SmsId)

Select IsNull(Sms_Unique_Identifier0,'')

From System_Disc

Where Name0 in ('Machine_One', 'Machine_Two')

And Sms_Unique_Identifier0 is not null

Delete System_Disc from System_Disc

Where Name0 in ('Machine_One', 'Machine_Two')

Delete System_Data from System_Data

Where Name0 in ('Machine_One', 'Machine_Two')