VB script delete computer from SCCM Database

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

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

  1. 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. 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
    1. 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. 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
  2. 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. 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
  3. 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
  4. 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. 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
  5. 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. 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
  6. 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. 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
  7. 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. 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
  8. 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. 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
  9. 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
  10. 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. 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
  11. 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. 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
  12. 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. 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. 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. 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
  13. 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. 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

Leave a Reply to Mark Bailey Cancel reply