SMS 2003: Delete a computer object with a report

Originally published 13 June 2007Last updated 2 December 20072 comments

This report deletes a computer object from the SMS database. Use it when restaging a computer with the same name; the normal process takes more than 30 days to age out the stale record.

Import the report

Code50 lines

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
// *********************************************************************************
//
// Created by SMS Export object wizard
//
// Thursday, May 31, 2007 created
//
// File Name: Delete Computer Object.MOF
//
// Comments :
//
//
// *********************************************************************************
// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Networknet-LocalIT tasks";
Comment = "Author:[email protected]
\nCreate date: 5/30/2007
\nDescription: This report will delete a computer object from the SMS database. Use this report if you restage a computer with the same name. Normal process will take more than 30days to age to stale the record.
\n
\nNote: Select the computer you will restage and click Display. When report is displayed it will show 0 rows!";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Delete Computer Object";
NumPrompts = 1;
RefreshInterval = 0;
ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Computername";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0
\n else
\n SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS
\n WHERE SYS.Netbios_Name0 like @__filterwildcard
\n ORDER By SYS.Netbios_Name0
\nend";
VariableName = "comp";
}};
SecurityKey = "";
SQLQuery = "exec sp_RemoveComputerObject1 @comp";
StatusMessageDetailSource = FALSE;
};
// ***** End *****

Save the MOF code above to a text file such as delete.mof.
Open the Systems Management Console, go to Reporting, and select Reports.
Right-click Reports, select All Tasks, and click Import Objects.
Browse to delete.mof and import the report.
Open the SMSReporting website in Internet Explorer. The Networknet-LocalIT tasks category should now be available. To change its name, edit the category in the SMS console.

The report is now created.

Add the stored procedure

The next step is to extend the SMS database with a stored procedure. Copy the following text to sp_delete.sql and change SMS_ABC to match your three-character site code.

