Hi there! Welcome to my blog. My name is Ivan. I am a Dutch IT Professional working as Principal IT Consultant. 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.
// *********************************************************************************
//
// 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 the mof code above to a text file with file name like delete.mof. Start the Systems Management Console and scroll down to Reporting and click Reports. Right click Reports; go to All Tasks and click Import Objects. Browse to the delete.mof file to import the report. After you the delete.mof file has been imported; start IE and open the SMSReporting website. New category should be available like Networknet-LocalIT tasks. If you want to change this go to the SMS console and change the category name.
Step 1 is finished now. The report is created. Next step is to extend the SMS database with extra stored procedure. Copy the text below to a text file named sp_delete.sql. Please change the SMS_ABC to your 3code Site name.
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
Login to your SQL server which hosts the SMS_ABC database and open SQL Query Analyzer tool. Open the sp_delete.sql file and execute the SQL (F5 short key).
Step 2 is completed now. Stored procedure has been created. Before the report will work for the reporting users the internal role webreport_approle will need to be granted with select and delete permissions on the SMS database tables.
These tables were granted with select and delete to webreport_approle. This is my test environment and I didn’t extend the SMS schema.
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 the file in SQL Query Analyzer. Now you are ready to delete a computer object. Select one computer and run the report.
If the report is providing you an error message; most probably the stored procedure is trying to delete the data within SMS tables where the SMS Reporting user role has no permissions.
Go back to SQL Query Analyzer and grant the permissions. E.g.
grant select,delete on object::TABLENAME_DATA TO webreport_approle grant select,delete on object::TABLENAME_HIST TO webreport_approle
If you have any questions please drop me an email.
SMS2003Rookie
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
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