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
40 Comments
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
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
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 .
Thanks for the script. It finds the right ResourceID for device but "unable to locate." Can you please help?
Thanks. Jimmy
can you post the error code ?
Thanks,
Eswar
Hi, I have tried this script for number of system. But am getting error in Line 24 char 1 Syntax error. Can you please guide me
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
Why dont you create collection for all unwanted computers and do delete special at one go ? Can you please guide me for this
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.
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
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
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'
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!
Either via ad system discovery or install client manually and try to run discovery data cycle action.
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
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.
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
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 ?
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.
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.
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
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.
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"
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.
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!
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!
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.
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!
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.
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! 😉
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.
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
you dont need to give datawriter permissions in SQL database instead, you can choose RBAC to provide delete resource =Yes permissions for the user .
Hi,
i want to retirve a machine from sccm 2007 data base which i deleted ?? please let m eknow know any script for this ??
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.
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
let me run the query and comeback to you.
Looks exactly like what Im after. Only issue is getting unable to determine the resource ID for .....
Any ideas?
did you check if the computer is available in SCCM ? Unable to Determine the Resource ID seems to be no record ?
Regards,
Eskonr
Hi Eswar,
Good after noon.
Iwant to delete the multiple computers from SCCM.if you have any script for the same.
Regards,
Srinivas.CH
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