VB script delete computer from SCCM Database

Posted on April 2, 2012 by Eswar Koneti | 39 Comments | 3,254 Views

There are cases where computer might needs to be deleted from SCCM Database when the OS up gradation happens(new name applied to host machines) and old name should be removed else it will automatically be removed as per the site maintenance settings.

Here is VB script that remove computer from SCCM Database based asking for machine name.

Note :I would love to test this on Lab before taking this to production.

You would be prompted for SCCM Site server name(where SQL server installed) and site code for the Site server.

ON ERROR RESUME NEXT
strServer=InputBox("Enter Site Server Nmae",,"win-cm2k7")
strSiteCode=InputBox("Enter Site Code",,"P01")
strComputer = InputBox("Enter Machine Name To Delete",,"System name")
Set locator = CreateObject( "WbemScripting.SWbemLocator" )
Set WbemServices1 = locator.ConnectServer( strServer,"root\SMS\site_" & strSiteCode
ResID = getResID(strComputer, WbemServices1)
If ResID = Empty Then
MsgBox "Unable To Determine The ResourceId For " & strComputer & " Exiting Application"
Wscript.Quit
Else
MsgBox "The ResourceId For " & strComputer & " On " & strServer & " Is " & ResID
End If
Set sResource = WbemServices1.Get("SMS_R_System='" & ResID & "'")
sResource.Delete_
If Err = 0 Then
MsgBox strComputer & " Has Been Removed From " & strSiteCode
Else
MsgBox "Unable To Locate " & strComputer & " On " & strServer
End If
Set sResource = Nothing
Function GetResID(strComputer, oWbem)
strQry = "Select ResourceID from SMS_R_System where Name=" & "'" & strComputer & "'"
Set objEnumerator = oWbem.ExecQuery(strQry)
If Err <> 0 Then
GetResID = 0
Exit Function
End If
For Each objInstance in objEnumerator
For Each oProp in objInstance.Properties_
GetResID = oProp.Value
Next
Next
Set objEnumerator = Nothing
End Function

39 Responses to "VB script delete computer from SCCM Database"

Sunil June 15, 2017 at 1:24 PM · Edit

Hi, I am trying to delete multiple entries of the same machine based on Resource ID.. but the script deletes only one entry. can you please help
Reply
1. Eswar Koneti July 7, 2017 at 1:00 AM · Edit
  
  Hi Sunil,
  This script works based on the hostname but not resourceID. If you need to delete based on resourceID ,you need to modify the script but i would always suggest to go with hostname for deletion .
  
  Regards,
  Eswar
  Reply
Ram Rachure February 18, 2017 at 5:54 PM · Edit

Hi Eswar,

As i have installed all prerequisite for SCCM when i try to Install CM12 it gets error like configuration manager Bootstrapper stopped working and I am unable to move forward to learn sccm2012. Kindly help in this .
Reply
Jimmy September 12, 2016 at 2:36 PM · Edit

Thanks for the script. It finds the right ResourceID for device but "unable to locate." Can you please help?

Thanks. Jimmy
Reply
1. Eswar Koneti October 21, 2016 at 10:12 AM · Edit
  
  can you post the error code ?
  
  Thanks,
  Eswar
  Reply
Surya J May 26, 2016 at 7:30 PM · Edit

Why dont you create collection for all unwanted computers and do delete special at one go ? Can you please guide me for this
Reply
1. Eswar Koneti May 27, 2016 at 8:42 AM · Edit
  
  Hi Surya, this script was written for cm07 environment, a long ago (also work in cm12 and later) but you can either of the methods like create collection, add all unwanted pcs and delete or if you want to delete single pc, you really no need to go through the console clicks and simply use the script to delete from sccm database.
  Reply
  1. Jason May 27, 2016 at 9:11 AM · Edit
    
    I had the same problems as a few others my support guys don't have SCCM admin access
    I solved this by creating a new custom security role & linking that to an AD group works a treat
    Reply
Rajesh August 7, 2015 at 10:01 PM · Edit

Hi Eswar,

Could you please let me know how to delete a machine from SCCM DB given only the MAC Address?

Basically I would like to know how to convert a MAC address to host name.

Thanks,
Rajesh
Reply
1. Eswar Koneti August 14, 2015 at 9:09 AM · Edit
  
  you can do this using SQL Query to know the computer name from MAC Address:
  SELECT sys.name0,mac.MAC_Addresses0 from v_r_system sys,v_RA_System_MACAddresses mac
  where sys.resourceid=mac.resourceid
  and mac.MAC_Addresses0='8C:DC:D4:D3:F1:A6'
  Reply
Sridhar April 17, 2014 at 5:36 AM · Edit

Hi Eswar,If a computer is deleted from All Systems & from SCCM Database. And it doesnt have sccm client installed how to bring this computer again to SCCM Console & register in database. Also, the computer is in the boundary configured in SCCM. What should be done on this? Thanks!
Reply
1. Eswar Koneti April 18, 2014 at 11:16 AM · Edit
  
  Either via ad system discovery or install client manually and try to run discovery data cycle action.
  Reply
Dan October 29, 2013 at 12:05 PM · Edit

Hi,

when i tried the script i get compilation error.

Line: 43
Char: 1
Error: syntax error
Code: 800A03EA
Source: Microsoft VBScript Compilation Error.

appreciate it very much if you can help me find out the fix.

thanks in advance.

Dan
Reply
1. Eswar Koneti October 29, 2013 at 7:58 PM · Edit
  
  can you check what is available in like 43 ? i updated the post without any blank spaces.can you try the new one ?
  please replace the quotes since the blog converts them to fancy.
  Reply
Mr A August 13, 2013 at 7:03 PM · Edit

HI,

I was trying to delete about 150 entries from SCCM server using below mentioned script with help of .txt file but it didn't work.Please let me know if anything else need to be changed in below mentioned script.message is saying compilation error while running the script

ON ERROR RESUME NEXT

Set objinputfile=fso.OpenTextFile(“julyy.txt”,1,True)
Set objoutputfile=fso.OpenTextFile(“Output.csv”,2,True)
Do While objinputfile.AtEndOfLine True
strcomputer=objinputfile.ReadLine

strServer=InputBox(“Enter Site Server Nmae”,,”D5SM”)

strSiteCode=InputBox(“Enter Site Code”,,”DS5”)

Set locator = CreateObject( “WbemScripting.SWbemLocator” )

Set WbemServices1 = locator.ConnectServer( strServer,”root\SMS\site_” & strSiteCode

ResID = getResID(strComputer, WbemServices1)

If ResID = Empty Then

objoutputfile.WriteLine( “Unable To Determine The ResourceId For ” & strComputer & ” Exiting Application”)
Else

objoutputfile.WriteLine(“The ResourceId For ” & strComputer & ” On ” & strServer & ” Is ” & ResID)

End If

Set sResource = WbemServices1.Get(“SMS_R_System=’” & ResID & “’”)

sResource.Delete_

If Err = 0 Then

objoutputfile.WriteLine ( strComputer & ” Has Been Removed From ” & strSiteCode)

Else

objoutputfile.WriteLine (“Unable To Locate ” & strComputer & ” On ” & strServer )

End If

Set sResource = Nothing

Function GetResID(strComputer, oWbem)

strQry = “Select ResourceID from SMS_R_System where Name=” & “’” & strComputer & “’”

Set objEnumerator = oWbem.ExecQuery(strQry)

If Err 0 Then

GetResID = 0

Exit Function

End If

For Each objInstance in objEnumerator

For Each oProp in objInstance.Properties_

GetResID = oProp.Value

Next

Next

Set objEnumerator = Nothing

End Function
Reply
1. Eswar Koneti August 30, 2013 at 8:05 PM · Edit
  
  what is the results after you ran it ? any errors ? do you have enough permissions on Database to do modifications ?
  try to run the script from command prompt to get any error msgs ?
  Reply
William June 21, 2013 at 8:41 AM · Edit

Hi,

Thanks for the response, but unfortunately that wont help... Based on request we should clean out obsolete machines in every half an hour (even after business hours), so that should be done via script. We have an implemented solution for cases, when the hardware id is the same...then the entries are getting merged automatically without the creation of an obsolete entry, but there are lot of obsoletes, where the Hardware id is different and one entry gets the flag obsolete=1. So the customer request is to habe obsolete=1 machines cleaned out in every 30 mins.
Reply
1. Eswar Koneti July 11, 2013 at 12:29 PM · Edit
  
  You can create a script that do special delete on collection and make it to run every 30 min that solves ur problem.
  I do not have readily avilable script to do special delete.no time to work on it now but i will post it on the web.
  You can google for script.
  Reply
William June 20, 2013 at 3:34 PM · Edit

Hi All,

I'm using SCCM 2007 R3

I'm looking for a script that removes all machines which have Obsolete = 1 flag from the database SMS_000 on SERVER1.

I would be really grateful to get assistance as I'm absolutely new to scripting.

Please assist! I would need that urgently due to an escalated project!

Thank you in advance!

William
Reply
1. Eswar Koneti June 21, 2013 at 7:03 AM · Edit
  
  Hi,
  you dont need any script to do this activity.Just create Query based collection with condition Obsolete=1 and do Delete special on collection.That works for you.
  Reply
Afzal June 17, 2013 at 7:05 PM · Edit

Hi ,i am new to vb script,can any one tell me how to remove machine from query based collection with out removing the computer from database.

below is the collection query

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.R

esourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = "VIA group"
Reply
1. Eswar Koneti June 21, 2013 at 7:08 AM · Edit
  
  To remove the machine from the collection,you can add either Query based rule with condition machine not like %ABCDEF% to the existing query so it will wont display in collection.
  Reply
Trent April 25, 2013 at 1:41 AM · Edit

Sorry for my extended delay but I was out of the office unexpectedly and was unable to follow up with testing.

I do receive access denied when running the delete as part of a VBScript, BUT I can manually launch the console with my test user's credentials and delete a resource directly from within the console. I also tried to add 'Modify Resource' permissions but I received the same 'Access Denied' message. I'm going to try a few more steps tomorrow but I wanted to post an update as it had been some time since your response.
Thanks!
Reply
Trent April 10, 2013 at 7:51 PM · Edit

Eswar,

The AD Security Group (for my techs) are a nested group of the SMS Admins group on the site server and given this group the 'Delete Resource' permissions to all instances of the Class 'Collection'. Still receive the access denied message.

Is there a log or something I might be able to review?
THANKS!
Reply
1. Eswar Koneti April 10, 2013 at 8:15 PM · Edit
  
  are you able to delete the computer resource manually from collection using the given permissions ?
  try with modify resource permission on the collection class in addition to Delete resource permission.
  Reply
Trent April 10, 2013 at 6:05 PM · Edit

I was curious if you could help me set the appropriate permissions for a tech to delete a computer object from the database (as Mark Bailey was wanting to do). Their security group has access to the SMS Provider and have been given 'delete resource' permissions within the SCCM console... but they are still given 'Access Denied'. Do I need to create a new SQL group and grant them delete perms there?

Thanks Eswar!
Reply
1. Eswar Koneti April 10, 2013 at 7:43 PM · Edit
  
  also ensure they should member of sms admin group on the sccm server to perform this operation.
  Collection should have delete resource permission for the users who would like to perform delete resource operation.
  Reply
Mark Bailey March 5, 2013 at 3:01 AM · Edit

What permissions does this require on the SCCM or SQL side?

BTW- I got it working after replacing the apostrophe symbol with an actual apostrophe.

Works great so far, just need to make sure my techs can use it! 😉
Reply
1. Eswar Koneti March 8, 2013 at 11:49 AM · Edit
  
  to edit or do any changes on the sccm console,user needs to be part of sms provider and other rights which you can provide from console security options based on what changes do you need to do Ex: collections,packages,site setting changes etc.
  Reply
  1. Jason May 6, 2016 at 8:30 AM · Edit
    
    This script works again thanks 🙂
    Same as Mark I'm trying to grant some of my users this access hopefully without adding full SCCM Admin rights can I just give them db_datawriter on the SQL database
    Reply
    1. Eswar Koneti May 7, 2016 at 11:15 AM · Edit
      
      you dont need to give datawriter permissions in SQL database instead, you can choose RBAC to provide delete resource =Yes permissions for the user .
      Reply
Rovi February 6, 2013 at 4:15 PM · Edit

Hi,
i want to retirve a machine from sccm 2007 data base which i deleted ?? please let m eknow know any script for this ??
Reply
1. Eswar Koneti February 8, 2013 at 1:35 PM · Edit
  
  all sccm information stored in Database,Once they are deleted from SCCM console,they get remove from DB.TO retrieve this,you can either run DDR on the machine using script (http://eskonr.com/2012/03/script-to-trigger-machine-policy-agent-on-multiple-machines/) or manually to get the computer back or wait for the discovery to happen.
  Reply
Dave January 24, 2013 at 3:22 AM · Edit

Great script and very useful... Thanks for posting it.

I can get the prompted one to work and it works great. But when I cut and paste the 2nd one that reads from a text file I cannot get past the line where it says "Function GetResID(strComputer, oWbem)" I have removed all the white space but it still fails. Any chance you could post the code in a text file to eliminate any ASCII white space.

Thanks
Dave
Reply
1. Eswar Koneti January 25, 2013 at 7:18 AM · Edit
  
  let me run the query and comeback to you.
  Reply
Luke Thomas October 11, 2012 at 4:29 AM · Edit

Looks exactly like what Im after. Only issue is getting unable to determine the resource ID for .....

Any ideas?
Reply
1. Eswar Koneti October 11, 2012 at 9:05 AM · Edit
  
  did you check if the computer is available in SCCM ? Unable to Determine the Resource ID seems to be no record ?
  
  Regards,
  Eskonr
  Reply
Vasu August 28, 2012 at 3:10 PM · Edit

Hi Eswar,

Good after noon.

Iwant to delete the multiple computers from SCCM.if you have any script for the same.

Regards,
Srinivas.CH
Reply
1. Eswar Koneti August 28, 2012 at 5:19 PM · Edit
  
  Hi,
  Try this script. I modified it to read the computers from text file and pipe the results to CSV file.
  Note : I did not test this script hence please test it before taking it to production.
  Why dont you create collection for all unwanted computers and do delete special at one go ?
  
  ON ERROR RESUME NEXT
  
  Set objinputfile=fso.OpenTextFile("input.txt",1,True)
  Set objoutputfile=fso.OpenTextFile("Output.csv",2,True)
  Do While objinputfile.AtEndOfLine <> True
  strcomputer=objinputfile.ReadLine
  
  strServer=InputBox("Enter Site Server Nmae",,"win-cm2k7")
  
  strSiteCode=InputBox("Enter Site Code",,"P01")
  
  Set locator = CreateObject( "WbemScripting.SWbemLocator" )
  
  Set WbemServices1 = locator.ConnectServer( strServer,"root\SMS\site_" & strSiteCode
  
  ResID = getResID(strComputer, WbemServices1)
  
  If ResID = Empty Then
  
  objoutputfile.WriteLine( "Unable To Determine The ResourceId For " & strComputer & " Exiting Application")
  Else
  
  objoutputfile.WriteLine("The ResourceId For " & strComputer & " On " & strServer & " Is " & ResID)
  
  End If
  
  Set sResource = WbemServices1.Get("SMS_R_System=’" & ResID & "’")
  
  sResource.Delete_
  
  If Err = 0 Then
  
  objoutputfile.WriteLine ( strComputer & " Has Been Removed From " & strSiteCode)
  
  Else
  
  objoutputfile.WriteLine ("Unable To Locate " & strComputer & " On " & strServer )
  
  End If
  
  Set sResource = Nothing
  
  Function GetResID(strComputer, oWbem)
  
  strQry = "Select ResourceID from SMS_R_System where Name=" & "’" & strComputer & "’"
  
  Set objEnumerator = oWbem.ExecQuery(strQry)
  
  If Err <> 0 Then
  
  GetResID = 0
  
  Exit Function
  
  End If
  
  For Each objInstance in objEnumerator
  
  For Each oProp in objInstance.Properties_
  
  GetResID = oProp.Value
  
  Next
  
  Next
  
  Set objEnumerator = Nothing
  
  End Function
  Reply