SMS Report - Delete Computer Object

13 06 2007

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. 

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

  1. USE [SMS_ABC]
  2. GO
  3. /****** Object: StoredProcedure [webreport_approle].[sp_RemoveComputerObject1] Script Date: 10/15/2007 11:27:23 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. – =============================================
  9. – Author: Ivan1980@Networknet.nl
  10. – Create date: 5/30/2007
  11. – Description: Stored Procedure for deleting computer from SMS database by SMS Reports
  12. – =============================================
  13. ALTER PROCEDURE [webreport_approle].[sp_RemoveComputerObject1]
  14. – Add the parameters for the stored procedure here
  15. @Machinename varchar(15)
  16. AS
  17. BEGIN
  18. – SET NOCOUNT ON added to prevent extra result sets from
  19. – interfering with SELECT statements.
  20. SET NOCOUNT ON;
  21.  
  22. – Insert statements for procedure here
  23. –SELECT * from system_Data where Name0=@machinename
  24. DELETE FROM system_Data WHERE Name0 =@machinename
  25. –SELECT * from system_DISC where Name0=@machinename
  26. DELETE FROM system_DISC WHERE Name0 =@machinename
  27. –SELECT * from system_Hist where Name0=@machinename
  28. DELETE FROM system_Hist WHERE Name0 =@machinename
  29. –print @machinename + ‘ has been deleted from SMS database’
  30. SELECT @machinename AS ‘Deleted’
  31. 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.

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

  1. GRANT SELECT,DELETE ON object::TABLENAME_DATA TO webreport_approle
  2. GRANT SELECT,DELETE ON object::TABLENAME_HIST TO webreport_approle

If you have any questions please drop me an email.

« Hello world! Analyze your scan log files »


Actions

Informations

  • Date : 13 June 2007
  • Categories : SMS

2 responses to “SMS Report - Delete Computer Object”

14 10 2007
SMS2003Rookie (20:36:15) :

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,

15 10 2007
admin (11:58:05) :

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

Leave a comment

You can use these tags : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>