SQL31 lines

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
USE [SMS_ABC]
GO
/****** Object: StoredProcedure [webreport_approle].[sp_RemoveComputerObject1] Script Date: 10/15/2007 11:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: [email protected]
-- Create date: 5/30/2007
-- Description: Stored Procedure for deleting computer from SMS database by SMS Reports
-- =============================================
ALTER PROCEDURE [webreport_approle].[sp_RemoveComputerObject1]
-- Add the parameters for the stored procedure here
@Machinename varchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT * from system_Data where Name0=@machinename
delete from system_Data where Name0 =@machinename
--SELECT * from system_DISC where Name0=@machinename
delete from system_DISC where Name0 =@machinename
--SELECT * from system_Hist where Name0=@machinename
delete from system_Hist where Name0 =@machinename
--print @machinename + ' has been deleted from SMS database'
select @machinename as 'Deleted'
END

Sign in to the SQL server hosting the SMS_ABC database and open SQL Query Analyzer. Open sp_delete.sql and execute it with F5. That completes the stored-procedure step.

Grant the reporting role permissions

Before reporting users can run the report, the internal role webreport_approle needs select and delete permissions on the SMS database tables. Those permissions were granted on the tables below in my test environment; I had not extended the SMS schema.

SQL86 lines

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
USE [SMS_ABC]
GO
grant select,delete on object::Add_Remove_Programs_DATA TO webreport_approle
grant select,delete on object::Add_Remove_Programs_HIST TO webreport_approle
grant select,delete on object::CD_ROM_DATA TO webreport_approle
grant select,delete on object::CD_ROM_HIST TO webreport_approle
grant select,delete on object::ClientKeyData TO webreport_approle
grant select,delete on object::Computer_System_DATA TO webreport_approle
grant select,delete on object::Computer_System_HIST TO webreport_approle
grant select,delete on object::DataItem TO webreport_approle
grant select,delete on object::DataItemContext TO webreport_approle
grant select,delete on object::DataItemProperty TO webreport_approle
grant select,delete on object::Desktop_Monitor_DATA TO webreport_approle
grant select,delete on object::Desktop_Monitor_HIST TO webreport_approle
grant select,delete on object::Disk_DATA TO webreport_approle
grant select,delete on object::Disk_HIST TO webreport_approle
grant select,delete on object::IDE_Controller_DATA TO webreport_approle
grant select,delete on object::IDE_Controller_HIST TO webreport_approle
grant select,delete on object::Keyboard_DATA TO webreport_approle
grant select,delete on object::Keyboard_HIST TO webreport_approle
grant select,delete on object::Last_Logon_Domain_DATA TO webreport_approle
grant select,delete on object::Last_Logon_Domain_HIST TO webreport_approle
grant select,delete on object::Logical_Disk_DATA TO webreport_approle
grant select,delete on object::Logical_Disk_HIST TO webreport_approle
grant select,delete on object::MeterData TO webreport_approle
grant select,delete on object::Modem_DATA TO webreport_approle
grant select,delete on object::Modem_HIST TO webreport_approle
grant select,delete on object::Motherboard_DATA TO webreport_approle
grant select,delete on object::Motherboard_HIST TO webreport_approle
grant select,delete on object::Mouse_DATA TO webreport_approle
grant select,delete on object::Mouse_HIST TO webreport_approle
grant select,delete on object::Netcard_DATA TO webreport_approle
grant select,delete on object::Netcard_HIST TO webreport_approle
grant select,delete on object::Network_Client_DATA TO webreport_approle
grant select,delete on object::Network_Client_HIST TO webreport_approle
grant select,delete on object::Network_DATA TO webreport_approle
grant select,delete on object::Network_HIST TO webreport_approle
grant select,delete on object::Operating_System_DATA TO webreport_approle
grant select,delete on object::Operating_System_HIST TO webreport_approle
grant select,delete on object::Parallel_Port_DATA TO webreport_approle
grant select,delete on object::Parallel_Port_HIST TO webreport_approle
grant select,delete on object::Partition_DATA TO webreport_approle
grant select,delete on object::Partition_HIST TO webreport_approle
grant select,delete on object::PC_BIOS_DATA TO webreport_approle
grant select,delete on object::PC_BIOS_HIST TO webreport_approle
grant select,delete on object::PC_Memory_DATA TO webreport_approle
grant select,delete on object::PC_Memory_HIST TO webreport_approle
grant select,delete on object::Printer_Configuration_DATA TO webreport_approle
grant select,delete on object::Printer_Configuration_HIST TO webreport_approle
grant select,delete on object::Printer_Device_DATA TO webreport_approle
grant select,delete on object::Printer_Device_HIST TO webreport_approle
grant select,delete on object::Processor_DATA TO webreport_approle
grant select,delete on object::Processor_HIST TO webreport_approle
grant select,delete on object::ScanPackage_Version_DATA TO webreport_approle
grant select,delete on object::ScanPackage_Version_HIST TO webreport_approle
grant select,delete on object::SCSI_Controller_DATA TO webreport_approle
grant select,delete on object::SCSI_Controller_HIST TO webreport_approle
grant select,delete on object::Services_DATA TO webreport_approle
grant select,delete on object::Services_HIST TO webreport_approle
grant select,delete on object::Sites_DATA TO webreport_approle
grant select,delete on object::SMS_Advanced_Client_S_DATA TO webreport_approle
grant select,delete on object::SMS_Advanced_Client_S_HIST TO webreport_approle
grant select,delete on object::SMSData TO webreport_approle
grant select,delete on object::Software_Updates_DATA TO webreport_approle
grant select,delete on object::Software_Updates_Extended_DATA TO webreport_approle
grant select,delete on object::Software_Updates_Extended_HIST TO webreport_approle
grant select,delete on object::Software_Updates_HIST TO webreport_approle
grant select,delete on object::Sound_Devices_DATA TO webreport_approle
grant select,delete on object::Sound_Devices_HIST TO webreport_approle
grant select,delete on object::System_DATA TO webreport_approle
grant select,delete on object::System_Enclosure_DATA TO webreport_approle
grant select,delete on object::System_Enclosure_HIST TO webreport_approle
grant select,delete on object::System_HIST TO webreport_approle
grant select,delete on object::Tape_Drive_DATA TO webreport_approle
grant select,delete on object::Tape_Drive_HIST TO webreport_approle
grant select,delete on object::USB_Controller_DATA TO webreport_approle
grant select,delete on object::USB_Controller_HIST TO webreport_approle
grant select,delete on object::Video_Controller_DATA TO webreport_approle
grant select,delete on object::Video_Controller_HIST TO webreport_approle
grant select,delete on object::VULNERABILITY_DATA TO webreport_approle
grant select,delete on object::VULNERABILITY_DETAIL_DATA TO webreport_approle
grant select,delete on object::VULNERABILITY_DETAIL_HIST TO webreport_approle
grant select,delete on object::VULNERABILITY_HIST TO webreport_approle
grant select,delete on object::Windows_Update_Agent__DATA TO webreport_approle
grant select,delete on object::Windows_Update_Agent__HIST TO webreport_approle
grant select,delete on object::WorkstationStatus_DATA TO webreport_approle

Save the code above to sql_grants.sql and execute it in SQL Query Analyzer.

Run the report

Select a computer and run the report to delete its object.

If the report returns an error, the stored procedure is most probably trying to delete data from tables where the SMS Reporting role lacks permissions. Return to SQL Query Analyzer and grant the permissions, for example:

SQL2 lines

1
2
grant select,delete on object::TABLENAME_DATA TO webreport_approle
grant select,delete on object::TABLENAME_HIST TO webreport_approle

If you have questions, please email me.

2 comments

Comments from the original WordPress site, kept read-only.

SMS2003Rookie 14 October 2007, 20:36
Hi,
how do you use this MOF? Where do I have to place it and where is this option shown when I want to use it??
Greetings,
admin 15 October 2007, 11:58
Hi there, I updated the post ;-).
I hope you are able to execute the text files in the correct tools. These steps are advanced and require some Sysadmin knowledge of SQL server.
Let me know your results. Cu

Import the report

Add the stored procedure

Grant the reporting role permissions

Run the report

More in 2003

2 comments